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

[AlexeySh]

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.

[renjith_nair · Accepted solution]

@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

[AlexeySh]

Gorgeous !

That's exectly what we were searching for.

Thanks for the help!