Getting Data In

Combine duplicate values from two search results

Smith_Splunk
Explorer

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

Tags (3)
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

Smith_Splunk
Explorer

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

0 Karma

Smith_Splunk
Explorer

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

Smith_Splunk
Explorer

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

0 Karma

Smith_Splunk
Explorer

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

0 Karma

Smith_Splunk
Explorer

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

gfuente
Motivator

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

0 Karma

Smith_Splunk
Explorer

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

0 Karma
Get Updates on the Splunk Community!

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

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...