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

[joseph_hazlett]

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?

[DalJeanis · Accepted solution]

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

[joseph_hazlett]

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. 🙂

[DalJeanis]

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

[joseph_hazlett]

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

[cmerriman]

what about

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

[abhijitsaoji]

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