The below query is used to return the Error distribution in 3 layers - Application, Dataservice & Queue for a time range two months.
Currently the query takes more than 5 mins to return the result.
index=performance host="prod*" AND host= "/*web/*" earliest=1500076800 latest=1504915200 | eval layer="Application"| append [search index=performance host="prod*" MQ _raw="/*ERROR/*" earliest=1500076800 latest=1504915200 | eval layer="Queue"] | append [search index=performance host="prod*" exception="*sql*" sqlserver OR db2 earliest=1500076800 latest=1504915200 | append[search index=de riak sourcetype=kvs_console "\[error\]" host="prod*" earliest=1500076800 latest=1504915200 ] | append [search index=de host="*prod*" source="*memsql*" "ERROR" earliest=1500076800 latest=1504915200 ]|append [search index=de OR index=app sourcetype="solr_log" SEVERE OR ERROR earliest=1500076800 latest=1504915200 ]|eval layer = "DataService"] |stats count by layer
The query is added a a search panel to dashboard. How can I tune this query so that it gives me results faster?
Try this ...
earliest=1500076800 latest=1504915200
(index=performance host="prod*"
AND ( ( host= "/*web/*" )
OR ( MQ _raw="/*ERROR/*" )
OR ( exception="*sql*" sqlserver OR db2 ) ) )
OR (index=de AND ( ( sourcetype=kvs_console "\[error\]" host="prod*")
OR ( host="*prod*" source="*memsql*" "ERROR" )
OR ( sourcetype="solr_log" SEVERE OR ERROR ) ) )
OR (index=app sourcetype="solr_log" SEVERE OR ERROR )
| eval layer=case(index="app" OR index="de","DataService",
match(host,"\/.*web\/"),"Application",
match(_raw,"MQ") AND match(_raw,"\/.*ERROR\/"),"Queue"
true(),"Application")
| stats count by layer
Please test thoroughly, this is aircode. Start by testing against a 1 minute slice of data and see if the results are the same as your original. Modify the regex in each match()
as needed to correctly identify the source of each event.