topic Re: How to edit my search to display a timechart showing the percentage of OS usage per month over several months? in Splunk Search

How to edit my search to display a timechart showing the percentage of OS usage per month over several months?

michael_sleep — Tue, 29 Sep 2020 11:57:05 GMT

Hey there, I'm scratching my head trying to figure out how to do this.

Basically, I want to run a report on 6 months of browsing data and be able to view the percentage of usage on different operating systems (Windows 7, OS X, etc) per month over several months. It would hopefully look something like this:

OS             August   September   October
Windows 7       68%    70%        70%
Windows 10       3%     5%         6%
Linux           10%    8%          7%
Windows 8.1      9%     8%         8%
MacOS           4%      4%         4%
Windows XP       2%     2%         2%
iPad             1%     1%         1%
Chrome OS       0%      0%         0%
Android OS       0%     0%         0%
Windows Vista   1%      1%         1%
Windows 8       1%      1%         1%
iPhone         0%       0%         0%

The closest I can get is this:

index=marketing_summary_sp | timechart span=1mon count by ua_os_family limit=0

That basically gives me the "count" of individual OS sessions per month but not the percentages. The only fields I think that would be involved are date_month and ua_os_family. I just can't figure out how to give per month percentages while also showing several other months. Thoughts?

Re: How to edit my search to display a timechart showing the percentage of OS usage per month over several months?

cmerriman — Tue, 29 Nov 2016 16:23:55 GMT

index=marketing_summary_sp|eventstats count as total|bucket _time as month span=1mon|stats max(total) as total count by ua_os_family month|eval Percentage=round((count/total)*100,0)."%"|chart values(Percentage) by ua_os_family month

something like that might get you what you're looking for, or you could try with date_month

index=marketing_summary_sp|eventstats count as total|stats max(total) as total count by ua_os_family date_month|eval Percentage=round((count/total)*100,0)."%"|chart values(Percentage) by ua_os_family date_month

Re: How to edit my search to display a timechart showing the percentage of OS usage per month over several months?

michael_sleep — Tue, 29 Nov 2016 16:45:46 GMT

Neither of these work unfortunately because they give the percentage spread across all months, rather than the percentage per month. For example, I would expect Windows 7 usage to be around 70% each month but using the above searches over a period of 3 months shows the percentage at around 23% per month (so it is giving a percentage per month across all months, rather than per month).

Re: How to edit my search to display a timechart showing the percentage of OS usage per month over several months?

cmerriman — Tue, 29 Nov 2016 17:03:56 GMT

 index=marketing_summary_sp|bucket _time as month span=1mon|eventstats count as total by month|stats max(total) as total count by ua_os_family month|eval Percentage=round((count/total)*100,0)."%"|chart values(Percentage) by ua_os_family month

sorry, i should have added the eventstats to be by month. that might help. or something like this

 index=marketing_summary_sp|bucket _time as month span=1mon|stats count by ua_os_family month|eventstats sum(count) as total by month|eval Percentage=round((count/total)*100,0)."%"|chart values(Percentage) by ua_os_family month

Re: How to edit my search to display a timechart showing the percentage of OS usage per month over several months?

michael_sleep — Fri, 09 Dec 2016 19:41:45 GMT

This does appear to get the job done. Upvoted/accepted.