Splunk Search

is cleaner timechart formatting possible for login/logoff time report? (seeking better human readability)

caseynordell
Explorer

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:

alt text

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.

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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".

View solution in original post

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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".

0 Karma

caseynordell
Explorer

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma

caseynordell
Explorer

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!!!

Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...