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 :slightly_smiling_face:

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 :slightly_smiling_face:

AlexeySh
Communicator

Gorgeous !

That's exectly what we were searching for.

Thanks for the help!

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...