Calculate vulnerabilty age by month with vulnerabilities that span over a month

[unitrium]

Hi,

I would like to create a graph showing the average vulnerability age for each month by severity. I use this search :

| tstats `summariesonly` min(_time) as firstTime,max(_time) as lastTime,count from datamodel=Vulnerabilities.Vulnerabilities by _time Vulnerabilities.signature,Vulnerabilities.dest, Vulnerabilities.severity span=1mon | `drop_dm_object_name("Vulnerabilities")` | where firstTime!=lastTime AND severity!="informational" | eval age=round((lastTime-firstTime)/86400) | eval _time=lastTime | timechart span=1mon avg(age) by severity | fields _time low medium high critical

However the age is calculated independently for each month. Meaning that if a vulnerability spans over multiple month its age will cap at 30 days maximum for each month in the graph. I'm unsure of how to make it cumulative 

[ITWhisperer]

Do you need the time element to the tstats?

| tstats `summariesonly` min(_time) as firstTime,max(_time) as lastTime,count from datamodel=Vulnerabilities.Vulnerabilities by  Vulnerabilities.signature,Vulnerabilities.dest, Vulnerabilities.severity | `drop_dm_object_name("Vulnerabilities")` | where firstTime!=lastTime AND severity!="informational" | eval age=round((lastTime-firstTime)/86400) | eval _time=lastTime | timechart span=1mon avg(age) by severity | fields _time low medium high critical

[unitrium]

I think it needed for the span=1mon so I can get the firstTime and lastTime for each month.

[ITWhisperer · Accepted solution]

So you still want it appearing every month (not just the last month) but to make it a running total? How about keeping the minimum firstTime and calculating age using that?

| tstats `summariesonly` min(_time) as firstTime,max(_time) as lastTime,count from datamodel=Vulnerabilities.Vulnerabilities by _time Vulnerabilities.signature,Vulnerabilities.dest, Vulnerabilities.severity span=1mon 
| eventstats min(firstTime) as firstTime by Vulnerabilities.signature,Vulnerabilities.dest, Vulnerabilities.severity
| `drop_dm_object_name("Vulnerabilities")` 
| where firstTime!=lastTime AND severity!="informational" 
| eval age=round((lastTime-firstTime)/86400) 
| eval _time=lastTime 
| timechart span=1mon avg(age) by severity 
| fields _time low medium high critical

[unitrium]

Seems to be closer to what I need, I'm just wondering what happens when a vulnerability is fixed, will it's average still be counted for the following months ?
If yes I was thinking to add an if clause in the eval age so that the age for the month should be 0 if lastTime!=_time
Would that work ?

[ITWhisperer]

I think the answer is no since the vulnerability won't show up for the month in the first tstats. You could check this in your results from just the tstats