how to get host, sourcetype and source from a single |metadata search.

[Genti]

say i am running a search like this:

| metadata type=hosts | eval FirstSeen=firstTime | eval RecentSeen=recentTime | eval seconds_since=now()-recentTime | convert ctime(RecentSeen) | convert ctime(FirstSeen) | eval hours=floor(seconds_since/60/60) | eval minutes=floor((seconds_since-hours*60*60)/60) | eval seconds=seconds_since-hours*60*60-minutes*60 | fields host, source, sourcetype, FirstSeen, RecentSeen, hours, minutes, seconds

It's basically grabbing the info from the metadata file (hosts.data) and telling me when was the first time and last time a host send data my way. Then i am calculating how many seconds ago this was, and calculating it in hours, minutes and seconds.

Thats all dandy and works perfectly. Notice however that i would like to see the source and sourcetype for this last event that came from that host.
I know that i can run | metadata type=sourcetypes/sources and get similar information, but i have a feeling that i should be able to, perhaps using a subsearch?, get the source/sourcetype info some way..

For example, i can use a search like: "_time=<somenumber scrapped from recentTime> | table source, sourcetype" but am failing in passing the necessary data from one search to the other.

Also, is there a better way of getting "hours, minutes, and seconds" in my search above?
TIA
.gz

[koshyk]

bump.. (To see if any better ideas in 2015)

[sideview · Accepted solution]

I'm afraid that it is not possible using the metadata command. The data in the hosts.data file is not correlated with the sourcetypes.data file nor the sources.data file. Its a request that comes up every so often but I'm afraid the data is not there without searching the actual indexes.

So you'd have to do one search per host to get the sourcetypes or sources for that host. You could either search against the data itself or you could search against the metrics log.

This is a bit nutty but theoretically you could send the output of | metadata type=hosts to the map command. The map command is very rarely used and since it's implemented in python it can scale quite badly. However it provides just this kind of looping operation. Essentially you could get it to run one search per host and output the sourcetypes for that host.

The bad news is that even with the savings from the metadata command, the map command will probably slow things down so much that it would be more efficient and simpler to just do things like this:

* | stats max(_time) count by host, sourcetype