When there are duplicate host names, how do you build a search that gets hosts' last activity?

[CMSchelin]

We have a bunch of hosts. Some of them are kind of like duplicates in that they are just the host name, and some are the FQDN. Example:

• Server012
• Server012.example.tld

I know how to use the metadata command to get a list of hosts and the last time they were seen. I also know how to strip out the domain part of the FQDN.

| metadata type=hosts
| rex field=host "^(?<hostname>.+)\.example.tld"
| table hostname, lastTime

The question is:

How can I collapse the duplicates and get the most recent most recent (sic) time?

Example data:

| host                  | lastTime   |
| Server012             | 1541663236 |
| Server012.example.tld | 1541689264 |

I want a query that would return:

| Server012 | 1541689264 |

Any insights?

[KailA · Accepted solution]

Hi @CMSchelin,

I think you can do something like that :

| metadata type=hosts
|rex field=host "^(?<hostname>.+)\.example.tld"
| table hostname, lastTime
| stats max(lastTime) as lastTime BY hostname

That will return you the most recent activity by host.

Let me know.
KailA 🙂

[CMSchelin]

Damn, all that typing for the question, and you answered it in like seconds. 🙂

That works perfectly, thank you!