Splunk Search
Highlighted

How to calculate and display the daily, weekly, and monthly differences of a field?

Motivator

Hi,

I have the below search

index=mso_statistics sourcetype=ic_connectivity_5min-too_small  stat_name=subscribers |eval mso = upper(substr(mso,1,1)).lower(substr(mso,2)) |chart sparkline(max(stat_val)) as Trend max(stat_val) as "Subscribers" by mso|addcoltotals labelfield=Trend label=Total|eval Subscribers=tostring(Subscribers,"commas")|rename mso as "MSO - Click for Expanded View"

The dashboard panel that this search creates looks like this:alt text

I'd like to show the daily/weekly/monthly difference of the Subscribers field.

0 Karma
Highlighted

Re: How to calculate and display the daily, weekly, and monthly differences of a field?

Motivator

The data looks like this:

1/9/17
2:35:01.000 PM  
1483994101 twc res prod intelligenthome.timewarnercable.com connectivity subscribers 129041
date_hour = 20 date_mday =  9 date_minute = 35 date_month = january date_second =   1 date_wday =   monday date_year =  2017 date_zone =    0 eventtype =   external-referer    eventtype = visitor-type-referred host =    ic-mm-1465 index =  mso_statistics linecount =  1 punct =   ____..___ source =  /Users/dcostillow/Documents/icHealthCheck/graphite/ic_connectivity_5min.txt sourcetype =    ic_connectivity_5min-too_small splunk_server =  idx5.icontrol.splunkcloud.com timeendpos =  10 timestartpos =   0 unix_category =   all_hosts unix_group =  default
1/9/17
2:35:01.000 PM  
1483994101 rogerssmb smb prod smartbusiness.rogers.com connectivity subscribers 1647
date_hour = 20 date_mday =  9 date_minute = 35 date_month = january date_second =   1 date_wday =   monday date_year =  2017 date_zone =    0 eventtype =   external-referer    eventtype = visitor-type-referred host =    ic-mm-1465 index =  mso_statistics linecount =  1 punct =   ____..___ source =  /Users/dcostillow/Documents/icHealthCheck/graphite/ic_connectivity_5min.txt sourcetype =    ic_connectivity_5min-too_small splunk_server =  idx5.icontrol.splunkcloud.com timeendpos =  10 timestartpos =   0 unix_category =   all_hosts unix_group =  default
1/9/17
2:35:01.000 PM  
1483994101 rogers res prod smarthome.rogers.com connectivity subscribers 80160
date_hour = 20 date_mday =  9 date_minute = 35 date_month = january date_second =   1 date_wday =   monday date_year =  2017 date_zone =    0 eventtype =   external-referer    eventtype = visitor-type-referred host =    ic-mm-1465 index =  mso_statistics linecount =  1 punct =   ____..___ source =  /Users/dcostillow/Documents/icHealthCheck/graphite/ic_connectivity_5min.txt sourcetype =    ic_connectivity_5min-too_small splunk_server =  idx5.icontrol.splunkcloud.com timeendpos =  10 timestartpos =   0 unix_category =   all_hosts unix_group =  default
1/9/17
2:35:01.000 PM  
1483994101 iTSCOMTouchstone res prod portal.intelligent-home.jp connectivity subscribers 4070
date_hour = 20 date_mday =  9 date_minute = 35 date_month = january date_second =   1 date_wday =   monday date_year =  2017 date_zone =    0 eventtype =   external-referer    eventtype = visitor-type-referred host =    ic-mm-1465 index =  mso_statistics linecount =  1 punct =   ____.-.___ source = /Users/dcostillow/Documents/icHealthCheck/graphite/ic_connectivity_5min.txt sourcetype =    ic_connectivity_5min-too_small splunk_server =  idx5.icontrol.splunkcloud.com timeendpos =  10 timestartpos =   0 unix_category =   all_hosts unix_group =  default
1/9/17
2:35:01.000 PM  
1483994101 peqTouchstone res prod manage.mypeq.com connectivity subscribers 301
date_hour = 20 date_mday =  9 date_minute = 35 date_month = january date_second =   1 date_wday =   monday date_year =  2017 date_zone =    0 eventtype =   external-referer    eventtype = visitor-type-referred host =    ic-mm-1465 index =  mso_statistics linecount =  1 punct =   ____..___ source =  /Users/dcostillow/Documents/icHealthCheck/graphite/ic_connectivity_5min.txt sourcetype =    ic_connectivity_5min-too_small splunk_server =  idx5.icontrol.splunkcloud.com timeendpos =  10 timestartpos =   0 unix_category =   all_hosts unix_group =  default
1/9/17
2:35:01.000 PM  
1483994101 comcastTouchstoneB res prod xhpodbportal-po-09.sys.comcast.net connectivity subscribers 34
date_hour = 20 date_mday =  9 date_minute = 35 date_month = january date_second =   1 date_wday =   monday date_year =  2017 date_zone =    0 eventtype =   external-referer    eventtype = visitor-type-referred host =    ic-mm-1465 index =  mso_statistics linecount =  1 punct =   ____--...___ source =   /Users/dcostillow/Documents/icHealthCheck/graphite/ic_connectivity_5min.txt sourcetype =    ic_connectivity_5min-too_small splunk_server =  idx5.icontrol.splunkcloud.com timeendpos =  10 timestartpos =   0 unix_category =   all_hosts unix_group =  default
1/9/17
2:35:01.000 PM  
1483994101 comcastTouchstone res prod appadmin.xfinityhomesecurity.com connectivity subscribers 22700
date_hour = 20 date_mday =  9 date_minute = 35 date_month = january date_second =   1 date_wday =   monday date_year =  2017 date_zone =    0 eventtype =   external-referer    eventtype = visitor-type-referred host =    ic-mm-1465 index =  mso_statistics linecount =  1 punct =   ____..___ source =  /Users/dcostillow/Documents/icHealthCheck/graphite/ic_connectivity_5min.txt sourcetype =    ic_connectivity_5min-too_small splunk_server =  idx3.icontrol.splunkcloud.com timeendpos =  10 timestartpos =   0 unix_category =   all_hosts unix_group =  default
1/9/17
2:35:01.000 PM  
1483994101 comcastB res prod xhpodbportal-po-09.sys.comcast.net connectivity subscribers 238966
date_hour = 20 date_mday =  9 date_minute = 35 date_month = january date_second =   1 date_wday =   monday date_year =  2017 date_zone =    0 eventtype =   external-referer    eventtype = visitor-type-referred host =    ic-mm-1465 index =  mso_statistics linecount =  1 punct =   ____--...___ source =   /Users/dcostillow/Documents/icHealthCheck/graphite/ic_connectivity_5min.txt sourcetype =    ic_connectivity_5min-too_small splunk_server =  idx3.icontrol.splunkcloud.com timeendpos =  10 timestartpos =   0 unix_category =   all_hosts unix_group =  default
1/9/17
2:35:01.000 PM  
1483994101 comcast res prod appadmin.xfinityhomesecurity.com connectivity subscribers 662215
date_hour = 20 date_mday =  9 date_minute = 35 date_month = january date_second =   1 date_wday =   monday date_year =  2017 date_zone =    0 eventtype =   external-referer    eventtype = visitor-type-referred host =    ic-mm-1465 index =  mso_statistics linecount =  1 punct =   ____..___ source =  /Users/dcostillow/Documents/icHealthCheck/graphite/ic_connectivity_5min.txt sourcetype =    ic_connectivity_5min-too_small splunk_server =  idx3.icontrol.splunkcloud.com timeendpos =  10 timestartpos =   0 unix_category =   all_hosts unix_group =  default
1/9/17
2:35:01.000 PM  
0 Karma
Highlighted

Re: How to calculate and display the daily, weekly, and monthly differences of a field?

SplunkTrust
SplunkTrust

I'm dubious about using "max" to calculate the far right "Subscribers" column. As an example of the problem, for Bhn, it would seem that 22,315 Subscribers number would be for beginning of the trend line, rather than the current point on it.

The reason this is important to your question is because any deltas that you would want to display would have to have accurate numbers for the subscribers as-of the particular time frame. First you need that for Now, then you need that for a week ago, and so on.

So, to fix THAT problem, I would suggest you replace "max" with "latest" for Subscribers. You might also consider using "avg" for the sparkline, although "max" wouldn't be a terrible representation of what is going on there.

0 Karma
Highlighted

Re: How to calculate and display the daily, weekly, and monthly differences of a field?

SplunkTrust
SplunkTrust

The sparkline should still be showing today/thisweek/thismonth data?

0 Karma
Highlighted

Re: How to calculate and display the daily, weekly, and monthly differences of a field?

Motivator

if I can get this data the sparkline would be discarded

0 Karma
Highlighted

Re: How to calculate and display the daily, weekly, and monthly differences of a field?

SplunkTrust
SplunkTrust

Great what would the columns in the expected output? What you'll be comparing at a time, (today vs yesterday OR today vs sametime last week or month)?

0 Karma
Highlighted

Re: How to calculate and display the daily, weekly, and monthly differences of a field?

Motivator

Ideally it would be the delta between today and yesterday, last week and this week, last month and this month.

Good question on the time... hadn't thought about that. Lets just pick a time and I'll adjust it once I find out what is a good time.

0 Karma
Highlighted

Re: How to calculate and display the daily, weekly, and monthly differences of a field?

SplunkTrust
SplunkTrust

The sparkline would be showing the max stat_value for each segment of time. Max, avg, or latest spark lines will all look more-or-less the same on that. However, the user is going to think the far right column represent the current value, but it is really showing the max of all the data being shown.. the highest point the subscriber count got anywhere on that trend line.

0 Karma
Highlighted

Re: How to calculate and display the daily, weekly, and monthly differences of a field?

Motivator

That is correct. The user is aware that the value is the highest value not necessarily the current value. Confusing I know but it works for the audience 🙂

0 Karma
Highlighted

Re: How to calculate and display the daily, weekly, and monthly differences of a field?

SplunkTrust
SplunkTrust

Give this a try

For Today vs Yesterday

index=mso_statistics sourcetype=ic_connectivity_5min-too_small  stat_name=subscribers earliest=-1d@d|eval mso = upper(substr(mso,1,1)).lower(substr(mso,2)) | eval Period=if(_time>=relative_time(now(),"@d"),"Today","Yesterday") |chart max(stat_val) over mso by Period |addcoltotals labelfield=Trend label=Total 
| eval Difference=Today-Yesterday | foreach Today Yesterday Difference [eval "<<FIELD>>"=tostring('<<FIELD>>',"commas")] 
|rename mso as "MSO - Click for Expanded View"

For This Week vs Last Week

index=mso_statistics sourcetype=ic_connectivity_5min-too_small  stat_name=subscribers earliest=-1w@w|eval mso = upper(substr(mso,1,1)).lower(substr(mso,2)) | eval Period=if(_time>=relative_time(now(),"@w"),"ThisWeek","LastWeek") |chart max(stat_val) over mso by Period |addcoltotals labelfield=Trend label=Total 
| eval Difference=ThisWeek-LastWeek| foreach ThisWeek LastWeek Difference [eval "<<FIELD>>"=tostring('<<FIELD>>',"commas")] 
|rename mso as "MSO - Click for Expanded View"

To similar change (earliest, eval Period, column names after chart) for monthly comparison.

View solution in original post

0 Karma