Splunk Search to get time if exceeds cut off time on account of day changed?

[sekhar463]

Hai All,

Good day,

we have event in splunk for job_name Test job HAS  START_TIME  at 2023/06/15 23:30:33 and END_TIME 2023/06/16 00:04:09 

AND we have static cut off time for each job which we have added in lookup data

FOR ABOVE job cutoff time is 23:40 but the job crossed cutoff time even day was changes

BELOW is the query i was using to get if any job exceed cutoff time on account of day changes it should consider the same day 

this query not giving expected ouput,please help on it

[yuanliu]

I get the feeling that in your use case, it is not worth thinking in calendar time.  Instead, just convert everything to epoch for calculation.

index=test sourcetype=test_source
| lookup Autosys_crd.csv JOB_NAME OUTPUT KB REGION CUTOFF_CST
| eval Last_Job_start = strptime(LAST_START, "%Y/%m/%d %H:%M:%S")
| eval Last_Job_end = strptime(LAST_END, "%Y/%m/%d %H:%M:%S")
| eval Job_start_date = strptime(replace(LAST_START, " [\d:]+", ""))
| eval Cutoff_Time = Job_start_date + strptime(CUTOFF_CST, "%H:%M")
| eval Exceeded_Cutoff = if(STATUS="ACTIVATED","",if(Last_Job_end > Cutoff_Time, 1, 0))
| stats count(eval(Exceeded_Cutoff == 1)) as Exceeded_Count

[sekhar463]

Not giving results one job exceeded cutoff time for below

we have event in splunk for job_name Test job HAS  START_TIME  at 2023/06/15 23:30:33 and
 END_TIME 2023/06/16 00:04:09 
cutoff time is 23:40

but when i was searching its not showing this,its not taking due to day change with END_TIME

how can i overcome this to check if any exceeded on the day even it was changed the day

[yuanliu]

Two problems with the previous one.  First, strptime cannot operate without a date string.  But the second is more important: Cutoff_Time was only populated in events that contain LAST_START, but it needs to be compared with LAST_END.  It has to be cross populated, by JOB_NAME.

So, if I assume that by START_TIME you mean LAST_START, by END_TIME you mean LAST_END, the following should give desired results

index=test sourcetype=test_source
| lookup Autosys_crd.csv JOB_NAME OUTPUT KB REGION CUTOFF_CST
| eval Last_Job_start = strptime(LAST_START, "%Y/%m/%d %H:%M:%S")
| eval Last_Job_end = strptime(LAST_END, "%Y/%m/%d %H:%M:%S")
| eval Job_start_date = replace(LAST_START, " [\d:]+", "")
| eval Cutoff_Time = strptime(Job_start_date . " " . CUTOFF_CST, "%Y/%m/%d %H:%M")
| stats max(Cutoff_Time) as Cutoff_Time max(Last_Job_*) as Last_Job_* by JOB_NAME
| eval Exceeded_Cutoff = if(STATUS="ACTIVATED","",if(Last_Job_end > Cutoff_Time, 1, 0))
| stats count(eval(Exceeded_Cutoff == 1)) as Exceeded_Count

[ITWhisperer]

Try something like this

index=test sourcetype=test_source
| lookup Autosys_crd.csv JOB_NAME OUTPUT KB REGION CUTOFF_CST
| eval Last_Job_start = strptime(LAST_START, "%Y/%m/%d %H:%M:%S")
| eval Last_Job_end = strptime(LAST_END, "%Y/%m/%d %H:%M:%S")
| eval Cutoff_Time = strptime(replace(LAST_START, " [\d:]+", "")." ".CUTOFF_CST, "%Y/%m/%d %H:%M")
| eval Exceeded_Cutoff = if(STATUS="ACTIVATED","",if(Last_Job_end > Cutoff_Time, 1, 0))
| stats count(eval(Exceeded_Cutoff == 1)) as Exceeded_Count