Splunk Search
Highlighted

Ranking rows in a Table based on count using Transpose & Timechart

Path Finder

I want something like below in the table.

Channel Name 25-Mar-15 26-Mar-15 27-Mar-15 28-Mar-15 29-Mar-15 30-Mar-15 31-Mar-15

BT Sports1 3 1 4 8 1 5 8
BT Sports2 2 2 2 9 10 7 9
Channel3 4 59 44 1 13 3 1
BT TV Preview 20 53 47 3 32 6 3
Animal Planet 9 28 21 2 12 8 2
Universal 5 23 22 4 11 4 4
Nat Geo Wild 10 28 23 16 3 2 16
Discovery 8 19 12 15 9 15 13 9
Nat Geo 39 40 35 34 36 33 34
History 3 9 5 6 2 15 6
GOLD 26 8 13 7 10 12 7
FOX 34 7 14 5 5 39 5
MTV 35 55 12 22 16 40 22
SyFy 14 11 12 4 19 12
Comedy Central 18 52 53 55 17 9 55
E! 43 6 6 10 47 10 10
Watch 19 5 7 9 8 1 9
Alibi 18 16 57 57 8 7 11 8
Boomerang 19 52 13 9 11 6 44 11
CI 20 19 4 8 59 9 13 59

The Numbers mentioned in the table are nothing but ranking based on the view count of each channel on the specified date. E.g. BT Sports1 is at 3rd postion on 25-Mar-15 and 1st position on 26-Mar-15 and these ranks are calculated based on number of views of BT Sports1 on 25-Mar-15 & 26-Mar-15.

My Query :
sourcetype=retsessions Severity="INFO" NOT source=/var/log/retchannelstats/RetStats18-9-201419-43.txt | bin time span=1d | convert ctime(time) timeformat="%d-%b-%y" | chart sum(Viewers) as Total over Channel by _time

Result from my query :
Channel 01-Jun-15 31-May-15
Alibi 17796 44990
Animal Planet 13782 31488
Animal Planet HD 1684 3731
BBC 2 HD 11317 45169

Here the numbers are the number of views but not the ranks. I want to show the ranks in place view count. Maximum count for a day will be ranked 1st and minimum will be ranked last. Can Streamstats be used here? Please reply.

0 Karma
Highlighted

Re: Ranking rows in a Table based on count using Transpose & Timechart

Esteemed Legend

sourcetype=retsessions Severity="INFO" NOT source=/var/log/retchannelstats/RetStats18-9-201419-43.txt | bin time span=1d | convert ctime(time) timeformat="%d-%b-%y" | chart sum(Viewers) as Total over Channel by _time

I can't see how to do more than 1 day at the same time, but here is how to do a single day (or I should say how to do all days, unless your timepicker is set to do just 1 day):

sourcetype=retsessions Severity="INFO" NOT source=/var/log/ret_channel_stats/RetStats_18-9-2014_19-43.txt |  chart count as Total by Channel| sort -Total | streamstats count as rank
0 Karma
Highlighted

Re: Ranking rows in a Table based on count using Transpose & Timechart

Path Finder

Hi woodcock. Appreciate your answer but i have already tried this.

http://answers.splunk.com/answers/241901/ranking-rows-in-a-table-based-on-the-count.html

My client specifically told to filter the ranks for each day so i can't show him for all days. Any other ideas will be highly entertained.

0 Karma
Highlighted

Re: Ranking rows in a Table based on count using Transpose & Timechart

Contributor

What version of Splunk are you using?

Sorry i don't have a Splunk instance to test with at the moment, but the pivot command gives you more levels of control on a visualisation than the chart command. Previously, I've used had to the use the pivot command instead of the chart command to add extra levels of visualisations that the chart command doesn't support.

Hopefully researching the pivot command gives you some ideas. If it doesn't can you post the search you're using, and the results you're seeing?

Dave

0 Karma
Highlighted

Re: Ranking rows in a Table based on count using Transpose & Timechart

Path Finder

I have already used pivot command but unfortunately pivot doesn't solve my issue. I have posted the search and the results. Please have a look

0 Karma
Highlighted

Re: Ranking rows in a Table based on count using Transpose & Timechart

Esteemed Legend

OK, this works, but it is not dynamic; it is an iterative approach that has to be hand-crafted (hard-coded and iterated out) for every date grouping. But it works. For your exact search (including timepicker value):

sourcetype=retsessions Severity="INFO" NOT source=/var/log/ret_channel_stats/RetStats_18-9-2014_19-43.txt | bin _time span=1d | convert ctime(_time) timeformat="%d-%b-%y" | chart sum(Viewers) as Total over Channel by _time
| sort 0 - "01-Jun-15" | transpose | rename row* AS * | rename column AS rank | transpose | where column!="rank" | rename column AS "01-Jun-15" "row 1" AS Channel "row 3" AS "31-May-15" | fields Channel "01-Jun-15" "31-May-15"
| sort 0 - "31-May-15" | transpose | rename row* AS * | rename column AS rank | transpose | where column!="rank" | rename column AS "31-May-15" "row 1" AS Channel "row 2" AS "01-Jun-15" | fields Channel "01-Jun-15" "31-May-15"
0 Karma
Highlighted

Re: Ranking rows in a Table based on count using Transpose & Timechart

Esteemed Legend

Did you try this?

0 Karma
Highlighted

Re: Ranking rows in a Table based on count using Transpose & Timechart

Path Finder

No, actually all the above are hardcoded values which i cannot use. I figured out another way to do this.

0 Karma
Highlighted

Re: Ranking rows in a Table based on count using Transpose & Timechart

Esteemed Legend

Do post your solution as an answer and then Accept it; I am curious how you solved it!

0 Karma
Highlighted

Re: Ranking rows in a Table based on count using Transpose & Timechart

Path Finder

Hi All,
I figured out below way to do this.

sourcetype=shmapplogs "getMS3SAS ended for - deviceId" | bucket span=1d _time | stats count by _time channelId | sort count desc | lookup youview_channels.csv service_id_truncated AS channelId OUTPUT channel_name_letter | streamstats count AS position by _time | fields channel_name_letter position _time | convert timeformat="%d-%b-%Y" ctime(_time) As Time | chart max(position) over channel_name_letter by Time

The above query is giving me a priority view per day

View solution in original post