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!
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.
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!
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!
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
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?
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.
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.
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)
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.
You might have a slight typo in the search - avg(Time)
, however Time
is never evaluated.
/k