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.
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.
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
You're too quick for me 🙂
Got me 6 days to remember timewrap... 😉
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.
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.
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
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) ]
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
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.