Splunk Search

## How to get new exceptions stats by comparing two time ranges?

Path Finder

Hello splunk,

I'm trying to compare the exceptions between time ranges and get the new exceptions list. Suppose consider I'm searching for 3to4am stats of exceptions and 7to 8am stats of exceptions. I should compare these two stats and list out what are the new exceptions occurred at 7to8am.

Example:

3to4am stats:         7to8am

Ex1                                Ex3

Ex2                                Ex1

Ex3                                Ex5

Ex4                                Ex6

Result:

Ex5

Ex6

Labels (4)

• ### subsearch

1 Solution
SplunkTrust

Have you tried the query?  Semantically, the following pairs of expressions are equivalent

 3am to 4 am hour > 2 AND hour < 5 hour >= 3 AND hour <= 4 7am to 8 am hour > 6 AND hour < 9 hour >= 7 AND hour <= 8

Each represents the same two-hour interval. (I interpret 3am to 4am as "3am to 4am inclusive".)

Or do you mean 3am to 4am is supposed to be a one-hour interval, as in "3am to 4am exclusive"?  If so, simply change to

``````| eval hour = strftime(_time, "%H")
| where hour IN ("3", "7")
| stats values(exception) as exception by hour
| stats values(hour) as hour by exception
| where mvcount(hour) == 1 AND hour == "7"
| stats values(exception) as new_exception_7``````
SplunkTrust
``| eval 7to8am=mvmap('7to8am',if(isnull(mvfind('7to8am',mvjoin('3to4am',"|"))),'7to8am',null()))``
Path Finder

How do we mention time here?. I think the mvmap('7to8am',if), doesn't work. Can you help me with full query here?

SplunkTrust

Sorry, I missed the line to gather the values into multivalue fields.

Here is a runanywhere example using your data showing it working

``````| makeresults
| fields - _time
| eval _raw="3to4am stats:         7to8am
Ex1                                Ex3
Ex2                                Ex1
Ex3                                Ex5
Ex4                                Ex6"
| table 3to4am 7to8am
``` the lines above create dummy data based on your example ```
| stats values(3to4am) as 3to4am values(7to8am) as 7to8am
| eval 7to8am=mvmap('7to8am',if(isnull(mvfind('7to8am',mvjoin('3to4am',"|"))),'7to8am',null()))``````
Path Finder

You understood it wrong. The data will be real time data. For the example sake i have mentioned those 3 to4am stats like that. But in real, i need to calculate those too. You should not define eval field and assign exceptions

SplunkTrust

In that case, please can you provide some sample data (anonymised of course) that we can work with?

Path Finder

Let's say you have one field extraction that extracts Exception from real time events. Example null pointer exception, Illegal argument exception, socket time out exception etc.. If i search set the time for the whole day, i need to search compare exception stats of 7to8am stats with 3to4am stats.

SplunkTrust
``````| gentimes start=-1 increment=30m
| rename starttime as _time
| fields _time
| eval Exception=mvindex(split("null pointer exception,Illegal argument exception,socket time out exception",","),random()%3)
| bin _time span=1h
| eval hour=strftime(_time,"%H")
| where hour == 3 OR hour == 7
| eval hour=tonumber(hour)."to".(tonumber(hour)+1)."am"
| stats values(Exception) as Exception by hour
| fields - Exception
| stats values(3to4am) as 3to4am values(7to8am) as 7to8am
| eval 7to8am=mvmap('7to8am',if(isnull(mvfind('7to8am',mvjoin('3to4am',"|"))),'7to8am',null()))``````
Path Finder

I don't know why gentimes used here. I have clearly mentioned the time ranges and also you don't have to list the Exceptions in eval command. As I said it will real time data and can be extracted through field name. And also how do I mention index and source here?

SplunkTrust

Since you haven't provided example data, the first 4 lines (starting with gentimes and finishing with eval) just generate sample data and should be replaced by your search.

It is usually easier for volunteers to suggest solutions if you provide sample data so we can test our solutions before making suggestions.

If you are unsure, please feel free to investigate the documentation as to what these unfamiliar commands do.

SplunkTrust

Assuming the events has a field named exception, you first group events into intervals of interest, namely 3 to 4 and 7 to 8, respectively, then group exceptions into these intervals, then find the difference, like this.

``````| eval hour = strftime(_time, "%H")
| eval interval = case(hour > 2 AND hour < 5, "3_4", hour > 6 AND hour < 9, "7_8")
| stats values(exception) as exception by interval
| stats values(interval) as interval by exception
| where mvcount(interval) == 1 AND interval == "7_8"
| stats values(exception) as new_exception_7_8``````
Path Finder

Yes, I do have one Exception field. But when i ran this query with that modification and set the time range for whole day. I need to compare stats of only 7to 8am stats with 3 to 4am stats.

SplunkTrust

Have you tried the query?  Semantically, the following pairs of expressions are equivalent

 3am to 4 am hour > 2 AND hour < 5 hour >= 3 AND hour <= 4 7am to 8 am hour > 6 AND hour < 9 hour >= 7 AND hour <= 8

Each represents the same two-hour interval. (I interpret 3am to 4am as "3am to 4am inclusive".)

Or do you mean 3am to 4am is supposed to be a one-hour interval, as in "3am to 4am exclusive"?  If so, simply change to

``````| eval hour = strftime(_time, "%H")
| where hour IN ("3", "7")
| stats values(exception) as exception by hour
| stats values(hour) as hour by exception
| where mvcount(hour) == 1 AND hour == "7"
| stats values(exception) as new_exception_7``````
Path Finder

I have added index, source and modified Exception field name that's it and set the time to present day. In this case it should fetch 3 to 4am exception stats with 7to8am stats.

SplunkTrust

You must realize that "isn't working" conveys little meaning in the best of scenarios, much less to volunteers who have little knowledge about your particular application and data.  What is "not working"?  What do your raw data look like?  What is the result you are expecting?  You haven't even answered whether 3 to 4am means a one-hour interval (exclusive) or two-hour interval (inclusive).

To give you an example of illustrating your raw data, let me present an emulation that results in the following dataset

 _time exception 2023-06-05 00:00:05 Ex6 2023-06-05 02:02:05 Ex6 2023-06-05 03:03:05 Ex1 2023-06-05 03:03:15 Ex2 2023-06-05 03:03:25 Ex4 2023-06-05 03:03:45 Ex3 2023-06-05 04:04:05 Ex2 2023-06-05 06:06:05 Ex6 2023-06-05 07:07:05 Ex3 2023-06-05 07:07:25 Ex1 2023-06-05 07:07:35 Ex5 2023-06-05 07:07:45 Ex6 2023-06-05 08:08:55 Ex6 2023-06-05 09:09:05 Ex6 2023-06-05 10:10:05 Ex6

Is this something that your original data look like?  If not, can you illustrate in a way that volunteers can understand?

Here is the code to generate the above set.  You can play with it and compare with your real data:

``````| makeresults
| eval _raw = "time,exception
2023-06-05 00:00:05,Ex6
2023-06-05 02:02:05,Ex6
2023-06-05 03:03:05,Ex1
2023-06-05 03:03:15,Ex2
2023-06-05 03:03:25,Ex4
2023-06-05 03:03:45,Ex3
2023-06-05 04:04:05,Ex2
2023-06-05 06:06:05,Ex6
2023-06-05 07:07:05,Ex3
2023-06-05 07:07:25,Ex1
2023-06-05 07:07:35,Ex5
2023-06-05 07:07:45,Ex6
2023-06-05 08:08:55,Ex6
2023-06-05 09:09:05,Ex6
2023-06-05 10:10:05,Ex6"
| eval _time = strptime(time, "%F %H:%M:%S")
| fields - _raw linecount time
``` data emulation above `````````

With this data set and the first code with the assumption of 3 to 4am inclusive, 7 to 8am inclusive (i.e., two-hour intervals),

``````| eval hour = strftime(_time, "%H")
| eval interval = case(hour > 2 AND hour < 5, "3_4", hour > 6 AND hour < 9, "7_8")
| stats values(exception) as exception by interval
| stats values(interval) as interval by exception
| where mvcount(interval) == 1 AND interval == "7_8"
| stats values(exception) as new_exception_7_8``````

gives exactly

 new_exception_7_8 Ex5Ex6

How is this "not working?"

To use 3 to 4am | 7 to 8am exclusive (i.e., one-hour intervals) as assumption, and my second code,

``````| eval hour = strftime(_time, "%H")
| where hour IN ("03", "07")
| stats values(exception) as exception by hour
| stats values(hour) as hour by exception
| where mvcount(hour) == 1 AND hour == "07"
| stats values(exception) as new_exception_7``````

(the above is slightly modified to pad hour with a leading 0), I also get

 new_exception_7 Ex5Ex6

which also meets your requirement.  Can you explain what "isn't working" here?

Get Updates on the Splunk Community!

#### Index This | A sphere has three, a circle has two, and a point has zero. What is it?

September 2023 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

#### Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

#### Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...