Archive
Highlighted

Highlight with color if SLA missed

New Member

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/CompletionTimeHour > SLA time is there a way to change the cell color.
2 - How to calculate time Difference ( SLA - CompletionTimeHour )

eval diff = SLA-CompletionTimeHour is now working

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

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

Tags (1)
0 Karma
Highlighted

Re: Highlight with color if SLA missed

Legend

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 CompletionTimeHour should be expressed as epoch time.

stats max(_time) AS Completion_Time by JOB yields the CompletionTime as epochtime. 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.

0 Karma
Highlighted

Re: Highlight with color if SLA missed

New Member

SLA column is in PRODBOXCO

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

0 Karma
Highlighted

Re: Highlight with color if SLA missed

New Member

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 SLAtimeminutes = 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 CompletionTime by JOB
| eval COMPLETION-DATE = strftime(CompletionTime, "%+")
| eval CTH = strftime(Completion
Time, "%H:%M")
| eval CTM = tonumber (strftime(CompletionTime,"%H"))*60 + tonumber (strftime(CompletionTime,"%M")) ]
| eval ACTUAL-SLA-DIFF = SLAtimeminutes - 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
BATA,BAT1,PROD.BAT1.XYZ,10:30
BAT
A,BAT2,PROD.BAT2.XYZ,09:30
BAT_B,BAT1,PROD.BAT1.XYZ,07:30

0 Karma
Highlighted

Re: Highlight with color if SLA missed

Legend

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
0 Karma
Highlighted

Re: Highlight with color if SLA missed

Legend

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

View solution in original post

0 Karma
Highlighted

Re: Highlight with color if SLA missed

New Member

thanks for the help iguinn.

Just change a bit and it works fine

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

0 Karma