topic Re: Stats function with additional fields in Splunk Search

Stats function with additional fields

worldexplorer81 — Fri, 21 Aug 2020 00:46:52 GMT

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!

Re: Stats function with additional fields

Nisha18789 — Fri, 21 Aug 2020 00:31:22 GMT

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.

Re: Stats function with additional fields

worldexplorer81 — Fri, 21 Aug 2020 00:45:10 GMT

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

Re: Stats function with additional fields

ITWhisperer — Sun, 23 Aug 2020 13:05:55 GMT

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:

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:

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

Re: Stats function with additional fields

worldexplorer81 — Mon, 24 Aug 2020 15:21:22 GMT

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