topic Re: How to compare data from the same month for multiple years? in Splunk Search

How to compare data from the same month for multiple years?

joseph_hazlett — Fri, 06 Oct 2017 17:46:21 GMT

I am doing a very basic search that just shows the top URIs during a specific month each year. I would like to be able to put multiple years within the same graph to do a quick visual comparison. My search is as follows:

source="/opt/gathered-logs/*/apache2/access_log" | stats count by uri

And I define the date range for the search (November 2015/16/17/etc.) I get the information I want from the graph in a pie graph, but it's not very helpful for comparison purposes. I'd like to just show an overall line graph that displays Nov. 2015 vs Nov. 2016 on the same graph. I don't really need to know individual stats per URI, so if I remove the |stats count by uri, I get the nice general green bar graph in splunk, but I don't see a way to define two different date ranges and overlay them or whatever. Is this possible?

Re: How to compare data from the same month for multiple years?

cmerriman — Fri, 06 Oct 2017 17:53:04 GMT

what about

source="/opt/gathered-logs/*/apache2/access_log"|timechart span=1mon count|timewrap 1mon

Re: How to compare data from the same month for multiple years?

DalJeanis — Fri, 06 Oct 2017 20:50:53 GMT

Try this

 source="/opt/gathered-logs/*/apache2/access_log" 
| eval Month=strftime(_time,"%Y-%m-%d")
| where substr(Month,6,2)="11"
| stats count as mycount by Month uri

... now you have all the info for each November (Month=11).

Let's take the traffic for the top 5 uris THIS year, and check the trend for those for prior years...

| appendpipe [| sort 0 - Month - mycount | head 5 | table uri | eval myflag="keepme"]
| eventstats values(myflag) as myflag by uri
| where myflag="keepme" AND isnotnull(mycount)
| eval _time = strptime(Month,"%Y-%m-%d") 
| timechart sum(mycount) by uri

updated - changed top 5 to head 5

Re: How to compare data from the same month for multiple years?

joseph_hazlett — Fri, 06 Oct 2017 22:05:37 GMT

This almost seems to work. I had to add "uri" after "top 5" for it to not error out. I just realized I haven't been collecting these Apache logs for more than a year, so I can't even test to see if this is fully functional until November rolls around. Thank you DalJeanis, I'm going to save this as a report and see what happens after our busy November ends. 🙂

Re: How to compare data from the same month for multiple years?

DalJeanis — Mon, 09 Oct 2017 22:51:43 GMT

@joseph.hazlett - sorry, that should be |head 5

Re: How to compare data from the same month for multiple years?

joseph_hazlett — Mon, 09 Oct 2017 23:00:15 GMT

Thanks. I've updated the report and hopefully will see results next month. 🙂

Re: How to compare data from the same month for multiple years?

abhijitsaoji — Thu, 10 Jan 2019 10:22:49 GMT

has this worked for you? I am also looking for the same. Please let me know.