Splunk Search

Stats function with additional fields

worldexplorer81
Path Finder

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! 

Labels (1)
0 Karma
1 Solution

worldexplorer81
Path Finder

Thanks all - I was able to resolve the issue by adding a 'sort -timestamp' before using latest(statusText)

View solution in original post

0 Karma

Nisha18789
Builder

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.

 

0 Karma

worldexplorer81
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Hi @worldexplorer81 

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:

timestampjobIdstatusText
2020-08-20 03:18:35.0887395SUCCESS
2020-08-20 03:18:28.0887395RUNNING
2020-08-20 03:18:28.0887395STARTING
2020-08-20 03:19:35.01111RUNNING
2020-08-20 03:20:28.01111STARTING
2020-08-20 04:10:00.02222RUNNING
2020-08-20 04:09:00.02222STARTING

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:

jobIdstatusText
1111STARTING
2222RUNNING
887395SUCCESS
0 Karma

worldexplorer81
Path Finder

Thanks all - I was able to resolve the issue by adding a 'sort -timestamp' before using latest(statusText)

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...