topic Re: Combine duplicate values from two search results in Getting Data In

Combine duplicate values from two search results

Smith_Splunk — Wed, 25 Jun 2014 05:31:46 GMT

Hi,

This question is slightly different from other related question. I have searched all the Splunk answers and couldn’t able to locate properly

I have a requirement to display all the exceptions along with last 5 mins and total count and also highlight the exceptions from the new release version in a table format.

I have formulated the two search queries based on the two different timestamp. The first is query is to run for the previous build versions timestamp and second query is to run for current build timestamp

The query is below,

index=abc earliest=1394424000 latest=1394510400| chart count by ExceptionList | append[search index=abc earliest=1394510400 latest=1394596800 | chart count  by ExceptionList|]

The results are,

**Results from query 1**
**ExceptionList                     count**
java.lang.IllegalArgumentException              1
java.nio.file.FileSystemNotFoundException           24
java.lang.IllegalStateException             15
java.lang.NullPointerException              15
com.ibm.db2.jcc.am.SqlException             1
**Results from query 2**
java.nio.file.FileSystemNotFoundException           20
java.lang.IllegalStateException             8
java.lang.FileNotFoundException             17
java.lang.StringIndexOutOfBoundsException           1
javax.xml.bind.MarshalException             2

From the above results “java.lang.IllegalStateException” & “java.nio.file.FileSystemNotFoundException” is repeated twice. I need to combine similar exception from two search results and display the total count.

The results should like,

**ExceptionList                     count**
java.lang.IllegalArgumentException              1
java.nio.file.FileSystemNotFoundException           44
java.lang.IllegalStateException             23
java.lang.NullPointerException              15
com.ibm.db2.jcc.am.SqlException             1
java.lang.NullPointerException              17
java.lang.StringIndexOutOfBoundsException           1
javax.xml.bind.MarshalException             2

Can anyone please suggest the approach to get above results.

To display Last5hrs and totalCount I have used below query. But while displaying the Exception count it’s not proper.

eval label = if(now() - _time <= 5*3600, "last5hrs;totalCount", "totalCount")) | makemv label delim=";" | chart count by ExceptionList label

Please suggest better approach or solutions for this use case.

Thanks,
Smith

Re: Combine duplicate values from two search results

gfuente — Wed, 25 Jun 2014 07:26:42 GMT

Hello

You can get this by using the "cluster" command, that groups similar events into clusters.

Doc reference:

http://docs.splunk.com/Documentation/Splunk/6.1.1/SearchReference/Cluster

Regards

Re: Combine duplicate values from two search results

martin_mueller — Wed, 25 Jun 2014 07:46:51 GMT

Are you possibly looking for this?

index=abc earliest=1394424000 latest=1394596800 | chart count  by ExceptionList

That will automatically sum up exceptions occurring in both time ranges.

Re: Combine duplicate values from two search results

martin_mueller — Wed, 25 Jun 2014 07:48:07 GMT

Also, your query at the bottom calculates five hours but uses five minutes as a label - that's probably unintended. Take a look at eval's relative_time() function for doing time-based calculations.

Re: Combine duplicate values from two search results

Smith_Splunk — Thu, 26 Jun 2014 13:46:04 GMT

Hi,

Thanks for quick response.

"Cluster" command is used to group similar events irrespective of timestamp. But in our case we need to get the different exceptions based on the two different timestamp.

Eg:
Release 1.0 - earliest=1394424000 latest=1394510400
Release 1.2 - earliest=1394510400 latest=now()

earliest is the time at which particular release is deployed

ExceptionList - We have extracted using regex from the log

Combine these 2 results and highlight newly added ExceptionList produced by Release 1.2

could you please provide the approach to achieve the same.

Regards,
Smith

Re: Combine duplicate values from two search results

Smith_Splunk — Mon, 28 Sep 2020 16:56:05 GMT

Hi Mueller,

My requirement in a nutshell - "Identify & Highlight the Exceptions newly introduced by the latest release deployment ".

We need to compare the list of exceptions generated in previous Release-say Rel_1.0, with the latest release Rel_1.1.

Hence we have two time ranges corresponding to the deployment time.

For Eg: Rel_1.0 deployed on 01-Jun-2014 and Rel_1.1 on 25-Jun-2014 (11 AM)

In this case i will get the exception list (extracted from the logs) from time range 01-June till 25-June(11 AM) for Rel_1.0 and 25-June(11 AM) till now for Rel_1.1
.....continue

Re: Combine duplicate values from two search results

Smith_Splunk — Thu, 26 Jun 2014 14:06:32 GMT

Example:

Rel_1.0
Exception Count
java.lang.NullPointerException 5
java.lang.illegalArgumentException 3

Rel_1.1
Exception Count
java.lang.NullPointerException 2
java.lang.FileNotFoundException 1

Our End Result should be

Exception Count
java.lang.NullPointerException 2
*java.lang.FileNotFoundException 1 *

** - Highlight Newly introduced exception

Kindly provide your valuable suggestions.

Regards,
Smith

Re: Combine duplicate values from two search results

somesoni2 — Thu, 26 Jun 2014 14:49:17 GMT

Give this a try

|multisearch [search index=abc earliest=1394424000 latest=1394510400 | eval label="Release 1.0"][ search index=abc earliest=1394510400 latest=now| eval label="Release 1.2"] | stats count, values(label) as label by ExceptionList | eval IsNew=if(mvcount(label)=1 AND mvindex(label,0)="Release 1.2","YES","NO") | table ExceptionList , count ,IsNew

Re: Combine duplicate values from two search results

martin_mueller — Thu, 26 Jun 2014 15:50:18 GMT

I see. Do test @somesoni2's suggestion, it might do what you need.

Re: Combine duplicate values from two search results

Smith_Splunk — Fri, 27 Jun 2014 13:52:26 GMT

Thanks Somesoni. It works as expected.

We have one more addition to the above requirement :

Need to display Last 5 Minutes along with TotalCount in same table

We tried below query and the results are not proper.
|eval range=if(now - _time <= 5*60, "last5Mins;TotalCount", "TotalCount") | makemv range delim=";"

could you please provide the approach to display Last5Mins count as well.

Regards,
Smith

Re: Combine duplicate values from two search results

somesoni2 — Fri, 27 Jun 2014 13:57:11 GMT

can you provide the expected table with Last5mins count? This last 5min count would be just for latest release?

Re: Combine duplicate values from two search results

martin_mueller — Fri, 27 Jun 2014 14:54:41 GMT

Based on the above search I'd suggest this:

| multisearch
  [search index=abc earliest=1394424000 latest=1394510400 | eval label="Release 1.0"]
  [search index=abc earliest=1394510400 latest=now | eval label="Release 1.2"]
| eval Last5Mins = if(_time >= relative_time(now(), "-5m"), 1, 0)
| stats count, sum(Last5Mins) as Last5Mins values(label) as label by ExceptionList 
| eval IsNew=if(mvcount(label)=1 AND mvindex(label,0)="Release 1.2","YES","NO")
| table ExceptionList, count, IsNew

For each Exception type you'd get a field with its count in the last 5 minutes.

Re: Combine duplicate values from two search results

Smith_Splunk — Mon, 30 Jun 2014 06:21:53 GMT

Thanks Martin & Somesoni for your help. Its working as our expectation. I really need to explore lot of stuffs in SPL

Thanks for your guidance .

The final results,

ExceptionList Last5Mins count IsNew

com.ibm.db2.jcc.am.SqlException 0 1 YES
java.lang.IllegalArgumentException 0 2 YES
java.lang.IllegalStateException 1 3 NO
java.lang.RuntimeException 0 7 NO
java.nio.file.FileSystemNotFoundException 0 5 NO

Re: Combine duplicate values from two search results

Smith_Splunk — Mon, 30 Jun 2014 06:27:48 GMT

Both above query and below one are working fine

|multisearch [search index=abc earliest=1403508847 latest=1403688907| eval label="Release 1.0"][ search
index=abc earliest=1403688907 latest=now| eval label="Release 1.2"] | eval timeRange = if(1403697000 - _time <= 5*60,
1, 0)|stats count, sum(timeRange) as Last5Min, values(label) as label by ExceptionList | eval IsNew=if
(mvcount(label)=1 AND mvindex(label,0)="Release 1.2","YES","NO") | table ExceptionList , Last5Min, count ,IsNew