Month over month reports

[combinatorics]

I like to compare my logs from a given day/week to the same day, week the previous month, in order to visualize growth/decline, spot anomalies, etc. Obviously I can easily define a timespan for the last 28 days (or however I want to define my initial period), but I have no idea how to also grab events for the previous 28 days and graph them on top of each other.

Basically I need something like this...

search 1: (some search criteria) earliest=-28d latest=now

search 2: (some search criteria) earliest=-56d latest=-28d

Display some graph of data from search 1 over top of search 2

Any ideas?

[jtrujillo]

tafiedler and fdimas sounds like you guys got something imported from Service Desk Express into your system,

Is there any way you could share some of the DB Connect (I assume that is how its working) info with me? Even a basic SELECT statement could work if you have it. I just don't know SDE schema at all.

[fdimas]

My solution may not be the most elegant one but this is what I did and worked for me:

index=sde sourcetype=sde_tickets State=C earliest=-13d@d latest=-7d@d CloseGroup="IS DESKTOP" | dedup Incident | timechart span=1d count(Incident) as Previous | eval Day=strftime(_time,"%a") | table Day,Previous | APPENDCOLS [ search index=sde sourcetype=sde_tickets State=C earliest=-6d@d CloseGroup="IS DESKTOP" | dedup Incident | timechart span=1d count(Incident) as Current | eval Day=strftime(_time,"%a") | table Day,Current]

[tafiedler]

Here is what I did... I wanted to compare the last 7 running days with the previous 7 running days and overlay them on the same graph.

I wrote the following queries and strung them together to make sure they worked:

index=sde sourcetype=sde_tickets State=C earliest=-13d@d latest=-7d@d CloseGroup="IS DESKTOP" | dedup Incident | timechart span=1d count(Incident) as Previous | eval Day=strftime(_time,"%a") | table Day,Previous | join Day type=outer [search index=sde sourcetype=sde_tickets State=C earliest=-6d@d CloseGroup="IS DESKTOP" | dedup Incident | timechart span=1d count(Incident) as Current | eval Day=strftime(_time,"%a") | table Day,Current]

I tried saving them and then joining using savedsearch but it didn't work. I am sure I was missing something and that it would with the correct syntax.

The above query lets me display the two periods, each as series and they graph nicely together. If you had overlapping buckets, this wouldn't work. For example, if I had wanted to do the last 30 days I would have had to come up with some other value for my X axis instead of the name of the day of the week.

My preference is to not do it this way, but to put two charts on the dashboard, one to show current and one to show previous, but it was how others wanted it.

[dwaddle · Accepted solution]

Relevant answer here --> http://splunk-base.splunk.com/answers/2712/line-chart-comparing-yesterdays-result-with-todays-result...

That is an example of week vs week or day vs day -- but, the general principle remains the same. The common concept here, though, is that "(some search criteria)" for the first time window and the second time window are the same search criteria.

[combinatorics]

This worked perfectly. Not only did it answer my question, I now have a reusable month_over_month search macro. Thanks for the help.