How to search new values for a field (exception) in the last 7 days which were not present the 7 days prior?

[joydeep741]

index=dotcom source=*system* *exception* earliest = -7d NOT [search index=dotcom source=*system* *exception* earliest = -20d | return 10000 exception] | dedup exception | table exception

I am trying the above search, but it is not giving me the right results.

For example, consider this scenario:
From April 1st to April 15th i got the following values of field exception:
- A
- B
- C
- D

Now From April 16th to April 23 i got the following values :-
- A
- B
- X
- Y

In my search result i want values of field exception which were present in the last 7 days (i.e 16th to 23rd) but not prior to that. So the result should give me
- X
- Y

[joydeep741 · Accepted solution]

index=dotcom source=system exception earliest = -30d latest=now |
stats earliest(_time) as FirstOccurence by exception |
fieldformat FirstOccurence = strftime(FirstOccurence,"%d/%m/%y %H:%M:%S") |
eval SevenDaysBack = relative_time(now(), "-7d@d" ) |
fieldformat SevenDaysBack = strftime(SevenDaysBack ,"%d/%m/%y %H:%M:%S")|
Table FirstOccurence exception SevenDaysBack | where FirstOccurence > SevenDaysBack

Logic :-
First i found the First occurence of each Exception In the last one month. Example
- NullPointerException On 01/04/2015
- ObjectNotFoundException On 11/4/2015
- NoSuchElementException On 22/4/2015
- StaplesIGFormattingException On 23/4/2015

Now, out of these results i select only those whose First Occernce was within last one week.
So i get :-
- NoSuchElementException On 22/4/2015
- StaplesIGFormattingException On 23/4/2015

It is giving me right results.

[fdi01]

try this :

 index=dotcom source=system exception earliest = -23d  latest=now  NOT [search index=dotcom source=system exception earliest = -23d  latest= -7d |eval  exception2=exception   ] |eval  exception=if(exception != exception2, exception , " ")| dedup exception | table exception

or

index=dotcom source=system exception earliest = -23d  latest=now   [search index=dotcom source=system exception earliest = -23d  latest= -7d |eval  exception2=exception   ] |eval  exception=if(exception != exception2, exception , " ")| dedup exception | table exception 

[fdi01]

try like this:

index=dotcom source=system exception earliest = -23d  latest=now  NOT [search index=dotcom source=system exception earliest = -23d  latest= -7d ] | dedup exception | table exception

[joydeep741]

Hey,

The problem is i am still getting all the results
- A
- B
- X
- Y

May be the issue is :
Exception with value A on 1st april(Timestamp) is considered different from Exception with value A on 22nd April.
So the "NOT" is not considering them as same events. and thus I am still getting the older values.
Hope i am clear.

[fdi01]

try this: index=dotcom source=system exception earliest = -23d latest=now NOT [search index=dotcom source=system exception earliest = -23d latest= -7d ] | dedup exception | table exception

[stephane_cyrill]

Hi try this,

index=dotcom source=system exception earliest = -7d NOT [search index=dotcom source=system exception earliest=0 latest=-7d| return 10000 exception] | dedup exception | table exception

[ngatchasandra]

Hi,

try with this query:

    index=dotcom source=system exception earliest = -7d | dedup exception | table exception

when you run it, splunk filter automaticaly only the events that occured in last 7 days. Thus the values of exception field cocern only the last 7 days.

[joydeep741]

Consider this scenario :
From April 1st to April 15th i got the following values of field Exception :
- A
- B
- C
- D

Now From April 16th to April 23 i got the following values :-
- A
- B
- X
- Y

In my search result i want values of field Exception which were present in the last 7 days(i.e 16th to 23rd) but not prior to that . So the result should give me
- X
- Y

[ngatchasandra]

See query that follow:

index=dotcom source=system exception ((earliest=-7d) NOT (earliest=0  latest=-7d))| dedup exception | table exception

earliest=0 latest=-7d meaning the corresponding results in the range from the beginning until it 7 days left to reach the current time.

[joydeep741]

Hey,

The problem is i am still getting all the results
- A
- B
- X
- Y

May be the issue is :
Exception with value A on 1st april(Timestamp) is considered different from Exception with value A on 22nd April.
So the "NOT" is not considering them as same events. and thus I am still getting the older values.
Hope i am clear.