Getting Data In

How to create a month over month, year over year report on web analytics metrics data from CSV files?

jrcajide
New Member

Hi folks!
This is my first post here. I am new to Splunk although I have been intensively working with it for the last 8 weeks. I would appreciate some help for you to create a report. I have read dozens of posts, even the great Exploring Splunk book by D. Carasso but couldn't get exactly what I am trying to achieve.
I have daily events from csv files with some web analytics metrics. What I want is to generate a report in the following way:

month    users  usersMoM  usersYoY  sessions  sessionsMoM sessionYoY ...
201410  500 10%    -4%      25000     12%       -3% ...

Where MoM compares the metric towards the value from previous month (201409) and YoY towards the value from the same month from previous year (201310)
Is it possible?
Thanks for your help.

Tags (3)
0 Karma

wpreston
Motivator

Have you tried the timewrap app? It adds a new Splunk search command that was specifically created for time over time comparisons. You can get it here: https://apps.splunk.com/app/1645/. Give it a try and see if it works for you.

Muryoutaisuu
Communicator

Don't know why I forgot to bring up that exceptional tool:

timewrap: https://apps.splunk.com/app/1645/

download and install it and then make your search something like that:

YOURSEARCH earliest=-1year@month latest=@month
| timechart sum(num_users)
| timewrap m

The you also can make a fancy chart

wpreston
Motivator

You're too quick for me 🙂

0 Karma

Muryoutaisuu
Communicator

Got me 6 days to remember timewrap... 😉

0 Karma

Muryoutaisuu
Communicator

Hi. Assuming situation from your question:

current month = 201411

month = 201410

previous month = 201409

Try:

 YOURSEARCH -1month@month latest=@month | stats count users as users_month
| append [ YOURSEARCH earliest=-2month@month latest=-1month@month | stats count users as users_MoM ]
| append [ YOURSEARCH earliest=-1y@month-1month latest=-1y@month | stats count users as users_YoY
| eval users_MoM_perc=(users_month / users_MoM) users_YoY_perc=(users_month / users_YoY)
| table month,users_month AS users,users_MoM_perc,users_YoY_perc ...

If you have difficulties defining rigth time definition, click on the timepicker and go to Advanced. Beneath the expressions Splunk will show you the selected date.

Might have typos. Hope this helps nevertheless.

0 Karma

jrcajide
New Member

What about this approach?

searchstring  earliest=-12month@month latest=-1month@month
| bucket _time span=1mon
| stats sum(users) as users by _time
| eval Month = strftime(_time, "%b %Y")  | addinfo
| appendcols [ search searchstring
| eval earliest_time=relative_time(info_min_time,"-2m")
| eval latest_time=relative_time(info_max_time,"-1m")| where _time>earliest_time and _time<=latest_time| eval ReportKey="previousmonth"| timechart span=1mon sum(users) as prev_month_users  by previousmonth]

I think is the way to get previous month value for every month but relative_time functions or addinfo seems not to be working ...
Thank you again for your help.

0 Karma

jrcajide
New Member

Thank you Muryoutaisuu
I have been trying you approach but I can get what I'm trying.

* earliest=-2month@month latest=@month | eval month=strftime(_time,"%m") | stats sum(num_users) as users_month by month 
 | append [* earliest=-2month@month latest=-1month@month | stats sum(num_users) as users_MoM  | stats first(users_MoM) as prevoius_month  ] |table month users_month prevoius_month

This returns users_month correctly, and also calculate prevoius_month users but in a new row. Also It doesn't calculate prevoius_month users for previous months, only for the last one.

Any idea? Thanks so much

0 Karma

Muryoutaisuu
Communicator

Why are you searching in your first search earliest=-2month@month instead of -1month@month ? Your range now is two months.

In your second search: Is it really necessary to make the stats first(...) as ... ? I don't see the reason.

Quote: "Also It doesn't calculate prevoius_month users for previous months, only for the last one." I thought that was as intended? If you want your search to take a median, you need your subsearch to sum the users from something like append [ YOURSEARCH earliest=-13month@month latest=-1month@month | stats sum(num_users) AS users_MoM | eval previous_months=(users_MoM/12) ]

0 Karma

jrcajide
New Member

But, what I am trying is to get a dataset, a full year to data, each month in a row with a column that is a comparison with prevoius month...
Is that possible?
Thanks

0 Karma

Muryoutaisuu
Communicator

Don't know if you can do that generic. But surely you can do it absolutely:

YOURSEARCH earliest=-1month@month latest=@month |eval month_0=strftime(_time,"%m") | stats sum(num_users) as users_0 by month_0
| append [ YOURSEARCH earliest=-2month@month latest=-1month@month | eval month_1=strftime(_time,"%m") | stats sum(num_users) as users_1 by month_1]
| append ....
| eval users_1_0=(users_0/users_1) users_2_1=(users_1/users_2) ...
| table month_0,users_0,month_1,users_1_0, ...

not sure, if I got the code right. Might have typos or fallacies.

I think in your case aggregating values might be a good option.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...