tstats latest time for devices in a lookup

[jacqu3sy]

Hi,

I have the following query, for returning the last time a device contained in a lookup logged to splunk by the Device_IP, seen within the 'source' field.

|inputlookup test_sheet.csv | join type=outer Device_IP
[ | tstats latest(_time) as lt WHERE index=* earliest=-3d latest=now() [|inputlookup test_sheet.csv | strcat "/" + Device_IP + "/" as source | fields source | format ] by source, sourcetype
| rex field=source "/(?\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})/" ]

As we have some devices that log by multiple sourcetypes, I need to expand the above search to return the last time that Device_IP logged BY sourcetype, so that we know if we have stopped reciving logs for a specific sourcetype.

No matter how I adjust or tweak that query, I only seem to be able to get the latest(time) returned for whichever sourcetype logged last, not the latest(_time) for each sourcetype related to the IP.

Any ideas? Thanks.

[somesoni2 · Accepted solution]

It is because the join command by default just does one match (one Device_IP from lookup matches just one (first) sourcetype from the tstats query). Just include max=0 in your join command to get all.

[jacqu3sy]

great. thanks.

[JDukeSplunk]

Maybe something like?

| tstats latest(_time) as last where index=_internal host=splunkservername sourcetype=splunk* by host sourcetype | convert ctime(last)