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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...