Kibana Aggregation to get counts with Top Hit

I would like to create a metric that counts the number of hosts running different software versions.
The tricky part is that each host regularly report its current versions to the ES. The version field changed as the software get upgraded and get reported.

Here is the example of data. There are two hosts (host names are 414986NB001 and DESKTOP-2HF27UT) Both are running different released versions of software, and the first host reported two different versions because it got upgraded.

I would like to create a metrics of count of hosts vs the latest software versions they are running. i.e. we want to ignore the older history of records.

In this example, 0 count
8000.0.415.0 1 count 1 count

I tried different approaches from Visual but it didn't come up what I wanted. Please advise any approaches.
(I feel like I should start with querying the documents with filtering out all old records for each host first and then do the aggregations. However, I don't know how to do that from Discover either.)


Literally reading your sentences,

In this example, 0 count
8000.0.415.0 1 count 1 count

This sounds like standard terms aggregation on your version field.

For filtering out versions, if your versions field are keyword field then you have to specify the actual version in discover search bar .



However, this is not good if you have multiple versions to filter. So, you want to use numeric data type for version field. Since you want to use it in combination with above terms agg , one approach is you could try multi field.

Your mapping might be something like,

  "mappings": {
    "properties": {
      "vers": {
        "type": "keyword",
         "fields": {
            "major": {"type": "integer"},
            "minor": {"type": "integer"},
            "patch": {"type": "integer"}

Then in your discover or filter context in vis , you could do something like,

vers.major: [ 8000 TO *] AND vers.minor: [1 TO * ]

Of course you could create as separate field in same document to.

Don't forget to parse to corresponding fields in your pipeline program before indexing or ingest pipeline in elasticsearch

The latest software version a host is running would come from the latest timestamp for that host, right? If they downgrade a host to an earlier version and the earlier version would be the "latest"?

As far as I can tell, there is no way to do this in TSVB or Data tables. I think it requires an advanced pipeline aggregation. Perhaps this is possible with Vega, but that could get difficult.

Thanks for your reply and comments.
In your example, if some host downgraded the software version, the software would send another log to ES for the latest version (regardless it is downgrade/upgrade). That is why there are several entries of documents from the same host for reporting the software version.

Just thinking out loud, if I can somehow SEARCH for the latest version data entry log for each host, and somehow the set of filtered data is being used for aggregation, that the whole day become easy.

sample entry (3 parsed fields)
time stamp hostname version (in string)
Mon 1:00am host1 1.2 <--- same data entries older than this time filtered out from this hosts.
Fri 1:00pm host2 3.0. <--- older data entries from this host filtered.

Thanks for your reply. Actually, the concern is not about the nature of the version value.
it is just a plain string. Please see my other reply. Thanks again.