Splunk Search

How to compare same date field after status changes?

ebruozys
Path Finder

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.

0 Karma
1 Solution

ebruozys
Path Finder

I ended up using Transaction command

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

View solution in original post

ebruozys
Path Finder

I ended up using Transaction command

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

isabel_ycourbe
Path Finder

Elegant solution.

0 Karma

isabel_ycourbe
Path Finder

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.

0 Karma

ebruozys
Path Finder

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.

0 Karma

493669
Super Champion

have you tried stats command :

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

it will return all values of STATE by ID and DATE

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>