How to store two time-ranges events and compare?

[kakar]

Hi Splunkers, 

I have been using Splunk for a while and went through many proposed solutions in this community and found none to get what I want. This could be due to high volume of events I have for each month or I am doing something wrong.

So, the challenge I face is that I have a field lets call it "Pages" and I want to compare the last two months customers visiting the top 10 most visited pages. 

I have used below query, but the number of events for both months shows the same: (Note: the example below covers the previous day and last day to save time while searching)

Main search...
| addinfo 
| eval timephase1=if(_time>=relative_time(info_max_time, "-2d@d"), "last_month", null()), timephase2=if(_time>=relative_time(info_max_time, "-1d@d"), "this_month", null())
| stats count(timephase1) as time1 count(timephase2) as time2 by Pages
| sort -time1
| head 10

 Any assistance will be appreciated!

[PickleRick]

Firstly - time manipulation. You might get better results by binning the time to fixed intervals (hours, days, weeks) and aggregating the results later (will work for sum() or count(), will _not_ obviously work on stats like avg(); then you'd have to calculate it "around").

Secondly - if you have lots of data and Page is a search-time extracted field... well, it's gonna be slow. Summary indexing is here to help you. Just schedule a search which will populate your summary index regularly and you can do a quick search from that summary data.

[yuanliu]

Is it possible that this is because your search only compares two days instead of two months, and that in those two days (the day before yesterday and yesterday) the numbers didn't change?

Here is a simpler search.

<main search> earliest=-1mon@mon ``` begins at the start of last month ```
| addinfo
| eval last_month = if(info_max_time < relative_time(info_max_time, "-0mon@mon"), 1, null())
| stats sum(last_month) as last_month count(eval(isnull(last_month))) as this_month by date_minute
| sort - last_month this_month

[gcusello · Accepted solution]

Hi @kakar,

if your issue is that, having too many events, you have a very slow search, you could schedule your search every night taking the events of the previous day and then store these results in a Summary index.

Something like this:

Main search...
| addinfo 
| stats count by Pages
| collect index=your_summary_index

Then you can run your search on the summary index having very high performaces.

index=your_summary_index
| timechart span=1d sum(count) AS total BY Pages

or a different search as you like.

Ciao.

Giuseppe

[kakar]

Thanks @gcusello, I'll consider the scheduling option. but the issue I have is the number of records for both months comes exactly the same (As per above query).

I was thinking about timechat command, but in this I'll have two sets of top 10 pages name, one for March and another one for April, whereas if I use stats it will be one set of Pages name and each bar will compare the two months data as shown in my initial message above.

I am also trying to sort below data that should show the high volume first.

[gcusello]

Hi @kakar,

I see in your graph that you have the same bars with different values in both the months, are you sure that you should have different values in the two months?

run your search (without month differentiator) using as timeframe the first and then the second mont ìso you can compare results.

then I don't like your expression "-0mon@mon", to go before of one month you should use "-mon@mon".

Ciao.

Giuseppe