How to view my VulnerabilityTitle count deltas over time with ranking

[jlovik]

Ok so bear with me as I explain. I would like to view my VulnerabilityTitle count deltas over time.

So for instance, if I ran count by VulnerabilityTitle yesterday to get my top 10 most common vulnerabilities in my environment that is great. however, I need to be able to do that day after day using a time range.
So, for instance, my top most common VulnerabilityTitle may drop down to my second most common VulnerabilityTitle after patching over the weekend.
So my end goal is to be able to pick a time window and get VulnerabilityTitle counts Ranked for all the days in the time window I pick. That way I can see x vulnerability was rank 1 yesterday and is now rank 2 today.

I am currently running this the search below which gives me most of what I want but it does not track the ranking over time. This ideally would not be only viewable in line graph format.

| eval import_time=strftime(_time, "%Y-%m-%d:%H")
| eval import_timeday=strftime(_time, "%Y-%m-%d")
| eventstats latest(import_time) as Last by import_timeday
| where Last = import_time
| timechart span=1d count by VulnerabilityTitle where max in top10 useother=f

[to4kawa · Accepted solution]

| eval import_time=strftime(_time, "%Y-%m-%d:%H")
| eval import_timeday=strftime(_time, "%Y-%m-%d")
| eventstats latest(import_time) as Last by import_timeday
| where Last = import_time
| stats count as Count by import_timeday VulnerabilityTitle
| sort 0 - Count
| streamstats count as tops by import_timeday
| where tops < 11

Hi @jlovik
there is no details. this is quiz, I guess. please provide correct answer and result.

[jlovik]

@magneto417x This is not a quiz. It's a real world problem im working on. i am just very new to splunk. So if i wanted to rank these over time in a timechart (i know i previously asked for the opposite) how would i do that? This seems like it should work to me.

| eval import_time=strftime(_time, "%Y-%m-%d:%H")
| eval import_timeday=strftime(_time, "%Y-%m-%d")
| eventstats latest(import_time) as Last by import_timeday
| where Last = import_time
| stats count as Count by import_timeday VulnerabilityTitle
| sort 0 - Count
| streamstats count as tops by import_timeday
| timechart span=1d count by VulnerabilityTitle where tops < 11

[to4kawa]

hi @jlovik

For me, this is Quiz. there is no detail, no log, no sample, no result. I know nothing.
Can you create an SPL in this situation?

| timechart span=1d count by VulnerabilityTitle where tops < 11
Can this work?

my query result:

import_timeday, VulnerabilityTitle, Count, tops
2020-05-01, XXX, 100, 1
2020-05-01, YYY, 50, 2
....

Each import_timeday , VulnerabilityTitle should be in the top 10 in order of Count.
You don't need timechart.

[jlovik]

your search works perfectly from above. i get the below all neatly aligned. it just doesn't seem to let me create a chart out of those fields after the | streamstats command. I would like to chart the tops 1-10 for each import_timeday for my search range in a line chart.

timeday, VulnerabilityTitle, Count tops,
2020-05-04, Weak LAN, 1
2020-05-02, VM Ware, 1
2020-05-05, tls, 1
2020-05-01, micro, 1

[to4kawa]

what's x-axis and y-axis ?

maybe:

...
| fields - Count
| xyseries import_timeday, VulnerabilityTitle, tops
| sort 0 import_timeday

Viz >> Line Chart

[jlovik]

you are magic. thank you!