topic Re: How to search and compare three indexes in one search? in Splunk Search

How to search and compare three indexes in one search?

JoshuaJohn — Tue, 09 Aug 2016 15:20:41 GMT

So I had an issue yesterday that was resolved, but ran into something similar that I cannot seem to find a solution to. I want my three indexes to display in one search.

Here are the three indexes:

index=nitro_prod_comm_pci com.impl.AgnosticOmsTokenTranslator "Calling translate"
index=nitro_prod_comm_pci com.impl.AgnosticOmsTokenTranslator "Message translation is successful"
index="nitro_prod_comm_email" INFO EmailType=Order_Confirmation

Getting them to display by index worked when I had two different indexes, but because two of the searches have the same index, it no longer works:

(index=nitro_prod_comm_pci com.impl.AgnosticOmsTokenTranslator "Calling translate") OR (index="nitro_prod_comm_email" INFO EmailType=Order_Confirmation) earliest = -24h| stats count by index | replace nitro_prod_comm_pci with Calling_Translate, nitro_prod_comm_email with Email_Confirmed

Any solutions to get the three indexes to compare with each other?

Re: How to search and compare three indexes in one search?

skoelpin — Tue, 09 Aug 2016 15:23:59 GMT

You can use a sub-search, only drawback is that its slow

index=nitro_prod_comm_pci com.impl.AgnosticOmsTokenTranslator "Calling translate" [search index="nitro_prod_comm_email" INFO EmailType=Order_Confirmation) earliest = -24h| stats count by index | replace nitro_prod_comm_pci with Calling_Translate, nitro_prod_comm_email with Email_Confirmed]

Re: How to search and compare three indexes in one search?

richgalloway — Tue, 09 Aug 2016 15:25:19 GMT

Can you share samples from each index?

Re: How to search and compare three indexes in one search?

JoshuaJohn — Tue, 09 Aug 2016 15:34:22 GMT

Using OR would work for that example, but I need ""Message translation is successful"" also which is why I cannot use the OR method or do stats count by index because the index is the same.

Re: How to search and compare three indexes in one search?

JoshuaJohn — Tue, 09 Aug 2016 15:36:06 GMT

They each provide the number ~10,000, slightly different for each of them I wanted to compare the differences in a piechart

Re: How to search and compare three indexes in one search?

richgalloway — Tue, 09 Aug 2016 15:38:44 GMT

I was wondering about field names. If there is a field common to the 3 event types then that could be used in the query in place of index.

Re: How to search and compare three indexes in one search?

somesoni2 — Tue, 09 Aug 2016 15:41:40 GMT

Try like this

(index=nitro_prod_comm_pci com.impl.AgnosticOmsTokenTranslator ("Calling translate" OR "Message translation is successful")) OR (index="nitro_prod_comm_email" INFO EmailType=Order_Confirmation) earliest=-24h
| eval Type=case(index="nitro_prod_comm_email","Email_Confirmed",match(_raw,"Calling translate"),"Calling_Translate",true(),"Message_Translation_Success") | stats count by Type

Re: How to search and compare three indexes in one search?

JoshuaJohn — Tue, 09 Aug 2016 15:45:58 GMT

Thank you!

Re: How to search and compare three indexes in one search?

JoshuaJohn — Tue, 29 Sep 2020 10:34:15 GMT

So I have been trying this and it appears calling translate and message_translation_success are providing the exact same results but they shouldn't be and when tested individually they dont

Re: How to search and compare three indexes in one search?

sundareshr — Tue, 09 Aug 2016 21:34:41 GMT

Try this

(index=nitro_prod_comm_pci com.impl.AgnosticOmsTokenTranslator ("Calling translate" OR "Message translation is successful")) OR (index="nitro_prod_comm_email" INFO EmailType=Order_Confirmation) earliest=-24h | rex "(?<status>translate|translation|Order_Confirmation)" | stats count by status | reanme translate AS Calling_Translate translation AS "Message translation is successful" Order_Confirmation AS Email_Confirmed

Re: How to search and compare three indexes in one search?

gcusello — Wed, 10 Aug 2016 07:09:07 GMT

try something like this

index=nitro_prod_comm_pci com.impl.AgnosticOmsTokenTranslator "Calling translate" | eval nsearch="search1" | append [ search index=nitro_prod_comm_pci com.impl.AgnosticOmsTokenTranslator "Message translation is successful" | eval nsearch="search2" ] | append [ search index="nitro_prod_comm_email" INFO EmailType=Order_Confirmation | eval nsearch="search3" ] | stats count by nsearch

Bye.
Giuseppe

Re: How to search and compare three indexes in one search?

somesoni2 — Mon, 15 Aug 2016 23:09:29 GMT

Could you provide more info on "providing the exact same result but they shouldn't"?

Re: How to search and compare three indexes in one search?

JoshuaJohn — Tue, 16 Aug 2016 13:44:31 GMT

It was actually my error with the data, your method works just fine! Thank you.