topic Re: Query Performace in Splunk Search

Query Performace

dglass0215 — Tue, 27 Apr 2021 18:02:58 GMT

Hello,

I am building a query to be able to display a line graph of status (offline, online) over a period of 30days. Query currently is so slow it usually doesn't finish. looking for assistance to see if I can do something different to speed it up. thanks!

Current query:

index=mydata sourcetype="mySourceType" (_raw=*offline* OR _raw=*online*)
| eval status=if(like(_raw, "%offline%"),"Offline","Online")
| timechart span=1d count by status

Re: Query Performace

manjunathmeti — Tue, 27 Apr 2021 18:38:06 GMT

hi @dglass0215,
You don't need to use wildcard (*) in the base search. Try the below query without the timechart first and check the performance. Then add timechart and check the performance.

index=mydata sourcetype="mySourceType" offline OR online | rex "(?<status>offline)" | eval status=if(status="offline","Offline","Online")

Re: Query Performace

bowesmana — Wed, 28 Apr 2021 04:47:16 GMT

Create a field extraction where you extract the current status from raw for that sourcetype, so when you search for it, you can search for all records with status, e.g.

index=mydata sourcetype="mySourceType" status=* | timechart span=1d count by status

otherwise the leading wildcard combined with _raw= is going to have to search all the data for that sourcetype for your time period. Any row that does not have an extracted status field will not be found.

Re: Query Performace

dglass0215 — Wed, 28 Apr 2021 13:22:57 GMT

Not sure how to do that when the data is unstructured.

Re: Query Performace

dglass0215 — Wed, 28 Apr 2021 13:23:29 GMT

Thank you. This definitely helped to speed it up.

Re: Query Performace

bowesmana — Wed, 28 Apr 2021 22:23:19 GMT

Take a look at the field extractor in the UI - you can get there from a raw event, via the Event Actions drop down. Splunk is great at unstructured data and it's really good to take a look at how you do field extractions, as it will help you a lot.

You can extract fields based on regular expressions or delimiters, if you don't know much about regex, then https://regex101.com is a good place to look to play with regex.