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

[kabiraj]

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/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

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.

[kabiraj · Accepted solution]

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

[woodcock]

Very well done! It does not handle ties but other than that, it is great!

[woodcock]

If you insert this, you will fix ties, too:

| eventstats max(position) AS position by count _time

[kabiraj]

Hi woodcock! Do you know any way to sort the above column names based on the dates mentioned in the column names e.g 30-May-2015 should come first followed by 31-May-2015 & 01-Jun-2015 and so on. Currently it's showing 01-Jun-2015 30-May-2015 31-May-2015 for my query. Its sorting automatically based on the day of the date mentioned in the column. I want it to sort based on the entire date and not just the day.

[kabiraj]

Thank You!

[woodcock]

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"

[woodcock]

Did you try this?

[kabiraj]

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

[woodcock]

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

[davebrooking]

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

[kabiraj]

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

[woodcock]

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

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

[kabiraj]

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.