@stephenb
Not butting in. Any ideas help. I used a bad example document to share. There is data with patch dates spanning weeks ago to years ago. However, the documents are indexed (@timestamp
) daily. The gist is these logs are the output of our vulnerability scanner. Checking machines for CVE vulnerabilities that exist. @timestamp
references when the scan discovered and logged that the vulnerability exists on a particular machine. PatchReleaseDate
represents when a fix for that CVE was released. I'm trying to query "Number of vulnerabilities where the patch has existed for X days" to ensure we're meeting our SLA of patching vulnerabilities within a certain period of time.
Here's the result of the test showing the range of dates in the result set:
{
"took" : 38,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 10000,
"relation" : "gte"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"max_date" : {
"value" : 1.61784E12,
"value_as_string" : "2021-04-08 00.00.00"
},
"min_date" : {
"value" : 1.0028448E12,
"value_as_string" : "2001-10-12 00.00.00"
}
}
}
This query:
GET tenablestatslogger/_search
{
"size": 0,
"query": {
"bool": {
"must": [
{ "match": {
"fields.HasPatch.keyword": "true"
}},
{"match": {
"fields.RiskFactor.keyword": "Critical"
}}
],
"filter": [
{"range": {
"fields.PatchReleaseDate": {
"lte": "now-60d"
}
}}
, {"range": {
"@timestamp": {
"gte": "now-36h"
}
}}
]
}
}
}
Gives me the results I want:
{
"took" : 1,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 256,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
}
}
I am looking to visualize the 256 number.
@wylie I'm going to test logging with the metric visualization today to validate that logging is working as expected.
Note: This is all sample data at the moment. These aren't our real stats