metadata: how to find the most recent event for each host in each index?

[robf]

i have 50 indexes and i want to find out the last most recent event for each host in each index.

i can do this for each index separately like so but there is no field "index"

| metadata type=hosts index=indexname1 | convert ctime(*Time) | table host recentTime 

i want to have a host and recent time for each index, but if a host has events in multiple indexes it should appear multiple times with the last event in each index.

Something like

| metadata type=hosts by index | convert ctime(*Time) | table host recentTime index

Is there anyway to do this and avoid have 50 separate scheduled searches?

[martin_mueller]

This should be much faster:

| tstats latest(_time) as _time where index=* by index host

[robf]

doesnt work on 4.3x tstats unknown....upgrade soon... thanks

[somesoni2 · Accepted solution]

Try this

|eventcount summarize=false index=*| map maxsearches=100 search="| metadata type=hosts index=$index$| convert ctime(*Time) | table host recentTime | eval index=\"$index$\""

[somesoni2]

You can add a dedup index before the map command. The maxsearches should be >= no of rows before the map (so yes, it should be no of indexes*indexers [if no dedup])

[robf]

actually this seems to be producing strage results.

this is a distributed architecture with 10+ indexers.

issues...
multiple results for a single host in the same index...
alot less results than when i run the search agaisnt indivdual indexes
should i add a dudup on the index eventcount?
how large should map maxsearches be? no. of indexes * indexers or?

[robf]

perfect thanks