How do I get a case insensitive host answer from the metadata command?

[HMTODD]

When I use metadata type=hosts I get data for host names that are all uppercase and data for host names that are all lowercase. I have not found a way combine the data.

My objective is to get the real and most recent value for lastTime for a host - not a value for the uppercase host name and another value for the lowercase version of the host name. Here are examples.

| metadata type=hosts index=*  | search host=*syslog* |  table host recentTime lastTime | sort -recentTime | eval recentTime = strftime(recentTime, "%x %X") | eval lastTime = strftime(lastTime, "%x %X")
host    recentTime  lastTime
NLGPHQVSYSLOG2  01/25/18 16:02:49   01/25/18 16:02:47
NLGPPTVSYSLOG1  01/25/18 16:02:27   01/25/18 16:02:26
nlgphqvsyslog2  01/25/18 15:45:31   01/24/18 04:00:18
nlgpptvsyslog1  01/25/18 12:33:00   01/23/18 03:59:56

If I add | eval host=lower(host) I get this

host    recentTime  lastTime
nlgpptvsyslog1  01/25/18 16:08:47   01/25/18 16:08:45
nlgphqvsyslog2  01/25/18 16:08:22   01/25/18 16:08:17
nlgphqvsyslog2  01/25/18 15:45:31   01/24/18 04:00:18
nlgpptvsyslog1  01/25/18 12:33:00   01/23/18 03:59:56

So I thought I would try to use latest(lastTime) by host. The values become blank. It looks like the latest function cannot work on the metadata events.

| metadata type=hosts index=*  | search host=*syslog* | eval host=lower(host) | stats latest(recentTime), latest(lastTime) by host | table host recentTime lastTime | sort -recentTime | eval recentTime = strftime(recentTime, "%x %X") | eval lastTime = strftime(lastTime, "%x %X")

host    recentTime  lastTime
nlgphqvsyslog2       
nlgpptvsyslog1

[elliotproebstel]

Instead of doing latest(lastTime), you should be able to do max(lastTime) before you use strftime on the field. When working with an epoch timestamp, max() should be functionally equivalent to latest(). Conversely, the equivalent of earliest() would be min().

[somesoni2 · Accepted solution]

Try like this

| metadata type=hosts index=*  | search host=*syslog* |  table host recentTime lastTime | eval host=lower(host) | stats max(recentTime) as recentTime max(lastTime) as lastTime by host | sort -recentTime | convert ctime(recentTime) ctime(lastTime) timeformat="%x %X"