topic Re: Optimize transaction query in Splunk Search

Optimize transaction query

AnujaJ — Thu, 20 Dec 2018 15:42:23 GMT

Hello I have a transaction query which I would like to optimize. It is impossible to run the query for a few hours. Is there anyway I can rephrase the query to have the same results?

Query: transaction Id1,Id2 startswith=login endswith=logout keepevicted=true

A unique event is mapped by combination of Id1 and Id2
I want to map all users who have logged in and logged out in the window
Also all users who have logged in but not logged out
And finally users who have logged out in the given time frame.

I am not sure how can I use subsearches or xyseries to optimize this query.

Re: Optimize transaction query

skoelpin — Thu, 20 Dec 2018 16:27:24 GMT

Can you paste your whole query? I strongly suggest users not to use the transaction command as it's non-streaming (i.e. forces the results to be pushed to the search head which loses the parallalism of Splunk).

Re: Optimize transaction query

AnujaJ — Tue, 29 Sep 2020 22:27:14 GMT

index=shop sourcetype="shop1" (component=logout) OR (component=login) Id1!=NULL
|transaction Id1,Id2 startswith=login endswith=logout keepevicted=true
|table Id1,Id2,api,duration,_time,evicted
|fillnull value=0
| eval l_time=relative_time(now(),"-15m")
| eval duration=if(match(api,"logout"),_time-l_time,if(match(api,"login"),now()-max(_time),duration))
| convert dur2sec(duration) AS duration
| search duration>360
|table Id1,Id2,api,duration
|stats sum(duration) as watch_time, count(Id1) as total_Ids by Id2
|eventstats sum(total_Ids) as total
|eval total_time =round(time/60,2),quoten=(total_Ids/total)*100
|eval average=total_time/total_Ids
|lookup userLookup Id2 as Id2 OUTPUT username as username
|table Id2, username,total_time, total_Ids,quoten,average | sort -quoten

Re: Optimize transaction query

DalJeanis — Wed, 02 Jan 2019 15:37:21 GMT

Transaction, while a powerful command, is also very resource-intensive. When possible, you should always try to use stats, eventstats or streamstats instead of transaction.

This method gets you what the transaction got you, but with much less machine cost.

 index=shop sourcetype="shop1" (component=logout) OR (component=login) Id1!=NULL
| fields _time Id1 Id2 api component
| eval login_time=case(component="login",_time)
| eval logout_time=case(component="logout",_time)

| rename COMMENT as "Assign a login group number to each login for each Id combination"   
| sort 0 _time 
| streamstats count(login_time) as groupno by Id1 Id2 

| rename COMMENT as "Calculate the duration of each group" 
| stats min(login_time) as login_time max(logout_time) as logout_time range(_time) as duration by Id1 Id2 groupno  

| rename COMMENT as "Set defaults for missing logins and logouts" 
| eval login_time=coalesce(login_time,  some default start time of your choice...)
| eval logout_time=coalesce(logout_time,  some default end time of your choice...)

From that point, your logic should be pretty much the same.

Note - your logic shows both fields component and api as being tested for values "login" and "logout". We assumed component was correct.

Re: Optimize transaction query

woodcock — Thu, 03 Jan 2019 01:37:15 GMT

If you post sample events and a desired final output, I will be able to get you an answer.

Re: Optimize transaction query

AnujaJ — Wed, 09 Jan 2019 09:50:55 GMT

I did step 14,15 (coalesce) before I did stats(step 11) so that he counts the evicted results as well. And instead of range I just use eval to substract time between logout and login. Somehow range was not able to calculate the correct duration. Thank you for your help.