Visualize / Search Date more than X days ago

csatola · April 12, 2021, 3:51pm

In my scenario, I'm logging server vulnerability data. @timestamp is when the document was added (and that's fine), but I also have a "PatchReleasedDate" field. What I would like to do is build visualizations for # of documents where the patch was released more than X days ago.

fields.PatchReleaseDate < now

Above works. Below doesn't.

fields.PatchReleaseDate < now-1d

Is date calculation possible in kibana queries? I did search and google around before posting and wasn't able to find anything.

wylie · April 12, 2021, 4:07pm

This is a common question. Usually, this isn't possible in Kibana because visualizations use a single query and it requires multiple queries to determine the duration in between two dates. This is why we offer the Transforms feature: Transforming data | Elasticsearch Guide [7.12] | Elastic

There might be a limited workaround for your specific use case. I'm assuming that you have an index pattern with @timestamp set as the "time field"- this is used by the time filter on all pages in Kibana. There are two exceptions to this:

TSVB lets you set the time field on every sub-visualization type
Lens automatically sets the time field to your date histogram time field, but you don't want a date histogram

So here I will recommend the steps to configure this in TSVB:

Open TSVB and go to the Metric visualization type.
Set the index pattern and choose PatchReleaseDate as your primary time field
Choose "Entire timerange mode" instead of "last value"
Set the time filter to contain the maximum number of days you are looking at, so for example you could set it to 90 days and you won't see anything before 90 days ago.
Type into the KQL filter bar PatchReleaseDate < "now-1d"
Save your TSVB visualization to your dashboard
If the overall time range of your dashboard is smaller than the time range you want to look at, you may need to set a per-panel custom time range. Go to Edit mode in the dashboard, and click the new panel. Choose a new time range.

csatola · April 12, 2021, 4:36pm

Thank you. This is very helpful. Unfortunately, I'm having issues with TSVB as a whole. The index has plenty of data in it in the last 24 hours (92k documents), but no matter what I do - including a filter of * gives me a count of zero. This cluster is an enterprise licensed cluster. Would you suggest I hit up support on this?

wylie · April 12, 2021, 4:53pm

If you can share a little bit more information about your TSVB setup, including a screenshot of the Data and Options tabs, I may be able to identify an issue. Version information helps too. Also if you have a sample document from the index in JSON form.

csatola · April 12, 2021, 7:26pm

Sure! I'm running 7.10.2. Here's an example document:

{
  "_index": "tenablestatslogger",
  "_type": "_doc",
  "_id": "lZF6xngBChZ1msVXqmHG",
  "_version": 1,
  "_score": null,
  "_source": {
"@timestamp": "2021-04-12T10:26:41.1480052-04:00",
"level": "Information",
"messageTemplate": "{Fqdn},{Ipv4},{Vulnerability},{RiskFactor},{HasPatch},{PatchReleaseDate}",
"message": "\"redacted\",\"redacted\",\"Security Updates for Microsoft Visual Studio Products (March 2020)\",\"High\",\"true\",03/10/2020 00:00:00",
"fields": {
  "Fqdn": "redacted",
  "Ipv4": "redacted",
  "Vulnerability": "Security Updates for Microsoft Visual Studio Products (March 2020)",
  "RiskFactor": "High",
  "HasPatch": "true",
  "PatchReleaseDate": "2020-03-10T00:00:00.0000000Z"
}
  },
  "fields": {
"@timestamp": [
  "2021-04-12T14:26:41.148Z"
],
"fields.PatchReleaseDate": [
  "2020-03-10T00:00:00.000Z"
]
  },
  "highlight": {
"fields.Vulnerability": [
  "Security Updates for Microsoft Visual Studio Products (@kibana-highlighted-field@March@/kibana-highlighted-field@ 2020)"
],
"message": [
  "\"rhays02\",\"10.151.250.68\",\"Security Updates for Microsoft Visual Studio Products (@kibana-highlighted-field@March@/kibana-highlighted-field@ 2020)\",\"High\",\"true\",03/10/2020 00:00:00"
]
  },
  "sort": [
1618237601148
  ]
}

Here's the screenshot:

Thanks!

wylie · April 12, 2021, 7:52pm

I have two theories here. The simplest is that you may have missed my instruction #3, which is to switch to "Data timerange mode > Entire time range", and #4, to set the time range to be very high.

The second is more of a question. Are you using the mapping of date_nanos for these date fields? It should be fully supported in your version, but since you are using this field as the main time field in TSVB maybe there is a bug.

csatola · April 14, 2021, 12:13pm

Thanks. I did read #3 in reverse. Thanks for catching that. I corrected, but that didn't help. Also slid the time range to 90 days but am still receiving 0 results.

Also, no, we're not using date_nanos. Just date

wylie · April 14, 2021, 6:28pm

@csatola thanks, it looks like your configuration is fine, but I see that you're still not getting the results that I would expect. I suspect that the query you're running is somehow not matching any documents, and that the 0 is displayed in your version instead of an empty indicator (I fixed this in more recent versions).

The next debugging step that I would recommend is temporarily enabling elasticsearch.logQueries: true in your kibana.yml or command line, restarting Kibana, and grabbing a copy of the query that TSVB sends. This will help you to understand if the issue is in TSVB or in your Elasticsearch setup.

I think having a support case for this might help, but I think it will get escalated to me or someone on my team anyway.

csatola · April 14, 2021, 7:08pm

@wylie At the risk of sounding completely dumb, using elasticsearch.logQueries: true with and without logging.verbose: true resulted in no actual queries hitting the logs when testing. Lots of web activity type logging, but no actual queries.

wylie · April 14, 2021, 7:34pm

@csatola Did you also restart Kibana after that? And I do think you'd need verbose logging on.

If that doesn't work, one thing to try would be using a different visualization editor that does have an inspect feature. Like the Metric visualization type.

stephenb · April 15, 2021, 12:39am

@csatola @wylie
Excuse me for butting in, Maybe I am missing something.

"fields.PatchReleaseDate": [
  "2020-03-10T00:00:00.000Z"
]

That is a March 2020 more than a year ago not this last March well beyond 90 days in your time picker....

Are you sure there is any recent data / patches.

You should be able to run a quick test... to see what the min / max dates are

GET  tenablestatslogger/_search
{
  "size": 0,
  "aggs": {
    "min_date": {
      "min": {
        "field": "fields.PatchReleaseDate",
        "format": "yyyy-MM-dd HH.mm.ss"
      }
    },
    "max_date": {
      "max": {
        "field": "fields.PatchReleaseDate",
        "format": "yyyy-MM-dd HH.mm.ss"
      }
    }
  }
}

warkolm · April 15, 2021, 3:53am

It's always a good idea, even if you post here

(You can always share your solution here as well, helping others!)

csatola · April 15, 2021, 12:27pm

@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

csatola · April 15, 2021, 12:47pm

I've figured out how to visualize my query.

Create a "Metric" visualization
Add a filter and click "Edit Query DSL"
Put the full query into the window and save.
Cheer because the resulting metric is accurate.

system · May 13, 2021, 12:48pm

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.