Splunk Search

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

rgoyal1010
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

sundareshr
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

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

rgoyal1010
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

sundareshr
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

rgoyal1010
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

sundareshr
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

rgoyal1010
New Member

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

0 Karma

sundareshr
Legend

Can you share some data? Couple of events

0 Karma

rgoyal1010
New Member

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

0 Karma

rgoyal1010
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...