Splunk Search

Compare changes today to last 7 days

kig121
Loves-to-Learn Lots

Hi All,

I am a newbie in Splunk world and looking for some help in structuring my query.

I have an index with data like this -

 

 

 

 

 

index="something" sourcetype="Datas" State="Agreed"

_tim	           ID	State
13/05/2021	01	Agreed
13/05/2021	02	Draft
13/05/2021	03	Agreed		
13/05/2021	04	Agreed		
13/05/2021	05	Agreed

12/05/2021	01	Agreed
12/05/2021	02	Draft
12/05/2021	03	Agreed		
12/05/2021	04	Agreed
12/05/2021	05	Agreed		

11/05/2021	01	Agreed
11/05/2021	02	Draft
11/05/2021	03	Agreed		
11/05/2021	04	Agreed		
11/05/2021	05	Draft

10/05/2021	01	Agreed
10/05/2021	02	Agreed		
10/05/2021	03	Agreed		
10/05/2021	04	Agreed		
10/05/2021	05	Darft

09/05/2021	01	Agreed
09/05/2021	02	Agreed		
09/05/2021	03	Agreed		
09/05/2021	04	Agreed		
09/05/2021	05	Draft

 

 

 

 


I am looking to build a query that will show me all the fields that have changed from last 7 days and today (13/05/2021) based on the ID. The output will be like this

 

 

 

 

 

_tim            ID		
13/05/2021	05	
10/05/2021	02

 

 

 

 

 

I can able to compare todays to tomorrow, but if i need to monitor what was the changes the last  7 days

index="something" sourcetype="Datas" State="Agreed" earliest=-0d@d latest=now | append [search index="something" sourcetype="Datas" State="Agreed" earliest=-1d@d latest=-0d@d ] | eventstats count by DoorsUUID | where count="1"

Labels (1)
0 Karma

kig121
Loves-to-Learn Lots

Thanks for reply @gcusello  and @bowesmana 

I show the original problem:
I have an indexed database which executed every day once a time. This Database contains Requirements where state is Draft or Agreed.

Today: 2.684 Agreed requirement.

Aim/tasks: Show those requirements which changed the last 5 or 7 days. (need exact requieremnt url, name, etc..)

Possible example: Today 2.684 yesterday 2.684  : Number was not changed, but it is possible that 3 req. set from Agreed to Draft but other 3 requirement set Draft to Agreed.
In this case i need to list 6 requirements.

kig121_0-1620974773252.png

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Have you tried either of the suggested solutions, if so, what was the outcome.

The lines from the 'sort ID _time' in my solution are the important ones to apply to your problem.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @kig121,

as @bowesmana said, did you tryed the hinted soutions?

what are the differences from the wanted results for each one?

because (I speak for my solution but for the other it's the same thing) I think that my solution fits your requirement and gives you the information about the status changes in your data.

Ciao.

Giuseppe

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @kig121,

please try this:

your_search
| stats dc(status) AS dc_status values(status) AS status values(_time) AS _time BY ID
| where dc_status>1

Ciao.

Giuseppe

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Have a look at this example using streamstats

I am not sure that your example expected output dates are what you intended for ID 2 and 5, as they don't reflect the actual dates of the change in your example data, so I am assuming this is what you intended.

| makeresults
| eval _raw="time	ID	State
13/05/2021	01	Agreed
13/05/2021	02	Draft
13/05/2021	03	Agreed		
13/05/2021	04	Agreed		
13/05/2021	05	Agreed
12/05/2021	01	Agreed
12/05/2021	02	Draft
12/05/2021	03	Agreed		
12/05/2021	04	Agreed
12/05/2021	05	Agreed		
11/05/2021	01	Agreed
11/05/2021	02	Draft
11/05/2021	03	Agreed		
11/05/2021	04	Agreed		
11/05/2021	05	Draft
10/05/2021	01	Agreed
10/05/2021	02	Agreed		
10/05/2021	03	Agreed		
10/05/2021	04	Agreed		
10/05/2021	05	Draft
09/05/2021	01	Agreed
09/05/2021	02	Agreed		
09/05/2021	03	Agreed		
09/05/2021	04	Agreed		
09/05/2021	05	Draft"
| multikv forceheader=1
| eval _time=strptime(time, "%d/%m/%Y")
| table _time ID State
| sort ID _time
| streamstats window=1 current=f latest(State) as PreviousState by ID
| where State!=PreviousState
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...