I have a Splunk search that extracts from the events for various queries executed and time taken by them. I want to find the top 10 queries in terms of total runtime irrespective of the count of runs. Currently am using this:
index=web_query Query!="" OR ReqEnd!="" OR ReqEnd!="*health" | eval Duration = round(ExecMs/60000,2) | rex field=Query mode=sed "s/\/\*.*\*\/\s*//g"| rex field=Query mode=sed "s/\d/X/g" | rex field=Query mode=sed "s/'.*'/''/g" | transaction Id | stats sum(Duration) as TotalDuration by Query | sort -TotalDuration"
But this is not giving me the correct result. For eg, there is one query that took 40 minutes, it is reflected in the 4 hour time span, but not in the 24 hour.
Try this
index=web_query Query!="" OR ReqEnd!="" OR ReqEnd!="*health" | eval Duration = round(ExecMs/60000,2) | rex field=Query mode=sed "s/\/\*.*\*\/\s*//g"| rex field=Query mode=sed "s/\d/X/g" | rex field=Query mode=sed "s/'.*'/''/g" | stats earliest(_time) as start latest(_time) as end by Query | eval TotalDuration=end-start | sort - TotalDuration |...
Try this
index=web_query Query!="" OR ReqEnd!="" OR ReqEnd!="*health" | eval Duration = round(ExecMs/60000,2) | rex field=Query mode=sed "s/\/\*.*\*\/\s*//g"| rex field=Query mode=sed "s/\d/X/g" | rex field=Query mode=sed "s/'.*'/''/g" | stats earliest(_time) as start latest(_time) as end by Query | eval TotalDuration=end-start | sort - TotalDuration |...
No this will not give the expected result. Duration here needs to be fetched from the ExecMs field already being printed in the log. Not sure what duration is being computed using time parameter here.
How many events are logged per query? If it only one, with the ExecMs as the duration for that query, what was the reason for the transaction command in your original query. Don't you already have the Duration for each query, sort by that and get head 10? What am I missing?
There are multiple events per query. And the duration here is not the duration of a Splunk query or an event. I am parsing log for an application that runs database queries against Oracle. So each of these queries has a runtime logged into ExecMs. query a takes 5 sec, query b takes 10 sec, query a is again fired and takes 10 seconds. Eventually I want my splunk result to tell that query a in total ran for 15 seconds and b for 10. Hope that explains.
Try this (you don't need the transaction command. Besides, transaction create multivalue fields)
index=web_query Query!="" OR ReqEnd!="" OR ReqEnd!="*health" | eval Duration = round(ExecMs/60000,2) | rex field=Query mode=sed "s/\/\*.*\*\/\s*//g"| rex field=Query mode=sed "s/\d/X/g" | rex field=Query mode=sed "s/'.*'/''/g" | stats sum(Duration) as TotalDuration by Query
I tried this, but without using Transaction I get TotalDuration as null.
Can you share some data? Couple of events
You must have got in the mail alert. Removed now for confidentiality of data.
Thanks Sundareshr for your help. Below query finally worked for me:
index=web_query (Query!="" OR ReqEnd!="") [search index=web_query host=prod* ReqEnd!="*health" | fields Id] | transaction Id | rex field=Query mode=sed "s/\/\*.*\*\/\s*//g"| rex field=Query mode=sed "s/\d/X/g" | rex field=Query mode=sed "s/'.*'/''/g" | transaction Id | eval Duration = round(ExecMs/60000,2) | rename distinct_count(Id) as Count | stats sum(Duration) as TotalDuration, Count by Query | sort -TotalDuration | head 10