How to display the SLA status based on the SLA time

[rock_s]

Hi Experts,

I have data as shown below, Whenever we run the search, if the current time is greater than start time we need to show the status as "Not Started", similar end time less the current time then status as Completed, if the current time is in b/w start time and end time then status as Running

DATA:
job SLA_start_time SLA_end_time
abs 16:00 17:00
abc 20:00 23:00
mlp 23:00 01:00

Expected output: if the current time hour is 18:00
job SLA_start_time SLA_end_time Status
abs 16:00 17:00 completed
abc 20:00 23:00 Not Started
zxc 18:00 19:00 Running
mlp 23:00 01:00 Not Started

Note: here few of jobs are starting end of today and supposed to complete it next day early morning like 01:00 clock
please help me on this, thanks in advance.

[to4kawa]

<form>
  <label>SLA sample</label>
  <fieldset submitButton="false">
    <input type="dropdown" token="time">
      <label>time</label>
      <fieldForLabel>time</fieldForLabel>
      <fieldForValue>_time</fieldForValue>
      <search>
        <query>| makeresults count=2
| streamstats count
| eval _time=if(count=1,relative_time(_time,"-1d@d"),relative_time(_time,"@d"))
| makecontinuous _time span=1h
| eval _time=strftime(_time,"%H:%M")
| eval time=_time
| filldown
| where count=1</query>
      </search>
      <prefix>"</prefix>
      <suffix>"</suffix>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>| makeresults
| eval _raw="job,SLA_start_time,SLA_end_time
abs,16:00,17:00
abc,20:00,23:00
zxc,18:00,19:00
mlp,23:00,01:00"
| multikv forceheader=1
| table job,SLA_start_time,SLA_end_time
| eval Status=case(strptime($time$,"%H:%M")&lt;strptime(SLA_start_time,"%H:%M"),"Not Started"
, strptime($time$,"%H:%M")&gt;=strptime(SLA_start_time,"%H:%M") AND strptime($time$,"%H:%M")&lt;=strptime(SLA_end_time,"%H:%M"),"RUNNING"
, strptime($time$,"%H:%M")&gt;strptime(SLA_end_time,"%H:%M"),"completed")</query>
          <earliest>0</earliest>
          <latest></latest>
          <sampleRatio>1</sampleRatio>
        </search>
      </table>
    </panel>
  </row>
</form>

How about relative_time() ?

[rnowitzki]

Hi,

I made up some sample data and it worked using this:

| eval curr_date_hour = strftime(now(), "%H")

| eval status=if(curr_date_hour>substr(SLA_start_time,1,2) AND curr_date_hour>=substr(SLA_end_time,1,2), "COMPLETED", if(curr_date_hour<substr(SLA_start_time,1,2),"NOT_STARTED", if(curr_date_hour>=substr(SLA_start_time,1,2) AND curr_date_hour<substr(SLA_end_time,1,2),"RUNNING", "ERROR_IN_STATUS_DETECTION")))

Note: This is only looking at full hours, so you have issues like if start time is 18:30 and you check at 18:00, it would be considered as "RUNNING".

It will be better to get the current time as HH:MM and also do the checks against that (so you can also get rid of the substr workaround).  It would be even better/safer if you have also the date for start and end time. 

But just to give you an idea how you can set the logic up. Some Homework left for you 😛

[rock_s]

Hi @rnowitzki thanks for the replay, as I mentioned above few of jobs are starting end of today like 23:00 pm and completing next day early in the morning like 02:00 am. how can we solve this scenario, how the query will be if I use date and time(HH:MM) as you suggested. please help on this, Thanks in advance.

[rnowitzki]

Hi @rock_s ,

The SPL as given above is already working for the case when jobs are starting end of today and completing next day. 

This is the part that catches it:

if(curr_date_hour<substr(SLA_start_time,1,2),"NOT_STARTED"

So, if the start time of the Job is later than the current time, it assumes it has not yet started, not regarding the end time (because the end time does not matter in that case, it would not change the Status).

It would fail though, when the start time is after midnight, does that happen for your use case? Are there Jobs that start e.g. at 1:00am and end at 4:00am?

That would be one example where you'd need the date also, so you can be really sure what you compare (hour of today or tomorrow).
 
To your question about how to work with date and time, I can only give some hints without knowing your data better. 

First question would be, if you even have the date of start and end time already in your data? Or do you only get the hours as given in the table in your initial post? It would be dificult to guess the date in that case. You could assume everything  in end time that is (e.g.) between 0:00am and 7:00am is "tomorrow", but not sure if that reflects your data.

If you have the date, or can derive it somehow, the logic of my SPL would not change, but you would work with some strptime() and strftime()  (see Date and Time Functions) before and after applying the Status.
I would get the Unix time of all dates with strptime(), apply the logic and then make it human readable again with strftime().

We could help in more detail, when you show how your date fields look like (if you have date in your data). But I guess it helps to work with the documentation and try it for yourself first. (and if you are to some degree nerdish is also fun 🤓).

[rock_s]

Hi @rnowitzki Thnaks for the replay and info.

I have used the same as you suggested but the result is not as expected, 

| eval curr_date_hour = strftime(now(), "%H")

| eval status=if(curr_date_hour>substr(SLA_start_time,1,2) AND curr_date_hour>=substr(SLA_end_time,1,2), "COMPLETED", if(curr_date_hour<substr(SLA_start_time,1,2),"NOT_STARTED", if(curr_date_hour>=substr(SLA_start_time,1,2) AND curr_date_hour<substr(SLA_end_time,1,2),"RUNNING", "ERROR_IN_STATUS_DETECTION")))

Output:

:

Here highlighted jobs are started at 6 pm, 8 pm, and 9 pm today and supposed to complete next day 01:00 am but still its showing completed, I was executed this search at 10 pm. please help on this same.

I have day field as date_mday which stores day value like 14, So i alerted the query to compare current day  with date_mday as shown below but no luck.

| eval curr_date_hour = strftime(now(), "%H")

| eval curr_day = strftime(now(), "%d") 

| eval status=if(curr_date_hour>substr(SLA_start_time,1,2) AND curr_date_hour>=substr(SLA_end_time,1,2), "COMPLETED", if(curr_date_hour<substr(SLA_start_time,1,2) AND (curr_day!=date_mday),"NOT_STARTED", if(curr_date_hour>=substr(SLA_start_time,1,2) AND curr_date_hour<substr(SLA_end_time,1,2),"RUNNING", "ERROR_IN_STATUS_DETECTION")))

I have a fields like DATE=2020-06-14 date_mday=14 date_month=06 date_year=2020 SLA_start_time and SLA_end_time. Please help me this and thanks in advance.

[rnowitzki]

Hi @rock_s ,

Do you have a date field for the sla start- and endtime?
I mean, you wrote that you have e.g.:  DATE=2020-06-14 date_mday=14 date_month=06 date_year=2020
Do you have this for both of the SLA times?

I see why the COMPLETED examples got wrong in your screenshot. It assumes it was completed, because the end time is smaller compared to the current time. So we have to include the date here.

So, can you derive the whole date and time for the SLA start- and endtime?

[rock_s]

Hi @rnowitzki , Thanks for the quick replay,

I have only these fields like DATE=2020-06-14 date_mday=14 date_month=06 date_year=2020 SLA_start_time=16:00 and SLA_end_time=17:00 in each event. but values are the different in each event 😜 

can we include date or date_mday to get as we required like this??

Spoiler

| eval status=if(curr_date_hour>substr(SLA_start_time,1,2) AND curr_date_hour>=substr(SLA_end_time,1,2) AND SLA_start_time < SLA_end_time, "COMPLETED", if(curr_date_hour<substr(SLA_start_time,1,2) AND (current_day!=date_mday),"NOT_STARTED", if(curr_date_hour>=substr(SLA_start_time,1,2) AND (curr_date_hour<substr(SLA_end_time,1,2) OR SLA_start_time > SLA_end_time),"RUNNING", "ERROR_IN_STATUS_DETECTION")))

[rnowitzki]

Well, it seems to me as if your date fields are not refering to the sla end/start time.  
So, you actually have only the hour for the sla times.

In that case, I can not think of another solution than already posted above. (to assume the end_hour is refering to the next day if it is "smaller" than the start_hour)

Did you try my last SPL?

[rock_s]

Hi @rnowitzki

Now Iam able to get the data for sla start time and sla end time including date as well as shown below.

DATE=2020-06-14 date_mday=14 date_month=06 date_year=2020 SLA_start_time=16:00 SLA_start_date= 061320 and SLA_end_time=17:00 SLA_end_date=061420(MMDDYY). Plz help me on this, Thanks in advance.

[rnowitzki]

Hi @rock_s ,

This is now comparing the current time with the SLA start and end time including date, hour, minute:

| eval SLA_start_datetime=strptime(SLA_start_date+SLA_start_time, "%m%d%y%H:%M")
| eval SLA_end_datetime=strptime(SLA_end_date+SLA_end_time, "%m%d%y%H:%M")
| eval curr_datetime = now()

| eval status=case(SLA_start_datetime>curr_datetime, "NOT_STARTED", SLA_start_datetime<curr_datetime AND SLA_end_datetime<=curr_datetime, "COMPLETED",SLA_start_datetime<=curr_datetime AND SLA_end_datetime>curr_datetime, "RUNNING")

| eval SLA_start_datetime=strftime(SLA_start_datetime, "%m%d%y %H:%M")
| eval SLA_end_datetime=strftime(SLA_end_datetime, "%m%d%y %H:%M")
| eval curr_datetime = strftime(curr_datetime, "%m%d%y %H:%M")

| fields job SLA_start_datetime SLA_end_datetime curr_datetime status

The first block adds together start/end date and time and converts it to unix time, also adds the current time as unix time.

The second block sets the status.

The third block converts the date fields back to human readable format. 

And the last line, with fields just puts together the final output table.

[rock_s]

Hi @rnowitzki ,

Thanks for the replay, Its working as expected, but I would like include the not_completed count as well like as shown blow.

 | eval status=case(SLA_start_datetime>curr_datetime, "NOT_STARTED", SLA_start_datetime<curr_datetime AND SLA_end_datetime<=curr_datetime OR not_completed!=0, "COMPLETED",SLA_start_datetime<=curr_datetime AND SLA_end_datetime>curr_datetime, "RUNNING")

 above case also working as expected but where i am getting the problem is when the job is completed(SLE end time reached) at that time if not_completed count is not equal to 0 then Status should be "SLA Not Completed/Met" after some time(after sla end time) not_completed=0 its becoming "Completed"
Working now:
when the job is completed(SLA end time reached) at that time if not_completed count not equal to 0 then Status should be "SLA Not Completed/Met"
Expected:
when the job is completed(SLA end time reached) at that time if not_completed count not equal to 0 then Status should be "SLA Not Completed/Met"  after some time count is equal to 0 even though  Status should be "Not Completed/Met" for that day why because its SLA missed for that day with in sla allocated time.

Please help on this, thanks in advance.

[rnowitzki]

Hi @rock_s ,

I don't really understand the latest requirement.

Can you please just give an example output table as you need it, with some sample data. I guess it is easier to understand with that.

Thx

[rnowitzki]

I adjusted the SPL a bit, so it does work. I would still suggest to integrate the date, if you access to the date for sla start- and endtime.

| eval status=if(curr_date_hour>substr(SLA_start_time,1,2) AND curr_date_hour>=substr(SLA_end_time,1,2) AND SLA_start_time < SLA_end_time, "COMPLETED", if(curr_date_hour<substr(SLA_start_time,1,2),"NOT_STARTED", if(curr_date_hour>=substr(SLA_start_time,1,2) AND (curr_date_hour<substr(SLA_end_time,1,2) OR SLA_start_time > SLA_end_time),"RUNNING", "ERROR_IN_STATUS_DETECTION")))

I adjusted the detection of COMPLETED  by adding "AND SLA_start_time < SLA_end_time"  and for RUNNING ones by adding  "OR SLA_start_time > SLA_end_time",  assuming the sla end hour is smaller when it is refering to the next date.

This might not work for all cases. It would be more clean to work with the whole date. Also it might be more readable working with case instead of ifs.