Splunk Search

summarize transaction search

smudge797
Path Finder

Whats the best way to summarize this data and subsequently search the results? The reason i ask is because the docs mention there is a transaction command that may need to be swapped for an si* command?

Here is the full search:

index=win sourcetype="WinEventLog:Security" source="WinEventLog:Security" EventCode=4624 OR EventCode=4634 Account_Name=* action=success  [inputlookup users.csv  | stats count by user  | table user  | rename user as Account_Name]
| eval User=if(mvcount(Account_Name)>1, mvindex(Account_Name,1), mvindex(Account_Name, 0)) 
| eval User=lower(User) 
| search NOT User=*$ 
| transaction User maxevents=2 startswith="EventCode=4624"  endswith="EventCode=4634" maxspan=-1 
| convert timeformat="%Y-%m-%d" ctime(_time) AS date 
| stats sum(duration) As Duration by date, User, ComputerName 
| eval Duration(M)=round((Duration/60), 0) 
| table  date,User,Duration(M),ComputerName

Thanks

1 Solution

woodcock
Esteemed Legend

Avoid transaction like this:

index=win sourcetype="WinEventLog:Security" source="WinEventLog:Security" EventCode=4624 OR EventCode=4634 Account_Name=* action=success [inputlookup users.csv | stats count by user | table user | rename user as Account_Name]
| eval User=if(mvcount(Account_Name)>1, mvindex(Account_Name,1), mvindex(Account_Name, 0)) 
| eval User=lower(User) 
| search NOT User=*$
| streamstats current=t count(eval(EventCode=4634)) AS SessionID BY User ComputerName
| stats earliest(_time) AS date latest(_time) AS latest BY User ComputerName SessionID
| eval Duration=latest - date
| bucket date span=1d
| stats sum(Duration) AS Duration BY date User ComputerName 
| eval Duration(M)=round((Duration/60), 0) 
| table date User Duration(M) ComputerName

View solution in original post

0 Karma

woodcock
Esteemed Legend

Avoid transaction like this:

index=win sourcetype="WinEventLog:Security" source="WinEventLog:Security" EventCode=4624 OR EventCode=4634 Account_Name=* action=success [inputlookup users.csv | stats count by user | table user | rename user as Account_Name]
| eval User=if(mvcount(Account_Name)>1, mvindex(Account_Name,1), mvindex(Account_Name, 0)) 
| eval User=lower(User) 
| search NOT User=*$
| streamstats current=t count(eval(EventCode=4634)) AS SessionID BY User ComputerName
| stats earliest(_time) AS date latest(_time) AS latest BY User ComputerName SessionID
| eval Duration=latest - date
| bucket date span=1d
| stats sum(Duration) AS Duration BY date User ComputerName 
| eval Duration(M)=round((Duration/60), 0) 
| table date User Duration(M) ComputerName
0 Karma

smudge797
Path Finder

That's cool and runs quicker.
Can date be converted as it was?

| convert timeformat="%Y-%m-%d" ctime(_time) AS date

Also there a way to Aggregate the number of events per day/ per user?

Thanks!

0 Karma

woodcock
Esteemed Legend

Right now it does duration/day/user/ComputerName. To change it to duration/day/user, just Remove every occurrence of "ComputerName" in the search. Or, if you need this in addition (both), then just add this to your search:

| appendpipe | stats sum(Duration) BY date User

To change the date, just add this to the end of the search:

| fieldformat date=strftime(date, "%Y-%m-%d")
0 Karma
Get Updates on the Splunk Community!

This Week's Community Digest - Splunk Community Happenings [9.26.22]

Get the latest news and updates from the Splunk Community here! Upcoming User Group Events! 👏 Check ...

BSides Splunk 2022 - The Call for Papers is now Open!

TLDR; Main Site: https://bsidessplunk.com CFP Site: https://bsidessplunk.com/cfp CFP Opens: December 15th, ...

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...