How To Compare Dates Between Two Different Sourcetypes

[ebruozys]

I want to calculate how long it takes until a event from one sourcetype switches to another sourcetype. For example event with ID 12345 enters sourcetype "start" on 2017-10-17 on 2017-10-30 it disapears from sourcetype 'start' and enters sourcetype "second_stage". I want to know how many days it took for to go to the second stage.

So far I have tried the following command
(index="test" source="start") OR (index="test" source="second_stage") ID=12345 | eval Start_Date=strptime(STARTDATE, "%Y-%m-%d %H:%M:%S."%N") | eval SecondStage_Date=strptime(SECONDSTAGEDATE,"%Y-%m-%d %H:%M:%S.%N") | eval Duration=floor((SecondStage_Date-Start_Date)/86400)

On execution of the search the field Duration stays empty. I asume that somehow I need to combine the Start_Date and SecondStage_Date to the ID, but I have no idea how to do that.

Does anybody else know how to do that?

[niketn]

@ebruozys, can you add the background of what is the process for your use case? Why and how do you get sourcetype of an event changed to a new one and purged from the original?

[gcusello · Accepted solution]

Hi ebruozys,
try something like this

(index="test" source="start") OR (index="test" source="second_stage") ID=12345
| stats earliest(STARTDATE) AS STARTDATE earliest(SECONDSTAGEDATE) AS SECONDSTAGEDATE
| eval 
     Start_Date=strptime(STARTDATE, "%Y-%m-%d %H:%M:%S."%N"),
     SecondStage_Date=strptime(SECONDSTAGEDATE,"%Y-%m-%d %H:%M:%S.%N"),
     Duration=floor((SecondStage_Date-Start_Date)/86400)
| table STARTDATE SECONDSTAGEDATE Duration

I prefer to use tostring funtion to show durations, in other words replace last eval with Duration=tostring(SecondStage_Date-Start_Date,"duration")
Bye.
Giuseppe

[ebruozys]

Hi Giuseppe,

Thank you for your response.

I realise my original question wasn't complete.

What if I want to get a list of multiple ID's?
Sow imagine I dont have a specific ID, I just want to get a list of ID's and the Duration per ID of how long it took for it to reach the second stage. I tried adding a 'chart count by duration', but all I seem to get is a count of total duration.

Greetings,
Evald

[gcusello]

Hi ebruozys,
if you have multiple IDs insert this clause in stats command

(index="test" source="start") OR (index="test" source="second_stage")
 | stats earliest(STARTDATE) AS STARTDATE earliest(SECONDSTAGEDATE) AS SECONDSTAGEDATE BY ID
 | eval 
      Start_Date=strptime(STARTDATE, "%Y-%m-%d %H:%M:%S."%N"),
      SecondStage_Date=strptime(SECONDSTAGEDATE,"%Y-%m-%d %H:%M:%S.%N"),
      Duration=floor((SecondStage_Date-Start_Date)/86400)
 | table ID STARTDATE SECONDSTAGEDATE Duration

Bye.
Giuseppe

[harsmarvania57]

Hi,

If you want to calculate difference by ID then you can try below query

(index="test" source="start") OR (index="test" source="second_stage") ID=12345 | eval Start_Date=strptime(STARTDATE, "%Y-%m-%d %H:%M:%S."%N") | eval SecondStage_Date=strptime(SECONDSTAGEDATE,"%Y-%m-%d %H:%M:%S.%N") | stats values(eval(floor((SecondStage_Date-Start_Date)/86400))) AS Duration by ID

Can you please let us know how your timestamp looks like in STARTDATE and SECONDSTAGEDATE field?

EDIT: Updated query