topic Re: How to build a timechart from a specific field and convert it from UTC to PST? in Splunk Search

How to build a timechart from a specific field and convert it from UTC to PST?

evanleair — Tue, 13 Sep 2016 19:25:45 GMT

Hello Splunk Masters,

The search query I have built out works great, but due to the amount of requests hitting us, Splunk can get backed up and post a bunch of logs all at once which causes a manufactured spike in my chart.

I would love to work around this by building a timetable off of a custom time field (BeginRequest-UTC) and converting it to PST. This way we're able to see when the requests are hitting our IIS services and accurately monitor when spikes are generated. I also need to be able to break it down by UserAgent as well to determine which user agents are sending successful responses and unsuccessful responses.

The below query is what I'm using to look at successful IIS responses broken down by UserAgent.

sourcetype=iis_logs http_status!=40* http_status!=5* | timechart count by UserAgent

Any help is appreciated!

Thanks,

Evan

Re: How to build a timechart from a specific field and convert it from UTC to PST?

sundareshr — Tue, 13 Sep 2016 19:32:41 GMT

Try this
UPDATED

sourcetype=iis_logs http_status!=40* http_status!=5* | eval time=strptime(BeginRequest-UTC, "%Y-%m-%d %H:%M:%S.%3N") |  eval time=time+(10*3600) | bin span=1h time |stats count by time UserAgent | eval time=strftime(time, "%x %X")

*OR*

sourcetype=iis_logs http_status!=40* http_status!=5* | eval time=strptime(BeginRequest-UTC, "%Y-%m-%d %H:%M:%S.%3N") | eval time=time+(10*3600) | bin span=1h time |chart limit=0 count over time by UserAgent | eval time=strftime(time, "%x %X")

I believe UTC>PST = 10hrs = 3600*10 (please verify)

Re: How to build a timechart from a specific field and convert it from UTC to PST?

evanleair — Tue, 13 Sep 2016 20:01:29 GMT

What type of field would I put in for "USE APPROPRIATE MODIFIERS HERE"? An example value I would get would be: 2016-09-13 19:55:09.503, and when I adjust the query to be:

sourcetype=iis_logs http_status!=40* http_status!=5* | eval time=strptime(BeginRequest-UTC, "earliest=-15m") | eval time=time+(7*3600) | bin span=1h time |chart limit=0 count over time by UserAgent | eval time=strftime(%x %X)

sourcetype=iis_logs http_status!=40* http_status!=5* | eval time=strptime(BeginRequest-UTC, " %H:%M:%S") | eval time=time+(7*3600) | bin span=1h time |chart limit=0 count over time by UserAgent | eval time=strftime(%x %X)

I get this error: Error in 'eval' command: The arguments to the 'strptime' function are invalid.

What can be done to get around that?

Re: How to build a timechart from a specific field and convert it from UTC to PST?

sundareshr — Tue, 13 Sep 2016 20:07:02 GMT

Try the updated query

Re: How to build a timechart from a specific field and convert it from UTC to PST?

evanleair — Tue, 13 Sep 2016 20:41:03 GMT

I'm running the updated query and am still getting the same error. Any other ideas? Thanks so much in advance!

Re: How to build a timechart from a specific field and convert it from UTC to PST?

sundareshr — Tue, 13 Sep 2016 20:43:18 GMT

🙂 The error is in the second eval statement. I have corrected it now.