topic Re: How do you set a human readable time format as earliest time? in Getting Data In

How do you set a human readable time format as earliest time?

AKG1_old1 — Fri, 08 Mar 2019 22:40:27 GMT

Hello,

I have one table that produces start time and end time in "%d-%m-%Y %H:%M:%S" (09-01-2019 07:44:05) format.

My requirement is when we drilldown this table, it set this time as earliest and latest time for the dependent panel.

    <drilldown>
      <eval token="time_token.earliest">round(strptime($row.Reset_Start$, "%d-%m-%Y %H:%M:%S"),0)</eval>
      <eval token="time_token.latest">round(strptime($row.Reset_End$, "%d-%m-%Y %H:%M:%S"),0)</eval>
      <set token="engine_id_token">$row.Engine_ID$</set>
      <set token="source_token">$row.source$</set>

 dependent search
<search>
<query> 
</query>
<earliest>time_token.earliest</earliest>
<latest>time_token.latest</latest>
</search>

Suppose time is "09-01-2019 07:44:05" in table( say GMT timezone). When I access it from GMT+1 timezone and convert it to epoch it automatically changes to "09-01-2019 08:44:05". I don't want it to be changed. I want to keep same time for all users accross different time zone.

One solution is to assign time without converting it to epoch but not sure if its possible.

Thanks

Re: How do you set a human readable time format as earliest time?

mayurr98 — Sat, 09 Mar 2019 03:00:00 GMT

can you share the XML? or the drill-down search ?
your requirement is quite unclear to me . what exactly do you want when you drill-down from the image you have shown ?
do you want to take the value of start and end time as it is and assign to some other field ? or you want as it is in the earliest and latest time?

Re: How do you set a human readable time format as earliest time?

nickhills — Sat, 09 Mar 2019 10:38:01 GMT

The problem with (accurately) converting to epoch is you need to know the timezone for the timestamp.

From the looks of your example, you don't have anything which indicates the TZ, so when you convert, it will assume UTC.

If your events are coming from all over the world (and different timezones) this will be quite challenging, unless you can somehow work out from the source/host where the event came from.
However, if your server(s) are all in one place you can 'manually' compensate for TZ by adding or subtracting 3600 for each hour.

...|eval my_earliest_epoch=strptime(startTime, "%d-%m-%Y %H:%M:%S") + (3600 * 5)

Edit: note you have to invert the addition/subtraction vs the normal UTC notation. Ie, if your TZ is UTC-5 you have to ADD 5 hours to epoch

Re: How do you set a human readable time format as earliest time?

AKG1_old1 — Sun, 10 Mar 2019 15:28:03 GMT

@mayurr98 I have updated the question with more details.

Re: How do you set a human readable time format as earliest time?

woodcock — Sun, 10 Mar 2019 19:03:03 GMT

This comes from this other Q&A:
https://answers.splunk.com/answers/590067/how-do-i-map-my-personally-tz-adjusted-time-to-ano.html

Here are the pieces of the puzzle:

This shows you the TZ settings for your users (probably just you, unless you have admin):

|rest/services/authentication/users/ splunk_server=local

This shows you the possible TZ settings on your Search Head:

|rest/servicesNS/-/search/data/ui/manager 
| regex eai:data="Time zone" 
| head 1 
| rename eai:data AS _raw 
| table _raw 
| rex mode=sed "s/(?ms)^.*Default System Timezone --\"\/>[\s\r\n]+(.*?)<\/options>.*$/\1/" 
| eval raw=split(_raw, "<opt value=") 
| mvexpand raw 
| rex field=raw "^\"(?<value>[^\"]+)\"\s+label=\"(?<label>[^\"]+)\"" 
| fields - _raw raw
| search label="*" AND value="*"

This is what really does what we need:

|makeresults 
| eval MYtime=strftime(_time, "%m/%d/%Y %H:%M:%S %Z")
| eval UTCtime=MYtime
| rex field=UTCtime mode=sed "s/\s+\S+$/ UTC/"
| eval UTC_time=strptime(UTCtime, "%m/%d/%Y %H:%M:%S %Z")
| eval TZdelta = round(_time - UTC_time, 0)
| eval TZdeltaDuration = if((TZdelta<0), "-", "") . tostring(abs(TZdelta), "duration")
| rename COMMENT AS "Calcluate 2 hourmin values: 1 personal which varies, and 1 UTC-normalized which does not"
| eval MYhourmin=strftime(_time, "%H%M")
| eval UTChourmin=strftime(_time + TZdelta, "%H%M")

You may have seen me gripe from time to time about the date_* fields and here is why. First of all, not all events have them; only those that have timestamps inside of the events. But if your events do have it, it is actually the thing that I am trying to fix here: it is a UTC/GMT-normalized value that is not normalized to your personal TZ setting. So if my events had the date_* values, I could have just used date_hour.

Re: How do you set a human readable time format as earliest time?

AKG1_old1 — Wed, 13 Mar 2019 12:01:41 GMT

Thanks @woodcock : This is really helpful. for this issue I got one easy solution. but we have some other use case in which this can be useful. !!

For this issue:
Instead of converting to epoch time in drilldown, I am converting it in search query and magically I am getting what I want. Not sure though why I am getting different time when converting it in drilldown compare to search query.

**Search Query:**
| eval epoch_earliest= strptime(Reset_Start, "%d-%m-%Y %H:%M:%S")
| eval epoch_latest= strptime(Reset_End, "%d-%m-%Y %H:%M:%S")

**Drilldown:**
          <set token="tps_selection_earliest">$row.epoch_earliest$</set>
          <set token="tps_selection_latest">$row.epoch_latest$</set>

Re: How do you set a human readable time format as earliest time?

woodcock — Thu, 14 Mar 2019 03:32:13 GMT

That should work perfectly. If not it is a bug.