Splunk Search

Two queries with different timeframes using join. Is there a more efficient way?

agdavidson
New Member

Hi there. I am new to SPL and wondering how to make a particular query more efficient. In particular, I want to create a table with a list of all hosts that have had an event with a given timeframe, let's say within the last 7 days. I also want to display the date when I last saw the host and the first time the system ever saw the host -- which is data that is prior to the previous 7 day timeframe.

In order to accomplish, I am running two separate queries with a join. The first query uses the settings from the time selector, the second query overrides the time selector and uses "all time" in order to obtain the first and last timestamps for each host.

Basically, for every host that has had an event in the last 7 days, I want to create a table that shows:
host | os | First event ever sent to Splunk | Last event sent to Splunk | License Expiration

I am currently using the following query, which does accomplish what I want but it takes quite a while to run:

index=main | dedup machineID host |
join type=inner host [search
earliest=0 latest=now index=main |
stats min(_indextime) as "First Seen"
max(_indextime) as "Last Seen" by host
| convert ctime("First Seen")
ctime("Last Seen") | fields host
"First Seen" "Last Seen"] | table host
os "First Seen" "Last Seen"
expiration| sort by host

Is there a better way to go about this?

Thanks in advance!

-Aaron

0 Karma

somesoni2
Revered Legend

Give this a try

index=main | stats latest(os) as os latest(expiration) as expiration by host machineID 
| append [| metadata type=hosts index=main | table host firstTime lastTime] 
| stats values(os) as os values(expiration) as expiration values(firstTime) as "First Seen" values(lastTime) as "Last Seen" by host
| where isnotnull(os)
0 Karma
Get Updates on the Splunk Community!

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...