Splunk Search

## Highlight with color if SLA missed

Path Finder

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

Tags (1)
1 Solution
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
``````
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
``````
Path Finder

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

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 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.

Path Finder

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
...
...
....

Path Finder

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

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
``````
Get Updates on the Splunk Community!

#### Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...

#### IM Landing Page Filter - Now Available

We’ve added the capability for you to filter across the summary details on the main Infrastructure Monitoring ...

#### Dynamic Links from Alerts to IM Navigators - New in Observability Cloud

Splunk continues to improve the troubleshooting experience in Observability Cloud with this latest enhancement ...