topic Re: Group users by computer name based on usage in Splunk Search

Group users by computer name based on usage

ykobak — Tue, 06 Jun 2017 00:55:04 GMT

I am trying to display a table of users usage for each individual computer that they have used. I can get the result I want when I search for an individual user using the search below:

index=windows_os user=User3 tag::host=INC000001498678 (EventCode=4624 OR EventCode=4647)
|transaction host startswith="4624" endswith="4647"
|eval "Time" = round(duration/60,0)
|stats sum(Time) count by host
|table host, sum(Time)

RESULT:
host sum(Time)
MU00043103 14
MU00042261 31

What I want to do is set user to * or not specify a user to view all users. I have tried the following:

index=windows_os tag::host=INC000001498678 (EventCode=4624 OR EventCode=4647) user!=SYSTEM user!="ANONYMOUS LOGON" user!=MU*$
| transaction host startswith="4624" endswith="4647"
| table user, host, duration
| eval "Time" = round(duration/60,2)
| table user, host, "Time"
| sort user

RESULTS:
user host Time
User1 MU00041577 105
User2 MU00041691 10
User3 MU00043103 9
User3 MU00042261 22
User3 MU00043103 5
User3 MU00042261 9
User 4 MU00041691 8
User5 MU00081455 3
User5 MU00081455 3
User5 MU00081455 4
User5 MU00081455 3

However, when I use the search above the events are not grouping each user on each computer. The result I would like to see is:

RESULTS:
user host Time
User1 MU00041577 105
User2 MU00041691 10
User3 MU00043103 14
User3 MU00042261 31
User4 MU00041691 8
User5 MU00081455 13

Any help would be much appreciated.

Re: Group users by computer name based on usage

dineshraj9 — Tue, 06 Jun 2017 08:30:25 GMT

Can can modify your search to below -

index=windows_os tag::host=INC000001498678 (EventCode=4624 OR EventCode=4647) user!=SYSTEM user!="ANONYMOUS LOGON" user!=MU*$
| transaction user host startswith="4624" endswith="4647"
| table user, host, duration 
| eval Time = round(duration/60,2) 
| stats sum(Time) as Time by user host
| sort user

You need to add user field in the transaction command, else a transaction may start for a particular user and end for another making data inconsistent. Finally you can take the sum of duration a particular user spent on a host and then sort the results.

Re: Group users by computer name based on usage

niketn — Tue, 06 Jun 2017 08:42:47 GMT

If you want to stick to transaction, you should add user also as your transaction key as suggested by dineshraj9 i.e.

| transaction user host startswith="4624" endswith="4647"

However, transaction is not suitable command for second scenario. It is more suitable when you want to stitch all events together for a single key value like sessionID, or as in your first query you have created the same only for one user and also one host.

Try converting your transaction query to stats:

index=windows_os tag::host=INC000001498678 (EventCode=4624 OR EventCode=4647) user!=SYSTEM user!="ANONYMOUS LOGON" user!=MU*$
| stats count as eventcount min(_time) as MinTime max(_time) as MaxTime values(EventCode) as EventCode by user host
| search eventcount>1 EventCode="4624" EventCode="4647"
| eval duration= MaxTime-MinTime
| eval "Time (in min)" = round(duration/60,2) 
| eval _time=MinTime
| sort user, host
| table _time user host "Time (in min)"

Re: Group users by computer name based on usage

dineshraj9 — Tue, 06 Jun 2017 08:49:28 GMT

I agree that using stats can provide a performance improvement in this case, but transaction supports multiple field list -

http://docs.splunk.com/Documentation/SplunkCloud/6.6.0/SearchReference/Transaction

Optional arguments field-list Syntax: ... Description: One field or more field names. The events are grouped into transactions based on the values of this field. If a quoted list of fields is specified, events are grouped together if they have the same value for each of the fields.

Re: Group users by computer name based on usage

niketn — Tue, 06 Jun 2017 10:13:33 GMT

@dineshraj9 using by user host in stats or transaction user host will give the same result. Events will be aggregated based on both user and host fields in both scenarios. With large dataset transaction may not just run slow, it can treat some records as evicted or orphaned and drop from transaction (keepevicted=t keeporphaned=t).

Although there is no hard-and-fast rule for specific correlation to be used Following flowchart by Nick Mealy
gives and idea of situations where one method might be preferred over another: http://docs.splunk.com/Documentation/Splunk/latest/Search/Abouteventcorrelation

Re: Group users by computer name based on usage

dineshraj9 — Tue, 06 Jun 2017 10:40:13 GMT

Agreed! Your point on evicted and orphaned searches is transactions is right.
Thanks for sharing the flowchart.

Re: Group users by computer name based on usage

ykobak — Tue, 06 Jun 2017 23:17:45 GMT

Thank you so much. I didn't know you can have multiple fields after the transaction command.