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.
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
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
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.
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?
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?