Splunk Search

How do I edit my Splunk search to identify the top database queries with the slowest performance?

New Member

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.

0 Karma
1 Solution

Legend

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

View solution in original post

0 Karma

Legend

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

View solution in original post

0 Karma

New Member

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.

0 Karma

Legend

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?

0 Karma

New Member

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.

0 Karma

Legend

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 
0 Karma

New Member

I tried this, but without using Transaction I get TotalDuration as null.

0 Karma

Legend

Can you share some data? Couple of events

0 Karma

New Member

You must have got in the mail alert. Removed now for confidentiality of data.

0 Karma

New Member

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
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!