Tenable: How to create break down of patch availability

[lball]

I'm trying to create a search that will provide statistics for patch availability according to our Tenable scans. I'd like to make it all one table, but so far I'm creating separate searches and having trouble getting results. I want to breakdown the results by the patchPubDate value into "Less than 30 days old", 30 to 60 days old", & "over 60 days old". The individual searches that I've been working on so far look like this:

index=tenable | where patchPubDate < relative_time(now(),"-30d@d")
| stats count as "Patch Available less than 30 days"

index=tenable | where patchPubDate = relative_time("-30d@d","-60d@d")
| stats count as "Patch Available 30 to 60 days"

So far, I'm bombing on the second search and I'm not sure how to ensure that I only get patches over 60 days old. The patchPubDate is in epoch time format.

[whrg · Accepted solution]

Hello @lball,

What is the format of the patchPubDate field? Could you post a sample event?

I'm assuming patchPubDate is a string object similar to "2018-10-30". Your search fails because you are comparing a string object ("2018-10-30") to a number (relative_time(...) returns a unix time object which tells how many seconds have passed since 1 January 1970).

What you need to do is convert patchPubDate from a string object to a unix time object using strptime.

Try this:

index=tenable
| eval PatchPubDate_unixtime=strptime(PatchPubDate,"%Y-%m-%d")
| stats count(eval(PatchPubDate_unixtime>relative_time(now(),"-30d@d"))) AS "Patch Available less than 30 days" count(eval(PatchPubDate_unixtime<relative_time(now(),"-30d@d") AND PatchPubDate_unixtime>relative_time(now(),"-60d@d"))) AS "Patch Available 30 to 60 days"

You might need to adapt the time format (I guess YEAR-MONTH-DAY) for the strptime function.

[lball]

I attempted your suggestion without success. The "patchPubDate" is in Epoch Time format.

[whrg]

Then remove the eval line. Also I see I had one mistake that there was a < instead of a >.

Now when I search for

| makeresults count=1 | eval PatchPubDate=1543622400
| stats count(eval(PatchPubDate>relative_time(now(),"-30d@d"))) AS "Patch Available less than 30 days" count(eval(PatchPubDate<relative_time(now(),"-30d@d") AND PatchPubDate>relative_time(now(),"-60d@d"))) AS "Patch Available 30 to 60 days"

then I get:

Patch Available less than 30 days    Patch Available 30 to 60 days
1                                   0

(1543622400 is epoch time for 2018-12-01.)