Splunk Search

How do I chart Windows logon and logoff per user by hour?

scottrunyon
Contributor

I get a nice table with the logon and logoff times per user using the following search -

LogName=Security EventCode=4624

| stats earliest(_time) AS LOGON by user
| join [ search LogName=Security EventCode=4634
| stats latest(_time) AS LOGOFF by user]
| eval LOGON=strftime(LOGON,"%H:%M"), LOGOFF=strftime(LOGOFF,"%H:%M")

What I would like to do i create a graph showing the count of logon and logoff by user broken down by hour. The problem is that Windows creates multiple 4624 and 4634 messages. As timechart has a span of 1 hour, it picks up these "duplicate" messages and I get an entry for every hour the user is online.

How would I create a query to only count and chart the first logon message and the last logoff message per user by hour?

Thanks.

0 Karma
1 Solution

woodcock
Esteemed Legend

This is actually fairly complicated so let's take it step by step.

First, gather all the events:

index=all_infra sourcetype=WinEventLog:Security LogName=Security EventCode=4624 OR EventCode=4634

For testing purposes, if you'd like to limit it to the 10 users with the fewest logins (so you can double-check the math/results), then add this:

    [ search index=all_infra sourcetype=WinEventLog:Security LogName=Security EventCode=4624 OR EventCode=4634 earliest=-1h latest=now 
    | rare user 
    | fields user]

We need a BY field that represents each day:

| eval day=strftime(_time, "%m%d%y") 

We need to find the first login (4624) and the last logout (4634) for each user every day:

| streamstats count(eval(EventCode="4624")) AS pos4624 count(eval(EventCode="4634")) AS pos4634 BY user day 
| eventstats earliest(pos4624) AS first4624 latest(pos4634) AS latest4634 BY user day 
| where pos4624=earliest4624 OR pos4634=latest4634

We can now calculate the duration of the "stretched" login (then we do not need the logout events):

| eventstats range(_time) AS duration BY user day
| search EventCode="4624"

Here is where it gets tricky. We are going to use the concurrency command and we need to make sure that we have 1 "marker" event in each hour:

| append 
    [| makeresults count=2 
    | addinfo 
    | streamstats count 
    | eval _time = if((count=1), info_min_time, info_max_time) 
    | table _time 
    | makecontinuous _time span=1h 
    | eval duration=3600, DATASET="KEEPME"]

Now the magic; we just let concurrency do it's thing, decrease our concurrency by 1 (to ignore the "marker" events), and plot concurrency for each hour:

| concurrency duration=duration 
| search DATASET="KEEPME"
| eval concurrency=concurrency-1 
| timechart span=1h first(concurrency) AS concurrent_logins

The downside is that this solution will probably take a very long time to run.

View solution in original post

woodcock
Esteemed Legend

This is actually fairly complicated so let's take it step by step.

First, gather all the events:

index=all_infra sourcetype=WinEventLog:Security LogName=Security EventCode=4624 OR EventCode=4634

For testing purposes, if you'd like to limit it to the 10 users with the fewest logins (so you can double-check the math/results), then add this:

    [ search index=all_infra sourcetype=WinEventLog:Security LogName=Security EventCode=4624 OR EventCode=4634 earliest=-1h latest=now 
    | rare user 
    | fields user]

We need a BY field that represents each day:

| eval day=strftime(_time, "%m%d%y") 

We need to find the first login (4624) and the last logout (4634) for each user every day:

| streamstats count(eval(EventCode="4624")) AS pos4624 count(eval(EventCode="4634")) AS pos4634 BY user day 
| eventstats earliest(pos4624) AS first4624 latest(pos4634) AS latest4634 BY user day 
| where pos4624=earliest4624 OR pos4634=latest4634

We can now calculate the duration of the "stretched" login (then we do not need the logout events):

| eventstats range(_time) AS duration BY user day
| search EventCode="4624"

Here is where it gets tricky. We are going to use the concurrency command and we need to make sure that we have 1 "marker" event in each hour:

| append 
    [| makeresults count=2 
    | addinfo 
    | streamstats count 
    | eval _time = if((count=1), info_min_time, info_max_time) 
    | table _time 
    | makecontinuous _time span=1h 
    | eval duration=3600, DATASET="KEEPME"]

Now the magic; we just let concurrency do it's thing, decrease our concurrency by 1 (to ignore the "marker" events), and plot concurrency for each hour:

| concurrency duration=duration 
| search DATASET="KEEPME"
| eval concurrency=concurrency-1 
| timechart span=1h first(concurrency) AS concurrent_logins

The downside is that this solution will probably take a very long time to run.

scottrunyon
Contributor

Using the streamstats / eventstats as suggested I was able to get the chart I needed.

index=* sourcetype=WinEventLog:Security LogName=Security EventCode=4624
| eval day=strftime(_time, "%m%d%y")

| streamstats count(eval(EventCode="4624")) AS pos4624 BY user day
| eventstats earliest(pos4624) AS first4624 BY user day
| where pos4624 = first4624
| append [search index=* sourcetype=WinEventLog:Security LogName=Security EventCode=4634
| eval day=strftime(_time, "%m%d%y") | streamstats count(eval(EventCode="4634")) AS pos4634 BY user day
| eventstats latest(pos4634) AS latest4634 BY user day| where pos4634 = latest4634 ]
| timechart span=1h count(first4624) AS Logon count(latest4634) AS Logoff

0 Karma

woodcock
Esteemed Legend

Excellent.

0 Karma

woodcock
Esteemed Legend

Like this:

index=YourIndexHere LogName=Security EventCode=4624 OR EventCode=4634
| eval action=case((EventCode=4624), "LOGON", (EventCode=4634), "LOGOFF", true(), "ERROR")
| bin _time span=1h
| stats count by _time action user
0 Karma

scottrunyon
Contributor

woodcock, thank you for the answer. I guess I need to define what I am trying to do a litter better.

I want to have a line graph that shows each user's first logon and last logoff per day. My graph should look something like - 2 lines with the logon line around zero until 09:00 when logons will spike then drop close to zero for the rest of the day and a logoff line that will be around zero until 18:00 when it will spike and then drop close to zero. I can get the data point using one of several searches but when I try and use a bin of 1h in stats or timechart, i get the a logoff and logoff for every hour, not only the first logon and last logoff. Windows AD throws out constant 4624 and 4634 messages while the user is connected, causing any graph to show to straight lines.

Hope this makes my predicament clearer.

0 Karma

woodcock
Esteemed Legend

Based on your clarification, I have a completely different answer that does what I think you are desiring, which is to show the number of concurrently logged-in users for each hour of the day. This will actually give you a single line. Even if the answer is not exactly what you'd like, it should give you enough Splunk-Fu to modify to suit your needs.

0 Karma
Get Updates on the Splunk Community!

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

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...