Query Performace

[dglass0215]

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

[bowesmana]

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.

[dglass0215]

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

[bowesmana]

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.

[manjunathmeti · Accepted solution]

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")

[dglass0215]

Thank you.  This definitely helped to speed it up.