How do I find the date/time that a field last changed?

[jamesgeldart]

I'm using Splunk to provide dashboards on a document workflow for a publishing company. The data from the workflow system creates a new event each time something changes on a document.

A key metric is to understand how long a document has been at the current status, so to do this we need to pull out the last date the status changed. We have data like the following (not all fields shown):

1689,"Dec 14 initiation.docx","Sent to management",Financials,"QPEF",,2015-01-02,Initiation,"SG","AM",2015-01-15T11:46:42Z,Financials
1689,"Dec 14 initiation.docx","Sent to management",Financials,"QPEF",,2015-01-02,Initiation,"SG","AM",2015-01-14T10:29:52Z,Financials
1689,"Dec 14 initiation.docx","Management feedback/revisions",Financials,"QPEF",,2015-01-02,Initiation,"SG","AM",2015-01-13T17:31:13Z,Financials
1689,"Dec 14 initiation.docx",Analyst,Financials,"QPEF",,2015-01-02,Initiation,"SG","AM",2015-01-02T17:23:25Z,Financials
1689,"Dec 14 initiation.docx",Analyst,Financials,"QPEF",,2015-01-02,Initiation,"SG","AM",2014-12-31T14:36:55Z,Financials
1689,"Dec 14 initiation.docx",Analyst,Financials,"QPEF",,2015-01-02,Initiation,"SG","AM",2014-12-30T17:21:29Z,Financials
1689,"Dec 14 initiation.docx",Planned,Financials,"QPEF",,2015-01-02,Initiation,"SG","AM",2014-12-19T17:19:49Z,Financials

This is extracted from the CSV into an index where the first column contains the Asset_ID, and the third column is the status.

The current search uses | dedup Asset_ID sortby -_time to get the latest values for each document, but what I need is the timestamp from the last time the status was changed, which in the above case would be from the second line down, i.e. 2015-01-14T10:29:52Z

Have had a search around Splunk answers including http://answers.splunk.com/answers/33266/how-to-find-the-latest-event-message-with-a-given-key-field.... which is a slightly different requirement, can't find any direct solution though. Any help much appreciated. Sure I can't be the only one trying to do something similar.

[chanfoli · Accepted solution]

I think streamstats is probably the way to go with this but there are almost always several ways to get the same result. I suggest taking a look at streamstats, specifically example 3 on its page for your requirement:

http://docs.splunk.com/Documentation/Splunk/6.2.1/SearchReference/streamstats

[jamesgeldart]

Awesome thanks 🙂
Example 3 on that page was essentially what I needed. The solution was:
index="quark_data" | streamstats current=false last(Status) as new_status last(Last_modified) as time_of_change by Asset_ID | where new_status != Status | dedup Asset_ID sortby -_time | table Asset_ID, new_status, time_of_change, Status