Splunk Search

Highlight with color if SLA missed

harsush
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)
0 Karma
1 Solution

lguinn2
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

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

harsush
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

0 Karma

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

0 Karma

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

0 Karma

harsush
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

0 Karma

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

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...