How can I keep track of IDs that come from different sources in the same index?

[Mike6960]

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())

[troyward]

Could you just dedup them at the end?

[gcusello · Accepted solution]

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

[Mike6960]

Hai Giuseppe, thanks, but Date_3 is also a value and not calculated from date_1 and date_2

[gcusello]

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

[Mike6960]

Great Thanks now I only have to figure the time (in days ) between each date

[gcusello]

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.