topic Re: Comparing today's data with last week's data in Splunk Search

Comparing today's data with last week's data

fpigeon — Wed, 16 May 2012 13:58:06 GMT

Hi,

I have a problem with comparing today's data with data from a week ago. Here is the query I run:

sourcetype="abc" Service="xyz" earliest=-0d@d latest=now | eval ReportKey="Today" | append [search sourcetype="abc" Service="xyz" earliest=-7d@d latest=-6d@d | eval ReportKey="LastWeek" | eval new_time=_time+60*60*24*7] | eval _time=if(isnotnull(new_time), new_time, _time) | timechart avg(Time) by ReportKey

This works to some extend. The issue is that the subquery - data from a week ago - does not appear fully. Meaning I can see data from 4PM until midnight but not prior to 4PM and I know for a fact that there is data for the entire day - starting at 00:00:01AM.

Can someone help me with this one?

Thank you very much!

Re: Comparing today's data with last week's data

lguinn2 — Wed, 16 May 2012 18:02:51 GMT

I see a possible problem in your search, but I don't think that it is causing the incomplete subsearch results. I would do this:

sourcetype="abc" Service="xyz" earliest=-0d@d latest=now |
eval ReportKey="Today" | 
append [search sourcetype="abc" Service="xyz" earliest=-7d@d latest=-6d@d | 
eval ReportKey="LastWeek" | 
eval _time=_time+60*60*24*7] | 
timechart avg(Time) by ReportKey

How many events should be returned from your subsearch? What did you get when you ran

sourcetype="abc"  Service="xyz" earliest=-7d@d latest=-6d@d | 
eval ReportKey="LastWeek" | 
eval _time=_time+60*60*24*7

The maximum number of events that can be returned from a subsearch is 10500, but it might be set lower in your Splunk environment.

There is more info in the Splunk User Manual under Performance of Subsearches (in the middle of the page)

Re: Comparing today's data with last week's data

kristian_kolb — Wed, 16 May 2012 19:18:26 GMT

You might have a slight typo in the search - avg(Time), however Time is never evaluated.

Re: Comparing today's data with last week's data

fpigeon — Thu, 17 May 2012 00:10:42 GMT

You are right, the subsearch returns 123,949 matching events which is way over the 10,500 you mentioned in your post. I guess now I will have to find a better way to compare today's values with last week's values.

Thank you very much for your precious time.

Re: Comparing today's data with last week's data

lguinn2 — Thu, 17 May 2012 01:08:57 GMT

There are solutions! If you are willing decide up-front that you want the timechart to report in 30-minute intervals (or longer), try this

sourcetype="abc" Service="xyz" earliest=-0d@d latest=now |
bucket _time span=30m |
stats avg(Time) as avgTme by _time |
eval ReportKey="Today" | 
append [search sourcetype="abc" Service="xyz" earliest=-7d@d latest=-6d@d | 
bucket _time span=30m |
stats avg(Time) as avgTme by _time |
eval ReportKey="LastWeek" | 
eval _time=_time+(60*60*24*7)] | 
chart max(avgTme) as avgTime over _time by ReportKey

The above summarizes the data before returning from the subsearch. And another way of doing it, which may be even better:

sourcetype="abc" Service="xyz" earliest=-7d@d latest=now |
fields Time |
eval weekAgo = relative_time(now(), "-6d@d") |
eval today = relative_time(now(), "@d") |
eval ReportKey = "omit" |
eval ReportKey = case(_time < weekAgo,"Last Week", _time > today,"Today") |
where ReportKey != "omit" |
eval _time = if(ReportKey=="Last Week", _time+(60*60*24*7), _time) |
timechart fixedrange=f avg(Time) as avgTime by ReportKey

This second solution does not use a subsearch. Instead, it collects all the data for the last week, and then categorizes it based on _time. If the data is not during the time that we want, it is dropped by the where command. The _time is tweaked for data from a week ago, and then reported by the categories.

I haven't actually tested these searches, so there may be typos. (I try to check...) Let me know if it doesn't work, and I'll help debug my typing!

Re: Comparing today's data with last week's data

fpigeon — Thu, 17 May 2012 02:29:40 GMT

Wow! It is actually working, thank you so much. Now, if I may push my luck even more 🙂 I was wondering if there is a way to improve the readability of the chart. When I look at the chart I see seven days even if the data is plotted on a single day. Therefore, I was wondering if it would be possible to remove the other days from the chart so it shows only today.

Thank you again.

Re: Comparing today's data with last week's data

lguinn2 — Thu, 17 May 2012 05:13:08 GMT

Okay, I just noticed a typo and fixed it. Which one of the searches are you using? I am not sure why the spread is still 7 days, but knowing which version you are using will give me a start.

Re: Comparing today's data with last week's data

fpigeon — Thu, 17 May 2012 16:07:16 GMT

Hi, I tried both but the spread appears only on the second search. My basic understanding why I see the spread is because in the search I specify earliest=-7d@d latest=now even though the data returned is showed under one single day. What do you think?

Re: Comparing today's data with last week's data

mcbradford — Fri, 18 May 2012 12:28:12 GMT

I am trying to accomplish basically the same thing, but I cannot get it to work. I want a line chart with two lines. One showing a count of events for the past 24 hours, and another line showing a count of the past 24 hours from a week ago (same time period)

I want to base the search only on a source type and not a field. I just want to be able to have a visual representation of the total alerts from the two different time periods.

Re: Comparing today's data with last week's data

lguinn2 — Sat, 19 May 2012 06:39:48 GMT

I think you may be right - what happens if you add fixedrange to the timechart command as

timechart fixedrange=f avg(Time) as avgTime by ReportKey

Re: Comparing today's data with last week's data

lguinn2 — Mon, 28 Sep 2020 11:50:46 GMT

Okay, try this

sourcetype=yoursourcetype |
eval weekAgo = relative_time(now(), "-6d\@d") |
eval today = relative_time(now(), "\@d") |
eval ReportKey = "omit" |
eval ReportKey = case(_time < weekAgo,"Last Week", _time > today,"Today") |
where ReportKey != "omit" |
eval _time = if(ReportKey=="Last Week", _time+(60*60*24*7), _time) |
timechart fixedrange=f count by ReportKey

If that doesn't work for you, you should probably open another Question on the forum and include some additional details!