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!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...