I am trying in splunk to monitor the progress of certain id’s which come from two different sources but in the same index.
From source one there is a DB-query which is executed once a day. This generates something like this:
ID Date_1 Date_2 Status
1 2-1-2017 23-9-2016 Y
2 23-3-2017 16-1-2017 x
3 16-6-2017 4-3-2017 y
4 12-12-2016 01-10-2017 y
The next day it may generated this:
ID Date_1 Date_2 Status
1 1-1-2017 23-9-2016 X
2 23-3-2017 16-1-2017 x
3 16-6-2017 4-3-2017 y
In total in the index I have this after two runs:
ID Date_1 Date_2 Status
1 1-1-2017 23-9-2016 X
1 2-1-2017 23-9-2016 Y
2 23-3-2017 16-1-2017 x
2 23-3-2017 16-1-2017 x
3 16-6-2017 4-3-2017 y
4 12-12-2016 01-10-2017 y
As you can see has Id 2 no changes but is still inserted twice of course and id 4 has disappeared in the next day because it has moved to another process.
ID 4 is now in the next process en will show in the other query from the second source. The output for this will be something like this:
id Date_3 code
4 10-10-2017 A4
I want to show the transit times (?) of each unique ID. Over time counting from today (NOW())
Hi Mike6960,
if I have correctly understood, try something like this:
your_search
| stats values(Date_1) AS Date_1 values(Date_2) AS Date_2 values(code) AS code) count by id
| where count =1
| eval Date_3=strftime(strptime(Date_2,"%d-%m-%Y")-strptime(Date_1,"%d-%m-%Y"),"%d-%m-%Y")
| table id Date_3 code
Bye.
Giuseppe
Could you just dedup them at the end?
Hi Mike6960,
if I have correctly understood, try something like this:
your_search
| stats values(Date_1) AS Date_1 values(Date_2) AS Date_2 values(code) AS code) count by id
| where count =1
| eval Date_3=strftime(strptime(Date_2,"%d-%m-%Y")-strptime(Date_1,"%d-%m-%Y"),"%d-%m-%Y")
| table id Date_3 code
Bye.
Giuseppe
Hai Giuseppe, thanks, but Date_3 is also a value and not calculated from date_1 and date_2
In this case add this additional fields to your stats using values function
your_search
| stats values(Date_1) AS Date_1 values(Date_2) AS Date_2 values(Date_3) AS Date_3 values(code) AS code count by id
| where count =1
| table id Date_3 code
Bye.
Giuseppe
Great Thanks now I only have to figure the time (in days ) between each date
It's easy
| eval Diff=(strptime(Date_2,"%d-%m-%Y")-strptime(Date_1,"%d-%m-%Y"))/86400
or if you prefer more detailed
| eval Diff=tostring(strptime(Date_2,"%d-%m-%Y")-strptime(Date_1,"%d-%m-%Y")),"duration")
Bye.
Giuseppe
P.S.: if you're satisfied by this answer please remember to accept or upvote it.