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
Revered Legend

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
Revered Legend

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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...