Windows logon report
Looking for a way to build a report for successful logins and eliminate duplicates users. So if I have a user who logs on multiple times I only want to show one successful logon. Anyone have an idea on how to write the SQL where clause to do this?
If you don't need the Timestamp of the event then it is easy. Just select the fields that show non-unique data, such as the username, maybe the source ip and the domain and then do a count(username) so that you have the total number of login messages.
Then envision will group all of the events together where the columns you have selected are unique. If you do not want to have a count select the checkbox for unique records only and it will do the same thing.
I hope that helps,
Any way to get the date of just the LAST logon? I've been pestering RSA for some time on a need to get dormant account reports. What I'd like is a listing of each user who has logged on, one unique user per line, with the last date they logged on.
This would be possible if they either, let me wrtie my own sql or provided a MAX function for the date field.
(It can also be done by extracting all rows and doing the work yourself in another dbms or spreadsheet, but that idea is wrong for so many reasons....)
I've tried to inject the max function into the sql, but thier parser rejects it. In sql parlance, I would want to issue something like this:
SELECT logon, MAX(date) from LogonTable group by logon;
Its a report that gets asked for all of the time and its so frustrating to have the data sitting in a repository but not allowed to access it....
The way enVision (and SIEM systems in general, as far as I know) are currently structured, they are all targetted on real/near-real time reporting. Going back and doing trending analysis, a la a data warehouse, is not something most of these tools are good at.
Have you tried using the Disctinct Checkbox? It is located under the list of fields on the table & field selection page in the report wizard.
It only counts a single data point for non-unique record sets.
That would not solve the problem. This is an account latency report. The main piece of information needed is the last time the user logged on, that is the max of the date within the give group (logon id)
The tools documentation touts its 'robust reporting' capabilities, they are anything but.
The direction for the company is to take the data and load it into another product, a data warehouse, so that flexible, I mean, robust reporting can be achieved.
I appreciate the thought.
I have had the same query in the past from Identity and Access Management groups. The way I have solved it is to query AD to get last login time. Of course, you have to convert the time to something that makes sense, but there are plenty of VBS tools out there that will take care of it for you.