User Login Report
We have an immediate need to search for all user logins, excluding one user. The current login report does not provide an option to omit a user. We need to omit this one user because it is responsible for 99 percent of logins, and the return search is too big to encompass all logins for past year if this one user is included.
-
Hi Sandra,
Are you dumping your logs to a database? If so, you could run something along these lines...
SELECT DISTINCT ConnectionUser
FROM daily_server_logs
WHERE EventDetails LIKE '%Authenticated'
AND EventDateTime BETWEEN '2024-01-01 00:00:00.000' AND '2024-12-31 23:59:59.999'
AND ConnectionUser NOT IN ('', 'anonymous', 'other names')Steve
0 -
Right now, I'm using the report in Cerberus. The database is closed to us, isn't it? If not, how can I get to it to run your suggested script? Thanks!
0 -
There are two ways to accomplish this, that I use, anyway.One, use the Database Configuration in Cerberus to send the statistics to a database.That creates a set of tables to use for external reporting. I don't know if this will do anything retroactivily.
SELECT DISTINCT [user_name]
FROM [sessions]
WHERE time_started BETWEEN '2024-01-01 00:00:00.000' AND '2024-12-31 23:59:59.999'
AND [user_name] NOT IN ('', 'anonymous', 'other names')Two, create a table something like this in your favorite database...CREATE TABLE daily_server_logs
EventDateTime datetime NULL,
EventType varchar(7) NULL,
ConnectionID varchar(6) NULL,
ConnectionIP varchar(15) NULL,
ConnectionUser varchar(50) NULL,
EventDetails varchar(4000) NULL
)...where you can dump the daily logs generated by Cerberus which you can set up using this:You could probably use the same table to import the normal rollover logs, but for setting it up as a job in MSSQL I like the consistency of creation of the daily.SELECT DISTINCT ConnectionUser
FROM daily_server_logs
WHERE EventDetails LIKE '%Authenticated'
AND EventDateTime BETWEEN '2024-01-01 00:00:00.000' AND '2024-12-31 23:59:59.999'
AND ConnectionUser NOT IN ('', 'anonymous', 'other names')Three, in a more manual process, you could pull the logs into Excel and filter your duplicates out there.Four, and I don't know if this is dependent on the reporting database being set up ahead of time.-
On the Logon report, there's a CSV Export button.
-
Export the report
-
Open it in Excel
-
In Excel change to the Data ribbon
-
Select Remove Duplicates
-
Expand the selection and click Remove Duplicates
-
Uncheck all columns except User (this will limit the duplicate checking to just the User column)
-
You should get a message with "##### duplicate values found and removed; ### unique values remain."
-
That should be all the logins - you'd just have to remove the one remaining line with the user you don't want included.
0 -
Please sign in to leave a comment.
Comments
3 comments