I get a nice table with the logon and logoff times per user using the following search -
| 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?
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
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.
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.
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.
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