topic Re: summarize transaction search in Splunk Search

summarize transaction search

smudge797 — Thu, 17 Mar 2016 16:29:03 GMT

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

Re: summarize transaction search

woodcock — Sun, 20 Mar 2016 23:26:52 GMT

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

Re: summarize transaction search

smudge797 — Mon, 21 Mar 2016 09:47:03 GMT

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!

Re: summarize transaction search

woodcock — Mon, 21 Mar 2016 14:18:48 GMT

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")