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

[Kk]

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

Thanks in advance 

[ITWhisperer]

| eval 7to8am=mvmap('7to8am',if(isnull(mvfind('7to8am',mvjoin('3to4am',"|"))),'7to8am',null()))

[Kk]

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

[ITWhisperer]

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"
| multikv forceheader=1
| 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()))

[Kk]

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 

[ITWhisperer]

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

[Kk]

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. 

[ITWhisperer]

| 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
| transpose 0 header_field=hour column_name=Exception
| fields - Exception
| stats values(3to4am) as 3to4am values(7to8am) as 7to8am
| eval 7to8am=mvmap('7to8am',if(isnull(mvfind('7to8am',mvjoin('3to4am',"|"))),'7to8am',null()))

[Kk]

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?

[ITWhisperer]

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.

[yuanliu]

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

[Kk]

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.

[yuanliu · Accepted solution]

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

[Kk]

Hello, The first query that you answered isn't working. Can you please on your side?

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.

[yuanliu]

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"
| multikv forceheader=1
| 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

Ex5 Ex6

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

Ex5 Ex6

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