Splunk Search

Comparing today's data with last week's data

fpigeon
New Member

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!

Tags (2)
0 Karma

mcbradford
Contributor

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.

0 Karma

lguinn2
Legend

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!

0 Karma

lguinn2
Legend

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!

lguinn2
Legend

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

0 Karma

fpigeon
New Member

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?

0 Karma

lguinn2
Legend

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.

0 Karma

fpigeon
New Member

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.

0 Karma

lguinn2
Legend

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)

0 Karma

fpigeon
New Member

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.

0 Karma

kristian_kolb
Ultra Champion

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

/k

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!