Highlight with color if SLA missed

[harsush]

Hi Team,

Below my search from which i am getting the completion time of job. I need ur help for couple.

1 - If Below search won't get completion time/Completion_Time_Hour > SLA time is there a way to change the cell color.
2 - How to calculate time Difference ( SLA - Completion_Time_Hour )

eval diff = SLA-Completion_Time_Hour is now working

Supported fields :
CYCLE,CheckPoint,Job,SLA
BAT_A,BAT1,PROD.BAT1.XYZ,10:30
BAT_A,BAT2,PROD.BAT2.XYZ,09:30
BAT_B,BAT1,PROD.BAT1.XYZ,07:30
...
...
....

| inputlookup PROD_BOX_CO
| search Job=PROD*
| rename Job AS JOB
| join type=left JOB [
search index=ca* sourcetype=uc4 host=uc* U0011502
| stats max(_time) AS Completion_Time by JOB
| eval Completion_Time_A = strftime(Completion_Time, "%+")
| eval Completion_Time_Hour = strftime(Completion_Time, "%H:%M")
] | eval diff = SLA-Completion_Time_Hour | table CYCLE CheckPoint SLA Completion_Time_A Completion_Time_Hour diff | sort Completion_Time

[lguinn2 · Accepted solution]

Yikes - given the format that you shared in a comment, here is what I would do.
First, since you have only HH:MM in the csv input, I would calculate the number of minutes since midnight instead of the actual epoch time. My search would be

| inputlookup PROD_BOX_CO
| search Job=PROD*
| rename Job AS JOB
| rex field=SLA "(?<hrs>\d+?)\:(?<min>\d+)"
| eval SLA_time_minutes = hrs * 60 + min
| join type=left JOB [ search index=ca* sourcetype=uc4 host=uc* U0011502
        | stats max(_time) AS Completion_Time by JOB
        | eval Completion_Time_A = strftime(Completion_Time, "%+")
        | eval Completion_Time_Hour = strftime(Completion_Time, "%H:%M")
        | eval Completion_time_minutes = strftime(Completion_Time,"%H")*60 + strftime(Completion_Time,"%M") ]
| eval diff = SLA_time_minutes - Completion_time_minutes
| eval diff_formatted = tostring(diff*60,"duration")
| table CYCLE CheckPoint SLA Completion_Time_A Completion_Time_Hour diff diff_formatted
| sort Completion_Time

[harsush]

thanks for the help iguinn.

Just change a bit and it works fine

| stats max(_time) AS Completion_Time by JOB
| eval CTD = strftime(Completion_Time, "%+")
| eval CTH = strftime(Completion_Time, "%H:%M")
| eval CTM = tonumber (strftime(Completion_Time,"%H"))*60 + tonumber (strftime(Completion_Time,"%M")) ]
| eval ACTUAL(SLAM-CTM) = SLA_time_minutes - CTM
| table CYCLE JOB SLA CTD ACTUAL(SLAM-CTM)
| sort Completion_Time

[lguinn2]

You can't calculate the time difference between two strings - which is what you get from strftime.

In order to calculate eval diff = SLA-Completion_Time_Hour, both SLA and Completion_Time_Hour should be expressed as epoch time.

stats max(_time) AS Completion_Time by JOB yields the Completion_Time as epoch_time. I don't know the format of SLA.

In Splunk 6.6, there are several ways to format a column in the results by clicking on the small paintbrush at the top of the column.

[harsush]

SLA column is in PROD_BOX_CO

CYCLE,CheckPoint,Job,SLA
BAT_A,BAT1,PROD.BAT1.XYZ,10:30
BAT_A,BAT2,PROD.BAT2.XYZ,09:30
BAT_B,BAT1,PROD.BAT1.XYZ,07:30
...
...
....

[harsush]

Thanks for your help Iguinn...

is there a way to covert SLA time to epoch_time

Suppose in my file SLA format is 10:30 convert it into today's date time. Is this possible ?

abv will help me to calculate accurate SLA

| inputlookup milestonejobs
| search CYCLE=ABCD
| rex field=SLA "(?\d+?):(?\d+)"
| eval SLA_time_minutes = tonumber(hrs) * 60 + min
| join type=left JOB [ search index=ca* sourcetype=uc4 host=uc* U0011502 OR U0011006 OR U0012109 OR U0011066 OR U0011037 OR U0011038
| rex "U0011502 Workflow '(?[^']+)'"
| rex "U0011006 Job '(?[^']+)'"
| rex "U0012109 Script '(?[^']+)'"
| rex "U0011066 Notification '(?[^']+)'"
| rex "U0011037 Event '(?[^']+)'"
| rex "U0011038 Event '(?[^']+)'"
| stats max(_time) AS Completion_Time by JOB
| eval COMPLETION-DATE = strftime(Completion_Time, "%+")
| eval CTH = strftime(Completion_Time, "%H:%M")
| eval CTM = tonumber (strftime(Completion_Time,"%H"))*60 + tonumber (strftime(Completion_Time,"%M")) ]
| eval ACTUAL-SLA-DIFF = SLA_time_minutes - CTM
| fillnull value=YET-TO-RUN COMPLETION-DATE ACTUAL-SLA-DIFF
| table CYCLE MILE-STONE JOB SLA COMPLETION-DATE ACTUAL-SLA-DIFF
| sort COMPLETION-DATE

Lookup file name : milestonejobs

CYCLE,CheckPoint,Job,SLA
BAT_A,BAT1,PROD.BAT1.XYZ,10:30
BAT_A,BAT2,PROD.BAT2.XYZ,09:30
BAT_B,BAT1,PROD.BAT1.XYZ,07:30

[lguinn2]

If you want to convert the SLA time to epoch time, then you will need to assume that the SLA date is "today". This can be calculated as:

 | rex field=SLA "(?<hrs>\d+?)\:(?<min>\d+)"
 | eval SLA_time_seconds = (hrs * 60 + min) * 60
 | eval midnight_today = relative_time(now(),"@d")
 | eval SLA_epoch_time = midnight_today + SLA_time_seconds