topic Re: Compare two search results in Splunk Search

How to run a search that compares errors in results from two different indexes?

raj11 — Mon, 07 Sep 2020 17:13:00 GMT

I have two searches below:

index=dev 'error'

index=prod 'error'

I want to run the above searches together for the same time period and find the unique errors present in search results for 1st query and NOT in the second query and vice-versa.

Re: Compare two search results

ITWhisperer — Sun, 06 Sep 2020 10:17:14 GMT

Simplistically, you could use both indexes and search for all errors, count the number of times they occur and just keep them where the count is 1

index=dev OR index=prod "error" | rex "(?<error>some match to extract your error into a field)" | stats count by error | where count = 1

Re: Compare two search results

raj11 — Sun, 06 Sep 2020 15:10:06 GMT

Thank you @ITWhisperer for the reply. The developers are looking for a unique list of errors in the below format. For example: Suppose below are the results for two queries (unique errors are Italic)

index=prod "errors"

1.error in apple

2. error in banana

3. error in orange

4. error in orange

5. error in apple

6. error in banana

index=dev "errors"

1.error in apple

2. error in banana

3. error in kiwi

4. error in kiwi

5. error in watermelon

6. error in apple

7. error in banana

The query results should look like below:

Unique errors in Prod:

error pattern count

error in orange 2

Unique errors in Dev:

error pattern count

error in kiwi 2

error in watermelon 1

Re: Compare two search results

ITWhisperer — Sun, 06 Sep 2020 15:20:50 GMT

OK so you first need to work out which errors occur in each environment then count them

index=dev OR index=prod "error" | rex "(?<error>some match to extract your error into a field)" | stats values(error) as error by index | stats values(index) as index, count by error | where count = 1

Re: Compare two search results

niketn — Sun, 06 Sep 2020 17:03:07 GMT

@raj11 try the following:

index IN ("dev","prod") "error" | stats count(eval(searchmatch("error"))) as ErrorCount by index

Alternatively, if you know how error values in your raw data is segmented you can also check out the PREFIX directive with tstats (available in Splunk 8.0.0 onward). As you would know tstats will run much faster.

Refer to the documentation: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Tstats#Usage

Or following Clara-fication blog on Search Best Practices: https://www.splunk.com/en_us/blog/tips-and-tricks/splunk-clara-fication-search-best-practices.html

Re: Compare two search results

raj11 — Mon, 07 Sep 2020 13:02:05 GMT

Thank you for the reply!! In the example above I do not want all the error count in both indexes.

The error count is needed for unique errors

1. Get all the errors for a time period for both indexes

2. Compare results for both indexes and display

a. Unique error count for unique errors in index 1 and NOT in index 2

b. Unique error count for unique errors in index 2 and NOT in index 1

Re: Compare two search results

raj11 — Mon, 07 Sep 2020 14:59:56 GMT

@niketn Thank you for your reply. I am new to splunk so please help! In the above query how can i exclude the sourcetype 'warn' to exclude warnings ?

Also I need to dynamically consider the error patterns and the since I do not know what the new error pattern would be, I am only looking for "ERROR" for now.

Re: Compare two search results

niketn — Mon, 07 Sep 2020 15:26:16 GMT

@raj11 please try the following

index IN ("Prod","Dev") AND "error in" | rex "error in (?<Error_Component>.*)" | stats values(index) as index count by "Error_Component" | search index IN ("Dev","Prod") NOT (index=Dev AND index=Prod) | sort - index

Following is a run anywhere example based on the sample Data and details provided

If the above does not work, in order for the community to assist you better

1. Please add more details like some sample events which you can mock/anonymize as per sensitivity of data.

2. Please provide the solution you have tried and where do you think you are failing.

3. What kind of events are we talking about? Are these custom 3rd party tools or from standard known technology?

4. If this is custom log, any reason why field extraction is not in place? Is the raw text event or of standard format like csv/tsv or any other auto-discover data for automatic field extraction?

Re: Compare two search results

niketn — Wed, 09 Sep 2020 06:18:47 GMT

@raj11 just use sourcetype!='warn'

Also if you are new to Splunk I would recommend you to go through Splunk Fundamentals courses on Splunk Education.

Fundamentals 1 is free for everyone. Fundamentals 2 should be free if you are working for Splunk Partner and your splunk login account uses partner domain in the email address (not personal email).

Re: Compare two search results

raj11 — Thu, 10 Sep 2020 17:18:08 GMT

Hi @niketn ,

Thank you for your inputs on this. I have tried excluding the warnings using the above and still did not get the desired result. I think the issue is with the requirement itself. The developers are asking to just look for ERROR logs using the key word "error" and not a pattern. All the errors are being listed with the query but the challenge is to categorize those errors as unique. For example an exception like below:

[2020-09-10 16:46:08.696 GMT] ERROR ShopAPIServlet|1070221786|/servlet/s/Sites-Site/dw/shop/v19_3/orders/2300010101/payment_instruments custom.OCAPI [] OCAPI:{"orderNumber":"2300010101","orderStatus":"NEW","orderSource":"APP"}

[2020-09-10 16:44:11.182 GMT] ERROR ShopAPIServlet|225074421|/servlet/s/Sites-Site/dw/shop/v19_3/orders/23053842/payment_instruments custom.OCAPI [] OCAPI:{"orderNumber":"23053842","orderStatus":"NEW","orderSource":"APP"}

With just ERROR splunk categorizes these exceptions as unique as I am not ignoring the order number for example....which is expected.

Since they are trying to extract the new error patterns without knowing the what the new patterns would be it ..I am finding it hard to suggest a solution.

Re: Compare two search results

ITWhisperer — Thu, 10 Sep 2020 21:33:35 GMT

Hi @raj11

You are right "the issue is with the requirement itself." What constitutes uniqueness in these entries?

[2020-09-10 16:46:08.696 GMT] ERROR ShopAPIServlet|1070221786|/servlet/s/Sites-Site/dw/shop/v19_3/orders/2300010101/payment_instruments custom.OCAPI [] OCAPI:{"orderNumber":"2300010101","orderStatus":"NEW","orderSource":"APP"}

All these different coloured parts might be useful parts to be taken into account when determining what a unique error class is. Some more useful than others, e.g. time and order number are probably not useful , whereas servlet or parts of the url or class(?) might be. You probably need to clarify this before proceeding.