Hi,
In my splunk events, I have multiple jobsNames and their corresponding statusText. For one jobName, there will be multiple events with different statusText.
I need to identify all jobNames where their latest/current status is 'Running' . i.e For the latest entry for a specific job, the status should be Running
i tried the below, but the stats by statusText shows all the status for a specific job. As such it does
index=batch firm* | stats latest(timestamp) as Time by jobName, statusText | where statusText=Running
An example of some events for one specific job can be as follows. The below job example should not appear in my results as the latest status is 'SUCCESS'
FYI - The Splunk _time for the 3 events are exactly the same. The differentiation comes in the timestamp field. As such i cannot use latest(statusText)
timestamp="2020-08-20 03:18:35.0", eventNum="575452832", jobId="887395", jobName="firm_fisp1_ov_D8117", boxJobName="firm_fisp1_postov_box_1", eventCode="101", eventText="CHANGE_STATUS", statusCode="4", statusText="SUCCESS", alarmCode="0", exitCode="0", machine="frmlxap1p1.prudential.com", runNumber="90859630", attemptNumber="1"
timestamp="2020-08-20 03:18:28.0", eventNum="575452821", jobId="887395", jobName="firm_fisp1_ov_D8117", boxJobName="firm_fisp1_postov_box_1", eventCode="101", eventText="CHANGE_STATUS", statusCode="1", statusText="RUNNING", alarmCode="0", text="Executing at WA_AGENT", exitCode="0", machine="frmlxap1p1.prudential.com", runNumber="90859630", attemptNumber="1"
timestamp="2020-08-20 03:18:28.0", eventNum="575452820", jobId="887395", jobName="firm_fisp1_ov_D8117", boxJobName="firm_fisp1_postov_box_1", eventCode="101", eventText="CHANGE_STATUS", statusCode="3", statusText="STARTING", alarmCode="0", exitCode="-21", machine="frmlxap1p1.prudential.com", runNumber="90859630", attemptNumber="1"
Any help will be appreciated!
Thanks all - I was able to resolve the issue by adding a 'sort -timestamp' before using latest(statusText)
Hi @worldexplorer81 , I believe this should help
index=batch firm* | stats latest(statusText) as currentStatus by jobName |search currentStatus=RUNNING
Please upvote my response, if it helps.
Hi @Nisha18789 ,
unfortunately, that does not work. I forgot to mention that the splunk _time can be exactly the same for 3 events with different status. As such, using latest(statusText) returns a status of STARTING instead of SUCCESS. So i unfortunately cannot use that
If I understand correctly, you want timestamp from your log entries to be used instead of the initial value of _time. So, for these entries, for example:
timestamp | jobId | statusText |
2020-08-20 03:18:35.0 | 887395 | SUCCESS |
2020-08-20 03:18:28.0 | 887395 | RUNNING |
2020-08-20 03:18:28.0 | 887395 | STARTING |
2020-08-20 03:19:35.0 | 1111 | RUNNING |
2020-08-20 03:20:28.0 | 1111 | STARTING |
2020-08-20 04:10:00.0 | 2222 | RUNNING |
2020-08-20 04:09:00.0 | 2222 | STARTING |
Reset _time to be your timestamp:
| makeresults
| eval events="2020-08-20 03:18:35.0+887395+SUCCESS|2020-08-20 03:18:28.0+887395+RUNNING|2020-08-20 03:18:28.0+887395+STARTING|2020-08-20 03:19:35.0+1111+RUNNING|2020-08-20 03:20:28.0+1111+STARTING|2020-08-20 04:10:00.0+2222+RUNNING|2020-08-20 04:09:00.0+2222+STARTING"
| makemv delim="|" events
| mvexpand events
| rex field=events "(?<timestamp>.*)\+(?<jobId>.*)\+(?<statusText>.*)"
| eval _time=strptime(timestamp, "%Y-%m-%d %H:%M:%S.0")
| stats latest(statusText) as statusText by jobId
By the way, this can be done when your logs are first loaded into your indexes
Using latest (which is based on _time) gives:
jobId | statusText |
1111 | STARTING |
2222 | RUNNING |
887395 | SUCCESS |
Thanks all - I was able to resolve the issue by adding a 'sort -timestamp' before using latest(statusText)