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