Getting Data In

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

Mike6960
Path Finder

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

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

troyward
Explorer

Could you just dedup them at the end?

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

Mike6960
Path Finder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

Mike6960
Path Finder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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.

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