Splunk Search

Group users by computer name based on usage

ykobak
New Member

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.

Tags (2)
0 Karma
1 Solution

dineshraj9
Builder

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.

View solution in original post

0 Karma

niketn
Legend

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)"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

dineshraj9
Builder

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.

0 Karma

niketn
Legend

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

dineshraj9
Builder

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

0 Karma

dineshraj9
Builder

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.

0 Karma

ykobak
New Member

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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