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
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="-"
Good to go! Thx again and I greatly appreciate all of the help in hammering this out to what I was looking for.
Glad to help. 🙂
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
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
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.
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
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
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 ?
I have and I'm waiting for his reply
Thx
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 context
OR 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.
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
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.
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
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
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
If you fixed the first part then my solution should work now.