Splunk Enterprise Security

tstats latest time for devices in a lookup

jacqu3sy
Path Finder

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.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

jacqu3sy
Path Finder

great. thanks.

0 Karma

JDukeSplunk
Builder

Maybe something like?

| tstats latest(_time) as last where index=_internal host=splunkservername sourcetype=splunk* by host sourcetype | convert ctime(last)
0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...