Changing timezone in report
I am collecting VPN session data from VPN concentrators located around the world into a session list in my manager. Since my manager is located in Eastern Timezone, all timestamps are in EST/EDT. I was asked for a report from our UK office about the usage of their VPN concentrator. They want times the user logged in and logged out, which i have, just in a wrong timezone. Is there a way to convert the timestamps into a different timezone as the query runs or the report renders? The StartTime and EndTime columns in the session list are the manager generated session timestamps, so i am assuming they are in the correct type, i just can't find a way to convert them into a different timezone. This is on ESM 4.0 SP3 Patch 3.
I had a couple thoughts but my guess is you might have already looked into them or the variables aren't there in your version. The first is do a getHour which returns an integer. Add the time difference then do a concat with getMinutes. The problem will be when it crosses days. The second one is using the variable timeDifferenceInSeconds and put in midnight of Jan 1, 1970. Then add the appropriate number of seconds.
Of course in both cases you will have to do additional variables on the backend which may or may not be really feasible. Probably time to put in a feature request to add or subtract seconds from a time field (unless I'm blind and that already exists) though that doesn't help you in the short term.
That's a pretty good workaround and i might try it. But I am surprised that there isn't a dropdown somewhere in the report or the query that would let me choose the timezone of the report. Sounds simple enough, right? And something that large organizations, spread over multiple timezones might need. Can someone from ArcSight comment on whether a TTP or a Feature Request already exists for this?
Just had another thought though not sure which option has a higher pain level. Depending on how your events come into your ESM you could possibly dual feed the events from your concentrators. In other words run two different connectors from these event sources. On the ESM side you pick one, adjust the time offset on the connector, then using the agent ID ignore the events from the bulk of your content. With those events though you could create, in theory, localized session lists to create the localized reports. That could be a real PITA though since you would be dealing with duplicate events in the system and may not be possible depending on how those events are brought in. The upside is not having to create variables upon variables as the time math would already be done as the events come in.
Actually, the getHour, getMinute, etc. variables have an option of selecting a time zone. So if the remote time zone appears in one of the event's fields (agentTimeZone, deviceTimeZone, etc.) you can concatenate a chain of these variables with the appropriate time zone.
I hope this helps,
Turns out all of that extra work is not necessary, you can specify the query timezone in reportparameters tab -> query parameters -> timezone parameter. And you can even map that to a custom parameter to be prompted for the timezone during every run of the report. I had a feeling something like this had to be there.