Count the Number Times Individual Users Login/LogOff/Close/TimeOut

[genesiusj]

Hello,
There are four different states of ID usage, which have the following field names: Login_ID, Logoff_ID, Closed_ID, and Timed_Out_ID.
The values in each of these fields may appear more than once and in more than one field.
Ex.: User aaho could appear in Login_ID 5 times, Logoff_ID 3 times, Closed_ID 1 time, and Timed_Out_ID 2 times.

Here is what we are looking to do. Below is the final example of what would like our table/report to look like.

Started with using the stats command with the values function to produce the 4 different states of ID usage.
Question. How do I display the counts of each ID in each state of ID usage. I figure it is a combination of the eval command, but not sure how to construct it.

Not enough karma points to include more pics, or URLs, code, etc.

Thanks in advance for any assistance/guidance you can provide.
God bless,
Genesius

[Vijeta]

@genesiusj Try this-

<your search> |  streamstats values(Login_ID) as user by Login_ID|streamstats values(Logoff_ID) as user by Logoff_ID|streamstats values(Closed_ID) as user by Closed_ID|streamstats values(Timed_out_ID) as user by Timed_out_ID| stats count(Login_ID) as login, count(Logoff_ID) as logoff, count(Closed_ID) as closed, count(Timed_out_ID) as time_out by user

[genesiusj]

@sukisen,
I am a newbie to Splunk (currently working Fundamentals 1). I didn't think you needed all of my SPL, but from your informative answer, I see that I did not give enough info.

First, the four states of ID usage: Login_ID, Logoff_ID, Closed_ID, and Timed_Out_ID are not fields within the events. These are parsed at search time using rex commands against the field Message.

Here is my full SPL with comments (in italics for readability).

======
index=ABC host=DEF.ME.COM File=session.c
comment("This rex command parse the Message field into new fields: Login_ID, Logoff_ID, Closed_ID, and Timed_Out_ID.")
| rex field=Message "login by \S+ (?\S+)|\w{7}\s(?\w+)-\S+\ logoff;|\w{7}\s(?\w+)-\S+\ ended without logout;|\w{7}\s(?\w+)-\S+\ timed out;"

comment("This rex command cannot be included in the above rex because once a match is found processing moves on to the next command; session count is in every event")
| rex field=Message "session count (?\d+)"

comment("This bin command arranges the field into descrete sets. In this case, the timestamp field is arranged into 1-hour buckets; minutes, seconds, etc. will not be used/displayed.")
| bin span=1h _time
comment("This stats command, with the count by _time, will arrange the output by hour over the past 24 hours.")

| stats count by Login_ID

You have been very helpful and have given me some things to think about.
I'm going to try the follow, based your answer above, and would like your comments.
Can I run a sub-search for each rex command parsing only one of the states of ID usage?

I'll try and let you know.

Thanks and God bless,
Genesius

[Sukisen1981]

Can I run a sub-search for each rex command parsing only one of the states of ID usage? - Yes, you can be aware though that there is a soft limit in sub searches and it does tend to degrade performance, that ebing said unless you have more than 20-25Kof data rows(events) to loop through in the sub serach.
It does not matter if the fields are default meta data fields or rex fields, as long as the field extraction works stats will work just the same.
I have tested my code on the defauly _audit index. Hopefully you will have access to this index , or install splunk locally and by default you will have access to the _audit index. Now, here is the code I tried index="_audit"
| stats count as count1 by info
| appendcols
[ search index="_audit"
| stats count as count2 by action ]
| appendcols
[ search index="_audit"
| stats count as count3 by user ]

As you can see, this fetches the count for the fields info,action and user respectively. I was thinking that this is what you need, can you first run this, check the output and confirm that this is what you needed? Of course, your index, fields will be specific to your needs

[Sukisen1981]

Try this - index=abc| stats count as count1 by Login_ID|appendcols [search index=abc | stats count as count2 by Logoff_ID] |appendcols [search index=abc| stats count as count3 by Closed_ID 1] |appendcols [search index=abc| stats count as count4 by Timed_Out_ID]

Replace abc by your index name

[genesiusj]

@Sukisen
Yes. For each individual state of ID usage, that stats count will work.
Thanks and God bless,
Genesius

[Sukisen1981]

just to understand your requirement clearly - if you do |stats count by Login_ID

Are you getting the expected results for log in at least?