Splunk Search

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

jwalzerpitt
Influencer

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

0 Karma

elliotproebstel
Champion

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="-" 

jwalzerpitt
Influencer

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

0 Karma

elliotproebstel
Champion

Glad to help. 🙂

0 Karma

woodcock
Esteemed Legend

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

jwalzerpitt
Influencer

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

0 Karma

woodcock
Esteemed Legend

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.

jwalzerpitt
Influencer

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

0 Karma

jwalzerpitt
Influencer

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

0 Karma

woodcock
Esteemed Legend

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 ?

0 Karma

jwalzerpitt
Influencer

I have and I'm waiting for his reply

Thx

0 Karma

elliotproebstel
Champion

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.

jwalzerpitt
Influencer

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

0 Karma

elliotproebstel
Champion

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.

jwalzerpitt
Influencer

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
0 Karma

elliotproebstel
Champion

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

jwalzerpitt
Influencer

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...