topic How to compare same date field after status changes? in Splunk Search

How to compare same date field after status changes?

ebruozys — Mon, 12 Feb 2018 14:05:12 GMT

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.

Re: How to compare same date field after status changes?

493669 — Mon, 12 Feb 2018 14:12:07 GMT

have you tried stats command :

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

it will return all values of STATE by ID and DATE

Re: How to compare same date field after status changes?

ebruozys — Mon, 12 Feb 2018 14:23:18 GMT

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.

Re: How to compare same date field after status changes?

isabel_ycourbe — Mon, 12 Feb 2018 14:58:36 GMT

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.

Re: How to compare same date field after status changes?

ebruozys — Tue, 13 Feb 2018 13:31:51 GMT

I ended up using Transaction command

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

Re: How to compare same date field after status changes?

isabel_ycourbe — Tue, 13 Feb 2018 13:55:54 GMT

Elegant solution.