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

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

sekhar463 — Thu, 19 Oct 2023 12:46:27 GMT

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

Re: SPLUNK SEARCH TO GET TIME IF EXCEED IF DAY CHANGED

yuanliu — Fri, 16 Jun 2023 17:29:23 GMT

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

Re: SPLUNK SEARCH TO GET TIME IF EXCEED IF DAY CHANGED

sekhar463 — Mon, 19 Jun 2023 09:03:04 GMT

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

Re: SPLUNK SEARCH TO GET TIME IF EXCEED IF DAY CHANGED

ITWhisperer — Mon, 19 Jun 2023 09:31:23 GMT

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

Re: SPLUNK SEARCH TO GET TIME IF EXCEED IF DAY CHANGED

yuanliu — Tue, 20 Jun 2023 06:38:34 GMT

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