How to count events for specific time period now and 7 days earlier?

[aasiaa]

Hi, 

I am preparing dashboard panel where I want to show number of events for specific period (chosen by user) and for the same period but last week. I would like to show the difference in numbers between them. 

By googling I found almost what I need with also difference calculated in percentage, which looks perfect but that gives me results for the whole day not for the period chosen by user (eg. 60 mins, 4 hrs etc.)

My query now looks like :

<my search query> earliest=-0m@h latest=now
| stats count as Today
| appendcols [search<my search query> earliest=-7d@h latest=-7d@s
| stats count as LastWeek]
| eval perc=abs(round(((LastWeek-Today)/LastWeek)*100,2))

How can I adjust it so it searches for events for the timeframe specified by user ?

I tried the solution from here https://community.splunk.com/t5/Splunk-Search/Search-for-same-time-frame-on-the-same-day-of-differen... but it does not give me any results. 

Also tried to deduct 7d from _time for the earliest and latest , but this seems incorrect; 

I would appreciate any ideas, 

Thank you,

[gcusello]

Hi @aasiaa,

let me understand: you want todays count and sevend days ago count, is it corret?

in this case you have to use different time modifiers:

<my search query> earliest=@d latest=now
| stats count as Today
| appendcols [search<my search query> earliest=-7d@d latest=-6d@d
| stats count as LastWeek]
| eval perc=abs(round(((LastWeek-Today)/LastWeek)*100,2))

In addition I hint to explore the timewrap command (https://docs.splunk.com/Documentation/SCS/current/SearchReference/TimewrapCommandOverview) that gives you the same result.

Ciao.

Giuseppe

[aasiaa]

Hi @gcusello , 

Thank you so much for coming back to me.  I do not actually want results for the whole day, what I need are results for period chosen by the user (eg,. last 15 minutes, 4 hrs, 2 last days etc.) and results for the same period last week 

eg. user chose timeframe today 8.00 - 16.00 so I want to show results for that period and results for 8.00 - 16.00 same day last week. Or user chooses 2 last days, eg. Thursday and Friday and I want to show results for those 2 days plus same days last week. I hope it make sense 

[bowesmana]

@aasiaa  You don't need to use appendcols, it's good to avoid using subsearches where possible. You can do it with a single search. I assume you have a dashboard that the user selects the time period and you have a token for earlist/latest.

All you need to do is to create a background global search that calculates the currently selected time range (using addinfo) and then creates tokens for the 7 day time range period.

<search>
  <query>
| makeresults
| addinfo
| eval prev_earliest=info_min_time-(7 * 86400), 
       prev_latest  =info_max_time-(7 * 86400)
  </query>
  <earliest>$your_time_token.earliest$</earliest>
  <latest>$your_time_token.latest$</latest>
  <done>
    <set token="prev_earliest">$result.prev_earliest$</set>
    <set token="prev_latest">$result.prev_latest$</set>
  </done>
</search>

In your search you then do

<my search query>
  (earliest >= $your_time_token.earliest$ latest < $your_time_token.latest$) OR 
  (earliest >= $prev_earliest$ latest < $prev_latest$)

``` Calculate the counts condtionally by looking at time ```
| stats sum(eval(if(_time>$prev_latest$, 1, 0))) as Today
        sum(eval(if(_time<$prev_latest$, 1, 0))) as LastWeek
| eval perc=abs(round(((LastWeek-Today)/LastWeek)*100,2))

This uses a single search to find data from both time ranges - which have been calculated in the other search.

[aasiaa]

Hi @bowesmana , thank you very much. This looks like it's gonna work. Just one question where do I create global background search, shall I create separate panel for that ? from the query you posted looks like should be somewhere in the code; is it possible to add it via Splunk user interface ?

[bowesmana]

You can put that search into the XML in two ways. Edit the dashboard XML source and just paste that search section in the early part of the XML before any <row> statements - that makes it a global search that will run when the time picker is changed.

Or if you want to see the effect of that search and then hide it, you can add this row at the top of the dashboard, using the $ShowTable$ token that will hide the table by default.

<row depends="$ShowTable$">
  <panel>
    <table>
INSERT THE SEARCH HERE
    </table>
  </panel>
</row>

If you want to see the table, you can add &ShowTable=1 to the URL and the table will show you the effect of that search when it runs - useful to see how things work.

I often use this technique in more complex dashboards, as it allows me to debug searches when something does not do what I expect.

[aasiaa]

Actually I found another way to do it, not exactly 2 values and percentage but displays the data I want. Displays single value with a trend and shows me the difference in value for the same period seven days ago. 
I just cannot make it work in a new dashboard layout, in old one my search is 

my search query | timechart count partial=false span=1m

and then choose single value Visualization  then format -> General -> compared To and choose 7 days ago from drop down;

Just need to find out where compared To sits in the new layout;

Thanks @bowesmana and @gcusello for your help

[gcusello]

Hi @aasiaa ,

if one answer solves your need, please accept one answer for the other people of Community or tell us how we can help you.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors;-)

[aasiaa]

@gcusello I am happy to give karma points but I have not implemented any of the solutions. First one gave me results for the whole day and the second one is just too complex for me atm, I am not sure how to deal with  xml code in my dashboard. So what I came up with in the end is just 

my search| timechart span=1d partial=false count 

 with static time range for last seven days , it is single value visualisation with the trend and sparkline. It shows results for same and previous day plus daily results for the last seven days on the sparkline. 

Thank you very much for trying to help and all the solutions posted here; I am pretty sure both of them will be of use to other people trying to solve similar problem;