How to search accounting transactions based on the local date where the event was created, not the reporting user's timezone?

[pdurrer]

I have accounting transactions from different timezones coming into Splunk via a message queue. These transactions are received throughout the day and evening. I've configured Splunk to populate the _time field using the timestamp of the transaction.

From the search and reporting standpoint, I'd like the user to be able to select a time range of "Yesterday" or "Previous Month", and show all the transactions for that accounting day or month respectively. The problem is when a transaction occurs late in the day in one timezone … it may be translated to another date when the reporting user is in an earlier timezone.

For example, suppose a transaction from our Honolulu office was created at 2015-09-30T23:00:00.999-10:00. My Splunk user account is configured for Eastern Time. Assuming it's October, and I try to search for all of last month's transactions, the aforementioned transaction will not appear, because it gets translated to October 1st (2015-10-01T05:00:00.999 Eastern Daylight Time). I'd like the search based on the local date … not one based on the reporting user's timezone.

I thought maybe a custom time range would do the trick, but came up dry there.

Any suggestions on how I might approach this problem?

[woodcock · Accepted solution]

First you need some way to select a different TZ. But once you have decided on a different timzezone, you can "pretend" that your times are different than they are like this. Let's say your local user configuration is different from the other ("local" as you put it) TZ by 2 hours. You simply run your search for Last so that your get from now() backwards to something that covers your desired range. So instead of "Previous mohth" you select "Last 32 days" and then do something based on the difference of these 2 searches:

... | eval _time = _time - 2*60*60 | bucket _time span=1mon | stats count by _time
... |                                bucket _time span=1mon | stats count by _time

Then you would tack on | where ... to strip off the slag on the edge and just keep the 1-month part you care about