How can I generate a trending analysis into the past?

[klawman]

I'm working with Qualys vulnerability data in splunk.

Qualys has an api call that runs once daily and collects any changes to the environment over the last day. Since splunk only receives a new event when a change is made, calculating TOTALS for vulnerabilities (QID) or impacted hosts (HOST_ID) really involves searching the entire event log.

Example: I want to a count of all systems currently affected by QID 100101 -

sourcetype=qualys_vm_detection HOSTVULN QID=100101 | dedup HOST_ID, QID | search STATUS!="FIXED" | stats count(HOST_ID) as #_affected

logic: I build the basic search criteria. I perform a DE DUPLICATION process on the HOST_ID and QID pairs to make sure I'm not over counting and I am looking at the most current host/vuln pair. I then run a check to exclude anything being reported as "FIXED" and I can the calculate the total number of hosts currently affected.

Question: How can I generate a trending chart of that total into the past?

The big issue is that HOSTVULN events only get added with a status change. If I do a typical time chart, I'm reporting only on the __changes_ made daily_ not the total numbers. If I were working foward, I could create a summary table but what if the request is "show me how the total number of hosts affected by xxxxxx went down over the last week/month."

What I really need to do is calculate that total, back up the 'latest' by a day, repeat the calculation and then iterate over a requested period. I realize this will take a LOT of time but I can't think of another approach given the nature of the data coming into splunk.

[aweitzman · Accepted solution]

I think you might want something like this:

sourcetype=qualys_vm_detection HOSTVULN QID=100101
| sort 0 _time
| eval IsItFixed=if(STATUS="FIXED","yes","no")
| dedup HOST_ID, IsItFixed
| streamstats count(eval(IsItFixed="no")) as NotFixed count(eval(IsItFixed="yes")) as Fixed 
| timechart span=1d last(NotFixed) as NotFixed last(Fixed) as Fixed
| eval StillNotFixed='NotFixed'-'Fixed'

(This might not be exactly right, but it should give you something to work with.)

The sort command just turns your events around so that they go forward in time.
The eval command rolls up all of the not-fixed statuses into the same value (I'm assuming there are different unfixed statuses).
The dedup command gives us the proper unique pairs of machine name and whether it is fixed or not.
The streamstats command adds a cumulative value to each event so that it contains the ongoing total of machines that are fixed and machines that were once unfixed.
The timechart command gives you the last cumulative value for each kind of machine on each day.
The last eval command then gives you the difference between the ones that have been fixed and the ones that haven't been.

The problem with your search as described above is that when you did your search STATUS!="FIXED", you lost all of the information when machines got fixed. Without that information, you can't keep a running total.

Hope this helps!