topic How do you use a subsearch with a 'table' command? in Splunk Search

How do you use a subsearch with a 'table' command?

AlexeySh — Tue, 29 Jan 2019 12:28:52 GMT

Hello,

In order to detect unused workstations in our computer park, we are searching for all assets not connected to Active Directory (AD) AND to Ghost Solution Suite (GSS) since >90 days.

We can easily perform two searches independently, which are basically the same. First one:

sourcetype=my_ad_sourcetype
| eval it = strptime(ad_last_inventory,"%Y-%m-%d")
| eval ot = strptime(nowstring,"%Y-%m-%d")
| eval diff = (ot - it)
| eval round = round(diff/86400, 0)
| search round > 90
| table ad_wks_name, ad_last_inventory

And the second one:

sourcetype=my_gss_sourcetype
| eval it = strptime(gss_last_inventory,"%Y-%m-%d")
| eval ot = strptime(nowstring,"%Y-%m-%d")
| eval diff = (ot - it)
| eval round = round(diff/86400, 0)
| search round > 90
| table gss_wks_name, gss_last_inventory

What we can’t do is to combine those two searches. We tried to execute one of two queries as a subsearch and perform a simple comparison at the end like:

| where gss_wks_name=ad_wks_name

Every time we face an issue, the main search is executed correctly, but the subsearch doesn’t give out the correct result. Instead it repeats the _wks_name and the _last_inventory date for the last workstation.

wks_123 | 2018-10-20 23:12:00.0
wks_123 | 2018-10-20 23:12:00.0
wks_123 | 2018-10-20 23:12:00.0
etc.

Do you have an idea what we're doing wrong?

Thanks for the help!

Alex.

Re: How do you use a subsearch with a 'table' command?

renjith_nair — Tue, 29 Jan 2019 14:56:30 GMT

@AlexeySh ,

Assuming that you have only one record per workstation, i.e. last_inventory is the latest value of that machine

Try this and lets know if there are some changes,

  sourcetype=my_ad_sourcetype OR sourcetype=my_gss_sourcetype
  |eval itAd=strptime(ad_last_inventory,"%Y-%m-%d"), itGss=strptime(gss_last_inventory,"%Y-%m-%d")
  |eval ot=strptime(nowstring,"%Y-%m-%d")
  |eval diffAd=round((ot-itAd)/86400,0) , diffGss=round((ot-itGss)/86400,0)
  |eval wks_name=coalesce(ad_wks_name,gss_wks_name)
  |table wks_name,diffAd,diffGss |fillnull value=0
  |stats max(diffAd) as diffAd,max(diffGss) as diffGss by wks_name
  |where diffAd>90 AND diffGss>90

Re: How do you use a subsearch with a 'table' command?

AlexeySh — Tue, 29 Jan 2019 15:59:03 GMT

Gorgeous !

That's exectly what we were searching for.

Thanks for the help!