How to compare same date field after status changes?

[ebruozys]

I'm trying to compare the same date field between two different events.

An event has the following fields that are important for the comparison.

DATE
STATE
ID

For example: ID 12345 receives on 5-2-2018 the STATE 'New'
The same ID receives on 13-2-2018 the STATE 'Executed'

Sow what I'm looking for is a statement that compares the different STATE for each unique ID.

[ebruozys · Accepted solution]

I ended up using Transaction command

<base search>| transaction ID startswith=eval(STATE="New") endswith=eval(STATE="Executed") | table ID STATE duration

[isabel_ycourbe]

Elegant solution.

[isabel_ycourbe]

Usually when I try to compare dates I tend to convert them to a number, do the math, and reformat them in dates. You can use strptime (http://docs.splunk.com/Documentation/Splunk/7.0.2/SearchReference/DateandTimeFunctions) that will take your date as input and return you an epoch that you can easily compare since it's a number. Combine that with streamstats or eventstats to get the result you want.

[ebruozys]

I see my initial question wasn't complete.

I'm trying to create a list that shows how long it took for each unique ID to get from STATE 'New' to STATE 'Executed'

I know how to compare two different date fields, but I have never compaired the same date field that has a different STATE.

[493669]

have you tried stats command :

<base search>|stats values(STATE) by DATE ID

it will return all values of STATE by ID and DATE