Splunk Search

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

AlexeySh
Communicator

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.

0 Karma
1 Solution

renjith_nair
Legend

@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
---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

renjith_nair
Legend

@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
---
What goes around comes around. If it helps, hit it with Karma 🙂

AlexeySh
Communicator

Gorgeous !

That's exectly what we were searching for.

Thanks for the help!

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...