Getting Data In
Highlighted

I am running a query in business hours what output I get.

Path Finder

hello everyone ,
I have a quick question, I am running a query in business hours and syslogs are generating in different timezones ex., UTC, PDT, IST etc. so when I run the below query it basically gives the list of total calls and usage of individual directory numbers who are from different timezones.
how do i know that the splunk giving output with the originating timezone syslogs which is for there dependent timezones.

0 Karma
Highlighted

Re: I am running a query in business hours what output I get.

Super Champion

your query is too complex for me to understand as it uses macro and so many calls. Any chance to make it simple to put the core part of search/issue

0 Karma
Highlighted

Re: I am running a query in business hours what output I get.

Path Finder

thanks for replying, but it seems query is correct. but the timezones are giving problem.

0 Karma
Highlighted

Re: I am running a query in business hours what output I get.

Builder

If the time value that is being used to determine the _time field contains the timezone in it, the GUI should automatically format the _time to accommodate whatever you have set in your accounts settings.

Example: If my logs have this as the date formatting: 2019-05-24T14:08:37,445-4:00 the sourcetype in the conf would need to have the timefield set to this: %Y-%m-%dT%H:%M:%S,%3N%:z

It would know that the event time is set to EDT with the -4:00 and you are set to whatever your timezone is in your settings and therefore if you search the last 4 hours it will automatically search the equivalent last 4 hours on those other timezones. If the sourcetype is not configured to account for the timezone then you are at the mercy of that and will have to craft your original earliest/latest to pull the maximum earliest/latest times to get all the different time zones. I have done this type of stuff before and can help, but ideally you really want to get your configuration setup so it accounts for the timezone on the timestamps.

If this comment/answer was helpful, please up vote it. Thank you.
0 Karma
Highlighted

Re: I am running a query in business hours what output I get.

Builder

Hi splunkuseradmin

When Splunk ingests your syslog data, if the syslog event does include timezone data, Splunk will change that timestamp to an epoch time value (seconds from epoch) adjusted for the timezone detected. This epoch time recorded is timezone agnostic, or UTC/GMT.

In order for your search to work properly, you will have to "map" the localized values of the time you're going to be matching (day of week and hour of day in your case).

Example syslog event:
2019-05-24T10:03:11.002 EDT INFO Somebody called 855-555-1212. Connection lasted for 1240s.

Splunk writes this timestamp as 1558706591.002.

When you execute a search, Splunk will convert the time value to match whatever the logged in user has set as their timezone information for their UI settings (Click your User in the top right of the window and select preferences). If you're in EDT, you will see 2019-05-24T10:03:11.002 EDT when you search for this event. If you're in PDT you will see 2019-05-24T07:03:11.002 PDT when you search for this event.

Therefore, you would need to set a field to the local time of the device where syslog was collected and search that value instead of using the default available time fields.

Example if you have the same event as above with a timestamp of 2019-05-24T10:03:11.002 EDT. Let's say you're in PDT and not EDT, so your searches always show the timestamp in Splunk as 2019-05-24T07:03:11.002 PDT. You can either calculate the offset value, or extract the values directly from the date/time string in the event. Below shows how you would extract from the string value in the event assuming the field name for the date/time value is datetime.

Get the local day:
| eval localday = strftime(strptime(datetime, "%F"), "%a")
Get the local hour:
| eval localhour = replace(datetime, "^.*T(\d\d):.*$", "\1")

Then using the local values for the day and time you can do your comparison:
| eval is_business_hours=case((localday=="Sat" OR localday=="Sun"),0,(localhour >7 AND localhour <17),1,true(),0)

I hope this helps.

0 Karma
Highlighted

Re: I am running a query in business hours what output I get.

Path Finder

any suggestions?

0 Karma
Highlighted

Re: I am running a query in business hours what output I get.

Builder

Can you provide the full non-macro search (you can press CTRL-E in the search box and it will expand the macros for you), as well as several sample (sanitized) events that the search should work on, AND (finally) what you want the expected results to show. Give me these, and I can provide you with the right search.

0 Karma
Highlighted

Re: I am running a query in business hours what output I get.

SplunkTrust
SplunkTrust

Hi @splunkuseradmin,

Seems like someone's already had this "mix of timezones" problem:

https://answers.splunk.com/answers/640623/calculate-timezone-and-adjust-timestamp.html

I think using the first solution in that link will solve your issue.

Cheers,
David

0 Karma
Highlighted

Re: I am running a query in business hours what output I get.

SplunkTrust
SplunkTrust

did you have a look ?

0 Karma
Highlighted

Re: I am running a query in business hours what output I get.

Path Finder

yes I had a look, it seems to be good solution but as you see my query is pretty big .
and I am new to this, wondering if you can able to fix somehow.

0 Karma