| mcatalog values(device) as device where index=*_metric | mvexpand device | join type=left device [ | mstats max(_value) as qgets where metric_name=*.QueueGets, queue_name=_monitor, service=*nq index=*_metric earliest=-3m by device, customer ] | eval online = if(qgets>0, "Online", "Offline") | search online=Offline ... View more

Hi @rock7177 and @woodcock, thank you for pointing out that my comment was not fully explained. Apologies for incorrectly using down vote, it will not happen again (unfortunately I can't remove the vote now - "Sorry, but you can't cancel a vote after more than 1 day"). There are two problems with the provided answer: it converts not from UTC to EST, but instead from EST to UTC (the wrong direction), and it does not take the timezone configured in the users preferences into account. An example follows. index=* | head 1 | eval time_in = strptime("2016-10-17 00:00:00 UTC", "%F %T %Z") | eval time_adjusted = strptime(strftime(time_in,"%F %T EST"),"%F %T %Z") | eval time_in_printed = strftime(time_in, "%F %T %Z") | eval time_adjusted_printed = strftime(time_adjusted, "%F %T EST") | table time_in_printed, time_adjusted_printed Assuming my user preferences are set to UTC timezone, then I get the following results: time_in_printed time_adjusted_printed 2016-10-17 00:00:00 UTC 2016-10-17 05:00:00 EST This is incorrect. The correct answer would be 2016-10-16 19:00:00 EST , see https://savvytime.com/converter/utc-to-est/00-00. The problem is that it is replacing the timezone information (+00:00) with EST (-05:00), rather than converting it. However, if my timezone is set to +10:00 Brisbane: time_in_printed time_adjusted_printed 2016-10-17 10:00:00 AEST 2016-10-18 01:00:00 EST This is a different but also incorrect answer. This is because the conversion method does not account for the timezone adjustment caused by updating the user preferences to anything other than UTC. ... View more

Inspired by the excellent answers provided here, it is possible to convert to an arbitrary timezone provided that timezone always has a fixed UTC offset (i.e. does not support day light savings time). eval _timezone = "AEST" | eval _time_AEST = _time - (strptime("2000-01-01 +00:00", "%F %:z") - strptime("2000-01-01 " . strftime(_time, "%:z"), "%F %Z")) + (strptime("2000-01-01 +00:00", "%F %:z") - strptime("2000-01-01 " . _timezone, "%F %Z")) | eval time_in_AEST = strftime(_time_AEST, "%F %T " . _timezone) The above works by first subtracting the UTC offset of the timezone as configured in the users preferences, then adds on the UTC offset of the timezone you configure (in this example AEST , but could be -05:30 or any valid Splunk timezone identifier). The UTC offset of the two timezones in question is calculated by using a reference date (I chose 2000-01-01 arbitrarily) and parsing it twice, first using the UTC timezone and then the queried timezone, and the difference of the two yields the timezone offset. A couple of things to remember: in the example, Splunk interprets the _time_AEST variable as seconds since epoch (1970-01-01 00:00:00 UTC), and so technically Splunk is interpreting this as a different 'real world' time -- if you attempt to print the timezone of the date, it will incorrectly report the users configured timezone. Instead, whenever printing this date always include the timezone manually and don't use the %Z timezone formats (as is done in the last line of the example). I discovered that Splunk Light Version 6.4.1.2 seems to have a bug where it ignores the users timezone and always reports UTC. Using the above method will not break under such circumstances. The above can easily be converted to a macro. However I have found that when passing arguments to a macro it is best to use pre-calculated fields, rather than expressions (since expressions will have their " quotes stripped when passed as arguments to a macro). Splunk does not seem to support the tz database - it only seems to support timezones with a fixed offset from UTC. For example, if you live in Sydney you would usually select the "Australia/Sydney" timezone from timezone dropdowns - however this item is not available in Splunks list of timezones in the user preferences. I believe this is because Sydney adheres to Day Light Savings time. If you wish to run a query using local time of an area which is not always a fixed offset from UTC, then you will have to upload a timestamp to Splunk in local time (of course this only applies if you are in control of your input sources) and not have Splunk interpret it (i.e. use a different field other than the _time property). For our use case, we are uploading the 'real world time' using time since Epoch (assigned to the _time property), and additionally upload a timestamp field formatted as a date in local time which Splunk interpets as a string. This gives Splunk enough information to assign the correct time to the event, but also allows us to run queries against the local time where the data is sourced from. ... View more

With the help of the excellent answers from @sideview and @jmccr78 I came up with what I believe to be an improved solution (simplified and robust). eval _time_UTC = _time - (strptime("2000-01-01 +00:00", "%F %:z") - strptime("2000-01-01 " . strftime(_time, "%:z"), "%F %Z")) | eval time_in_UTC = strftime(_time_UTC, "%F %T UTC") This works by computing the UTC offset of the timezone configured in the user preferences and subtracting it from the time (similar to the other answers). However the timezone offset is calculated simply by using a reference date (I chose 2000-01-01 arbitrarily) and parsing it twice, first using the UTC timezone and then the users configured timezone, and the difference of the two yields the timezone offset. This method can easily be extended to support converting to an arbitrary timezone. eval _timezone = "AEST" | eval _time_AEST = _time - (strptime("2000-01-01 +00:00", "%F %:z") - strptime("2000-01-01 " . strftime(_time, "%:z"), "%F %Z")) + (strptime("2000-01-01 +00:00", "%F %:z") - strptime("2000-01-01 " . _timezone, "%F %Z")) | eval time_in_AEST = strftime(_time_AEST, "%F %T " . _timezone) This works by first subtracting the UTC offset of the timezone as configured in the users preferences, then adds on the UTC offset of the timezone you configure (in this example AEST , but could be -05:30 or any valid Splunk timezone identifier). A couple of things to remember: in the example, Splunk interprets the _time_AEST variable as seconds since epoch (1970-01-01 00:00:00 UTC), and so technically Splunk is interpreting this as a different 'real world' time -- if you attempt to print the timezone of the date, it will incorrectly report the users configured timezone. Instead, whenever printing this date always include the timezone manually and don't use the %Z timezone formats (as is done in the last line of the example). I discovered that Splunk Light Version 6.4.1.2 seems to have a bug where it ignores the users timezone and always reports UTC. Using the above method will not break under such circumstances. The above can easily be converted to a macro. However I have found that when passing arguments to a macro it is best to use pre-calculated fields, rather than expressions (since expressions will have their " quotes stripped when passed as arguments to a macro). Splunk does not seem to support the tz database - it only seems to support timezones with a fixed offset from UTC. For example, if you live in Sydney you would usually select the "Australia/Sydney" timezone from timezone dropdowns - however this item is not available in Splunks list of timezones in the user preferences. I believe this is because Sydney adheres to Day Light Savings time. If you wish to run a query using local time of an area which is not always a fixed offset from UTC, then you will have to upload a timestamp to Splunk in local time (of course this only applies if you are in control of your input sources) and not have Splunk interpret it (i.e. use a different field other than the _time property). For our use case, we are uploading the 'real world time' using time since Epoch (assigned to the _time property), and additionally upload a timestamp formatted as a date in local time field which Splunk interpets as a string. This gives Splunk enough information to assign the correct time to the event, but also allows us to run queries against the local time where the data is sourced from. ... View more

Thank you for your excellent answer. I removed | eval time_utc_epoch=strftime(_time, "%s") | convert num(time_utc_epoch) and instead used _time directly in the last | eval time_utc_epoch= expression (as _time is already in epoch, no need to convert it again). I also removed the outer if from the last | eval time_utc_epoch= expression, as adding/subtracting zero would have no effect. ... View more

Try this answer: https://answers.splunk.com/answering/224136/view.html ... View more

I downvoted this post because this is not correct. this query says "pretend this utc date is actually a est date" which will add 5 hours (rather than subtract). what you want is to say "given this utc date, what would it be in est time"? this is not possible to do simply in splunk. ... View more