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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...