Splunk Search

Query works fine but takes lot of time to execute. Can someone help me with other approach?

amaurya1
Explorer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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?

---
If this reply helps you, Karma would be appreciated.
0 Karma

amaurya1
Explorer

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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?

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...