I've a below query where I'm filtering out the results of one index "def" from the result of other index "abc". I'm using "NOT" for the same but it takes lot of time. Can I achieve the same result by using two indexes in OR condition?
index=abc sourcetype=xyz
NOT
[search index=def indicator=1
| stats count by cust_no |rename cust_no as account_number|table account_number]
| eval is_graced=if(event=="GRACE",1,0)
| eval is_passed=if(event=="PASS", 1, 0)
| eval is_failed=if(event=="FAIL", 1, 0)
| stats min(_time) as _time ,sum(is_graced) as grace,sum(is_passed) as success, sum(is_failed) as fail by account_number, session_id
| eval only_grace = if(grace > 0 and success = 0 and fail = 0, 1, 0)
| timechart span=1h sum(only_grace) as final_number
How large are the indexes you are searching? How many indexers are doing the searching?
What does the Job Inspector say about how the search is spending its time?
Do you get better time with fields account_number | format
instead of table account_number
?
HI @richgalloway Actually this query is a part of a bigger query. I've used union to join 3 queries where I'm getting the account numbers from each and finally using these account numbers in the 4th query to calculate some numbers. Since many queries have "NOT" function used in the code, it's creating bottleneck for Splunk and I'm getting below errors while running the big query.
Errors -
Dispatch Command Unknown error for indexer: Ivssplunkindex1. Search Results might be incomplete! If this occurs frequently, please check on the peer.
Dispatch Command Unknown error for indexer: Ivssplunkindex2. Search Results might be incomplete! If this occurs frequently, please check on the peer.
The limit has been reached for log messages in info.csv.3 messages have not been written to info.csv Please refer to search.log for these messages or limits.conf to configure this limit
[subsearch]: [subsearch]: Dispatch Command: Unknown error for indexer: Ivssplunkindex1. Search Results might be incomplete! IF this occurs frequently, please check on peer.
One of the solutions which I think might work is rewriting the smaller queries by removing "NOT" and using "Index A OR Index B". And once I've updated these queries, I'll create summary indexes.
However, since I'm new to splunk, therefore I'm unable to rewrite the above query as "INDEX abc" or "INDEX def". I was hoping if someone could help me with that.
In total I'm using 5 indexes.
Thanks for the added information. Multiple joins and unions can definitely slow a search. However, the sample query in your question does neither of those.
Do you have answers for my questions?