How to subtract 15 hours from my event timestamp

[Mohsin123]

Hi, i'm making a batch job status panel for websphere team . i need to show those jobs as pending state who are running since 15 hours till now . so what my purpose is that whatever time itr is now , it will subtract 15 hours from now and the time the job was last updated and then it will update the job status as pending . can anyone please help .

[Sukisen1981]

Hi - @shraddhamuduli ·

| eval current_time=strptime(strftime(now(),"%m-%d-%Y %H:%M:%S"),"%m-%d-%Y %H:%M:%S") |eval job_time=strptime(strftime(_time,"%m-%d-%Y %H:%M:%S"),"%m-%d-%Y %H:%M:%S") | eval diff=round((current_time-job_time)/3600,2)| eval IsPending=if (job_status="Complete" AND diff>=15, "Pending","Running")

1- You really need to be careful with you _time format, the code above assumes your _time is like 09-23-2017 17:23:08 format, else you just need to pass the pope format. It will affect the field diff as well. in short current_time-job_time in this case gives the difference in hours.
2- You need to figure out the proper job_status field or the job completion status field name in you events.
3 -Lastly, even if the job is complete and time elapsed is 14.9 hours it will still come as pending

[Mohsin123]

thanku so much..i will try this tomorrow

[Sukisen1981]

hey, did you try it out?

[niketn · Accepted solution]

@shraddhamuduli, try with either one of the following approaches:

1) Pass -15h@h as the earliest snap to time for your query using Splunk Time Picker. Following is the Simple XML example:

2) Use earliest and latest options in the base search to apply the -15h@h as the earliest time as selected in the time picker.

Following is a run anywhere dashboard based on Splunk _internal Index which exemplifies the two approaches:

<form>
  <label>Splunk Answers 577166 - Pull Events from 15 hours back</label>
  <fieldset submitButton="false">
    <input type="time" token="tokTime">
      <label></label>
      <default>
        <earliest>-15h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Option 1 - Time passed through earliest and latest options for Search in Simple XML- Splunk Internal Log Errors</title>
      <table>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!="INFO"
| stats count by component            
          </query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
        </search>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Option 2 - Time passed through earliest and latest options in the Base Search - Splunk Internal Log Errors</title>
      <table>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!="INFO" earliest="$tokTime.earliest$" latest="$tokTime.latest$"
| stats count by component            
          </query>
        </search>
      </table>
    </panel>
  </row>
</form>

[Mohsin123]

Hi @niketnilay actually you are showing results that are >than 15 hours from now . but my question is ...i have current time now() and the last updated event time for a job , say current time is 16:04 pm and the last updated _time for a job is 24/09 09:00 am . so this job was complete in last status . but as the status hasnot updated to started yet and the timesta,mp is more than 15 hours then i can conclude that the job is still running ..i will forcibly convert the status of this job from complete toi pending !

[niketn]

@shraddhamuduli, following query might change as per the fields/values you have in your real-time data. Current query is based on partial information provided in your details. Assuming status as the field containing job status (PS: field names are case sensitive, so make sure that it matches exactly as the field present in your data). Couple of values to be used in the query are "running" and "complete". Values are case insensitive, however make sure the values are the same event without same casing.

<YourBaseSearch>
| eval status=if(now()-_time>54000 AND status=="complete","running",status)
| <YourRemainingSearch>

Since _time and now() will give epoch times they can directly be used in calculation (without any manipulation/conversion).
PS: 54000 sec = 15*60* 60 sec