topic Re: How can I create a ranking based on a count per week by a field in Splunk Search

How can I create a ranking based on a count per week by a field

jwalzerpitt — Thu, 07 Dec 2017 13:53:20 GMT

I am looking to do the following:

1) Create a table based on a count of blocks by week number and zone

I have the following search for that:

index="foo" sourcetype=foo
| ip_cidr.csv cidr_range as src
| eval firewall=coalesce(firewall,"null")
| eval context=coalesce(context,"null")
| eval zone=coalesce(zone,"null")
| eval week_month=strftime(_time, "%V") 
| bucket span=7d _time
| stats count by week_month,zone

2) Assign a rank for each zone by sorting from highest count to lowest with 1 being assigned to the zone with the highest count, 2 assigned to the zone with the second highest count, etc., i.e.:

Week 43 Rank
    Zone 1 - 5
    Zone 2-  9
    Zone 3 - 1
    Zone 4 - 7

Week 44 Rank
    Zone 1 - 15
    Zone 2-  2
    Zone 3 - 11
    Zone 4 - 3

I'd like to see the rank for each zone on a weekly basis

Then I'd like to compute the average rank of each zone based on their rankings per week, like so:

Average Rank
    Zone 1 - 3
    Zone 2-  21
    Zone 3 - 10
    Zone 4 - 1

Thx

Re: How can I create a ranking based on a count per week by a field

elliotproebstel — Thu, 07 Dec 2017 15:51:19 GMT

A couple of questions -
In your base query, you do this:

 | eval firewall=coalesce(firewall,"null")
 | eval context=coalesce(context,"null")
 | eval zone=coalesce(zone,"null")

But then you never use those fields. Is the "rank" intended to be a sum of the instances where firewall OR contextOR zone are not null?

For the second part of your request, do you want all weeks in the data to be listed, or do you want to be able to specify a week number (passed in with a token, presumably), and see the data for that week? I have a good sense of how to do the latter, not so much for the former.

Re: How can I create a ranking based on a count per week by a field

jwalzerpitt — Thu, 07 Dec 2017 16:00:12 GMT

I want to use zone (as it's more specific than firewall or context, but I have those broken out in case I need to use them), and I'd like to have all weeks in the data be listed (as I can see from week to week at a glance where each zone is ranked).

However, it would be great to implement being able to specify a week number passed in a token to see data for that week as well.

Thx

Re: How can I create a ranking based on a count per week by a field

woodcock — Thu, 07 Dec 2017 16:24:02 GMT

Add this to your existing search:

| sort 0 - count
| streamstats count AS rank BY week_month zone
| eventstats first(rank) AS rank BY count week_month zone
| stats avg(rank) BY zone

Re: How can I create a ranking based on a count per week by a field

jwalzerpitt — Thu, 07 Dec 2017 17:11:13 GMT

Thx for the reply and the search. A few quick questions

1) Is it possible to create an inverse so that the lowest avg ranking (avg(rank)) is listed as 1, second lowest average is listed as 2, etc.?

2) Is it possible to see the weekly data for each zone?

3) How hard would it be to compare a zone’s week against it’s previous week to see what the % change is (either up or down?) So for Zone 1 I’d like to compare week 2 to week 1 to see what the % change is, then week 3 to week 2, and so on. Each zone would have a % comparison allowing me to see the % up or down for each zone.

Thx again

Re: How can I create a ranking based on a count per week by a field

woodcock — Thu, 07 Dec 2017 18:11:44 GMT

1) Remove the - in the sort.
2) Change the last stats to eventstats.
3) Check out the delta and autoregress commands.

Do click accept to close the question if the answer worked.

Re: How can I create a ranking based on a count per week by a field

jwalzerpitt — Thu, 07 Dec 2017 18:58:09 GMT

Thx - I did click accept as the answer worked

1) I did remove the - in the sort as below, but I'm not seeing the inverse

| sort 0 count
| streamstats count AS rank BY week_month zone
| eventstats first(rank) AS rank BY count week_month zone
| stats avg(rank) BY zone

is the avg(rank) based on the count using streamstats/eventstats? I dumped seven days of data into Excel and mocked up what I'm looking for and I'm seeing a different outcome re: rank/avg rank

    11/30/2017  12/1/2017   12/2/2017   12/3/2017   12/4/2017   12/5/2017   12/6/2017   12/7/2017   
Zone 1  0   44  86685   31  46  13  25  13  
Zone 2  2   511 457 457 509 562 509 509 
Zone 3  0   4263    803 803 3803    837 805 803 
Zone 4  98  543 719 272 552 751 1341    769 
Zone 5  3648    2428    1856    1331    3804    3439    2968    250 
Zone 6  169 165 165 165 141 179 528 14  
Zone 7  254 222 174 154 250 248 224 0   
Zone 8  340 395 320 130 435 450 360 0   
Zone 9  292 801 401 360 505 451 481 434 
Zone 10 172 129 59  59  502 214 186 215 
Zone 11 118 157 354 189 158 189 232 40  

Rank:                                   
    Day 1   Day 2   Day 3   Day 4   Day 5   Day 6   Day 7       Avg Ranking
Zone 1  10  11  1   11  11  11  11      9.4
Zone 2  9   5   5   3   4   4   5       5.0
Zone 3  10  1   3   2   2   2   3       3.3
Zone 4  8   4   4   5   3   3   2       4.1
Zone 5  1   2   2   1   1   1   1       1.3
Zone 6  6   8   10  7   10  10  4       7.9
Zone 7  4   7   9   8   8   7   9       7.4
Zone 8  2   6   8   9   7   6   7       6.4
Zone 9  3   3   6   4   5   5   6       4.6
Zone 10 5   10  11  10  6   8   10      8.6
Zone 11 7   9   7   6   9   9   8       7.9

Thx

Re: How can I create a ranking based on a count per week by a field

jwalzerpitt — Thu, 07 Dec 2017 19:04:04 GMT

Also, dumping out to a table in Splunk, I'm seeing the same zone listed multiple times per week, whereas I would think that each zone should only be listed once. Perhaps there's an issue with my command | eval week_month=strftime(_time, "%V")?

Thx

Re: How can I create a ranking based on a count per week by a field

woodcock — Thu, 07 Dec 2017 19:17:56 GMT

I did not assess your original search in any way and it appears that it is defective. Have you addressed all of the concerns by @elliotproebstel ?

Re: How can I create a ranking based on a count per week by a field

jwalzerpitt — Thu, 07 Dec 2017 19:19:46 GMT

I have and I'm waiting for his reply

Thx

Re: How can I create a ranking based on a count per week by a field

elliotproebstel — Thu, 07 Dec 2017 20:37:34 GMT

The following is meant as an intermediate step to see if we are getting things right. It's a little tough, since I don't have your source data to test on...

your base search...
| sort - week_month count 
| streamstats count AS rank BY week_month 
| where rank<=5

I expect that to return the zones ranked 1-5 for each week, listed in order by week. Let me know if that part works.

Re: How can I create a ranking based on a count per week by a field

jwalzerpitt — Thu, 07 Dec 2017 21:06:21 GMT

My search is now set as below and returns the zones ranked 1-5 for each week, listed in order by week (I had a typo in which I didn't the lookup command for the second line).

index="foo" sourcetype=foo host="foo
 | lookup ip_cidr.csv cidr_range as src
 | eval firewall=coalesce(firewall,"null")
 | eval context=coalesce(context,"null")
 | eval zone=coalesce(zone,"null")
 | eval week_month=strftime(_time, "%V") 
 | bucket span=7d _time
 | stats count by week_month,zone
 | sort - week_month count 
 | streamstats count AS rank BY week_month 
 | where rank<=5

Re: How can I create a ranking based on a count per week by a field

elliotproebstel — Thu, 07 Dec 2017 21:14:51 GMT

Great - so now I have a question about the tables you're seeking. In this snippet:

 Week 43 Rank
     Zone 1 - 5
     Zone 2-  9
     Zone 3 - 1
     Zone 4 - 7

Is Zone 1 the name of one of your zones, and 5 is the ranking for that zone for that week?
Or are you saying that in Week 43, the #1 ranked zone is named 5? At first, I was thinking you meant the latter, but now I'm thinking you meant the first interpretation. If the first, then I think this snippet will meet your goal for #2:

index="foo" sourcetype=foo host="foo
| lookup ip_cidr.csv cidr_range as src
| eval firewall=coalesce(firewall,"null")
| eval context=coalesce(context,"null")
| eval zone=coalesce(zone,"null")
| eval week_month=strftime(_time, "%V") 
| bucket span=7d _time
| stats count by week_month,zone
| sort - week_month count 
| streamstats count AS rank BY week_month 
| fields - count 
| sort week_month zone

Re: How can I create a ranking based on a count per week by a field

jwalzerpitt — Tue, 29 Sep 2020 17:10:25 GMT

Using the search below (after the last eval line), I see a table of week_month, zone, count, rank, with the sort order being week_month and rank (and obviously count is sorted as well from highest to smallest)

| stats count by week_month,zone
| sort - week_month count 
| streamstats count AS rank BY week_month
| sort week_month rank

Thx

Re: How can I create a ranking based on a count per week by a field

elliotproebstel — Thu, 07 Dec 2017 21:27:59 GMT

Cool, so are you all set? If not, the following snippet should give you the first of the excel-mocked tables you mentioned below:

[ your base search ]
| sort - week_month count 
| streamstats count AS rank BY week_month 
| fields - count 
| sort week_month zone 
| eval week_month="Week ".week_month 
|xyseries zone week_month rank 
| fillnull value="-"

Re: How can I create a ranking based on a count per week by a field

jwalzerpitt — Thu, 07 Dec 2017 21:32:28 GMT

Good to go! Thx again and I greatly appreciate all of the help in hammering this out to what I was looking for.

Re: How can I create a ranking based on a count per week by a field

elliotproebstel — Thu, 07 Dec 2017 21:33:45 GMT

Glad to help. 🙂

Re: How can I create a ranking based on a count per week by a field

woodcock — Thu, 07 Dec 2017 22:21:59 GMT

If you fixed the first part then my solution should work now.