Subsearch with different date range

[jkcouch]

Script sends VM configuration information into splunk daily. Trying to get a list of events that existed more than 3 times (3 days). Then I want to take that list and pare it down to the ones that still exist today(because many may not exist anymore since I use this list to remove the cause of the event daily).

source="PS_VM_Config" VMCDMedia="True" earliest=-3d@d | stats count(VMName) AS ConnectedDays, Values(VCenter) AS VCenter BY VMName | where ConnectedDays > 2 | join VMName [search source="PS_VM_Config" VMCDMedia="True" earliest=-1d@d] | table VMName VCenter 

Currently I get the full list, not the ones that still exist today. I want to see events that match this criteria: 3 ore more events in the last 3 days and exist within last 24hours.

[jkcouch · Accepted solution]

ytamura got me pointed in the right direction (thanks). join defaults to type=inner, my problem was the date formatting of "Earliest". Once changed to hour format it worked.

source="PS_VM_Config" VMCDMedia="True" earliest=-72h | stats count(VMName) AS ConnectedDays, Values(VCenter) AS VCenter BY VMName | where ConnectedDays > 2 | join [search source="PS_VM_Config" VMCDMedia="True" earliest=-24h] | table VMName VCenter 

[ytamura]

You can use the option:

| join type=inner

to only join events that have matching fields. Read more here: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Join

[jkcouch]

Thanks you got me pointed in the right direction.