Keep most recent _time of multiple fields with non-NULL values

[ErikaE]

I have user-generated data that I am trying to splunk to show whether or not an audit or check has been performed in a certain time range. Example rows look like this ...

_time    Field A               Field B              Field C
#1       Complete                                   Complete
#2                                                  Complete     
#3       Complete              Complete       

Current state: I have a search that creates a table showing complete and incomplete checks:

earliest=-24h sourcetype=_json source="AuditData" index=auditindex | stats values(*) as * | appendpipe [ stats count | where count==0] |`AuditFillNull`   | transpose | rename column as "Audit" | rename "row 1" as Status

Audit       Status 
Field A    Complete 
Field B    Incomplete 
Field C    Complete  

The above shows the table the search produces if the search time frame covered the #1 event but not #2 or #3. The fill null macro has an eval + coalesce expression for each field that fills in Incomplete in the place of null values.

What I would like to show is a table like this:

Audit       Last Done      Status 
Field A     #1             Complete  
Field B     #3             Incomplete  
Field C     #1             Incomplete 

Where the "Last Done" shows the time of the last complete value in the data set. I'm sure there must be a way to accomplish this task but I'm not sure what commands to look at.

[somesoni2 · Accepted solution]

Give this a try

 earliest=-24h sourcetype=_json source="AuditData" index=auditindex  | table _time FieldA FieldB FieldC | untable _time Audit Value | fillnull value="Incomplete" Value | eval LastDone=if(Value="Complete",_time,null()) | stats latest(LastDone) as "Last Done" latest(Value) as Status by Audit

[ErikaE]

sourcetype=_json source="AuditData" index=auditindex | `AuditFillNull` | table _time * | untable _time Audit Status | eval LastDone=if(Status="Complete",_time,null()) | stats latest(LastDone) as "Last Done" latest(Status) as Status by Audit | eval Last_Done=strftime('Last Done', "%a %H:%M") | table Last_Done Audit Status

That worked great! I had to move the fill null, perhaps because of the way I'm extracting fields the fields simply don't show up if they have a null value.

[woodcock]

In your last (desired output) table, why does Status for Field C have a value of Incomplete?

It seems to me that Incomplete is determined by a lack of a Complete value for the latest time ( #1 ), right?

[ErikaE]

In the example :

The time range of interest (say, last 24 hours) for the set of audits or checks A, B, C returns example event #1 and #2 but not #3. In the old search, that would show A and C as complete and B as incomplete.

What that doesn't tell the end user is how urgent it is to go and complete Audit B. If it's only been 25 hours that conveys a different level of urgency than if it's been 50. The example only has 3 audits but in reality there are up to 25 that a single user is responsible for. The 'old search' compresses many records into a status for the time period at cost of losing the time information. Does that help?