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!

Thanks for the Memories! Splunk University, .conf24, and Community Connections

Thank you to everyone in the Splunk Community who joined us for .conf24 – starting with Splunk University and ...

.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 ...