I'm attempted to get a report for login and logoff times on Windows workstations. Here is what I have done so far:
installed splunk forwarder with the following inputs.conf
[WinEventLog:Security]
disabled = 0
whitelist=4624,4634,4647
Constructed the following query:
index=my.index host="computer.name" sourcetype="WinEventLog:Security"
| search NOT (Account_Name=*$ OR Account_Name=SYSTEM OR Account_Name=ANONYMOUS*)
| eval time=_time
| timechart span=1d min(time) as "Logon Time", max(time) as "Logoff Time" BY Account_Name
| convert timeformat="%H:%M:%S" ctime(*)
This produces the following (correct) results when I test it with two users logging in consecutively:
However, the formatting is not ideal as this chart could get unwieldy with many users logging in.
Is there a way that someone can think of to provide cleaner results than this? Thanks.
What is the purpose of this report? As in, what are your people trying to tell when they look at the output?
I don't see a valid use for timechart with this kind of individual logon/logoff data. If they are looking for logon/logoff time for individuals, they don't need to see it graphically.
index=my.index host="computer.name" sourcetype="WinEventLog:Security"
| search NOT (Account_Name=*$ OR Account_Name=SYSTEM OR Account_Name=ANONYMOUS*)
| eval time=_time
| bin _time span=1d
| stats min(time) as LogonTime, max(time) as LogoffTime BY _time Account_Name
| convert timeformat="%H:%M:%S" ctime(*Time)
If they want to see it graphically, then they probably don't need to see it individually... Also, you probably need to be checking for the actual eventIDs (4624, 528 and so on) and detecting actual logons and logoffs, not just assuming that the event that happened at 11:00 PM must have been a logoff and the one that happened at 2 AM the next day was a logon.
Windows logons are generally in the list...
(EventID=528 OR EventID=540 OR EventID=552 OR EventID=4624 OR EventID=4648),
and logoffs are usually 4634, but 4647 is also on the list.
If you actually could use the first and last events of the day as logon and logoff time, then you could do somehing like this to provide a sort of histogram of the logons and logoffs
index=my.index host="computer.name" sourcetype="WinEventLog:Security"
| search NOT (Account_Name=*$ OR Account_Name=SYSTEM OR Account_Name=ANONYMOUS*)
| eval time=_time
| bin _time span=1d
| stats min(time) as LogonTime max(time) as LogoffTime BY _time Account_Name
| eval LogTimes="Logon=".LogonTime." Logoff=".LogoffTime
| table Account_Name LogTimes
| makemv LogTimes
| mvexpand LogTimes
| rex field=LogTimes "(?<logtype>[^=]+)=(?<TheTime>\d+)"
| eval _time=TheTime
| table _time logtype Account_Name
| bin _time span=10m
| stats count values(Account_Name) as Account_Names by _time logtype
Updated first example to only convert times to time format.
Updated last example to use "Account_Name" rather than "user".
What is the purpose of this report? As in, what are your people trying to tell when they look at the output?
I don't see a valid use for timechart with this kind of individual logon/logoff data. If they are looking for logon/logoff time for individuals, they don't need to see it graphically.
index=my.index host="computer.name" sourcetype="WinEventLog:Security"
| search NOT (Account_Name=*$ OR Account_Name=SYSTEM OR Account_Name=ANONYMOUS*)
| eval time=_time
| bin _time span=1d
| stats min(time) as LogonTime, max(time) as LogoffTime BY _time Account_Name
| convert timeformat="%H:%M:%S" ctime(*Time)
If they want to see it graphically, then they probably don't need to see it individually... Also, you probably need to be checking for the actual eventIDs (4624, 528 and so on) and detecting actual logons and logoffs, not just assuming that the event that happened at 11:00 PM must have been a logoff and the one that happened at 2 AM the next day was a logon.
Windows logons are generally in the list...
(EventID=528 OR EventID=540 OR EventID=552 OR EventID=4624 OR EventID=4648),
and logoffs are usually 4634, but 4647 is also on the list.
If you actually could use the first and last events of the day as logon and logoff time, then you could do somehing like this to provide a sort of histogram of the logons and logoffs
index=my.index host="computer.name" sourcetype="WinEventLog:Security"
| search NOT (Account_Name=*$ OR Account_Name=SYSTEM OR Account_Name=ANONYMOUS*)
| eval time=_time
| bin _time span=1d
| stats min(time) as LogonTime max(time) as LogoffTime BY _time Account_Name
| eval LogTimes="Logon=".LogonTime." Logoff=".LogoffTime
| table Account_Name LogTimes
| makemv LogTimes
| mvexpand LogTimes
| rex field=LogTimes "(?<logtype>[^=]+)=(?<TheTime>\d+)"
| eval _time=TheTime
| table _time logtype Account_Name
| bin _time span=10m
| stats count values(Account_Name) as Account_Names by _time logtype
Updated first example to only convert times to time format.
Updated last example to use "Account_Name" rather than "user".
Thanks for your reply.
The purpose of the report is: there is a workstation which multiple users share and the department wishes to bill back use of the equipment to each user according to the number of hours they were logged in. This is currently done on a paper sign-in sheet, but they wish to automate the process.
I agree with you that timechart may not be the best way to do this and also that I probably should be matching event codes to specific logon/logoff events. I've attempted other ways, but I've been having trouble getting them to work properly and the code I posted is the most workable and accurate I've come up with so far.
I've tried your examples and the results from the first one look the closest to what I'm going for, but for some reason the Account_Name column is empty. I'm going to tinker with it on my end and see if I can get it working better.
The problem with the first example was that it was trying to format everything, including the Account_Name, to a time variable. Fixed the code. Also fixed the last example, which had an incorrect variable.
These both work perfectly after your corrections, as far as I can tell on my test machine.
Now I'm off to test them in the real world situation (checking them against the existing paper logs).
Thank you very much!!!