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

[agdavidson]

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

[somesoni2]

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)