Search code examples
splunksplunk-query

Splunk to take the second queries result(field) into first query for Percentage Memory of Linux host

I am a newbie to SplunK.

I am trying to pull the Memory % of my Linux hosts which belong to a particular group called Database_hosts.

I am able to get the Memory % of a particular host if I provide that explicitly as host="host01.example.com" however, I'm looking to run this query against multiple hosts.

Multiple hosts which belong to Database_hosts group I can extract from the inputlookup cmdb_host.csv in Splunk.

Now, I can extract the hosts from inputlookup cmdb_host.csv where it contains the hosts in name field but I am clueless how to put my second query into my first query ie sourcetype=top pctMEM=* host="host01.example.com"

Both the queries working independently though.

My first query:

sourcetype=top pctMEM=* host="host01" OR host="host02"
| multikv 
| dedup host
| rex field=pctMEM "(?<usage>\d+)" 
| where usage> 40
| table  host pctMEM

Result on run:

enter image description here

and this is my second query:

| inputlookup cmdb_host.csv
| search support_group="Database_hosts" NOT (fqdn IN("ap*", "aw*",""))
| table name

Result on run:

enter image description here

How I can use my second query output field name into first query's host= field?

Any help will be much appreciated.

EDIT: just tried but no luck:

sourcetype=top pctMEM=* host="[inputlookup cmdb_host.csv where support_group="Database_hosts" | table name] 
| multikv 
| dedup name
| rex field=pctMEM "(?<usage>\d+)" 
| where usage>20
| table  name pctMEM
Solution

  • You're very close. If you run the subsearch (the part inside square brackets) by itself and add | format then you'll see what is returned to the main search. It'll look something like ((name=host01) OR (name=host02)). Combining that with the main search produces:

    sourcetype=top pctMEM=* host=((name=host01) OR (name=host02))
| multikv 
| dedup name
| rex field=pctMEM "(?<usage>\d+)" 
| where usage>20
| table  name pctMEM

    which won't work. It can be fixed by renaming name to host in the subsearch and letting the subsearch create the expression.

    sourcetype=top pctMEM=* [|inputlookup cmdb_host.csv where support_group="Database_hosts" 
  | return 100 host=name]
| multikv 
| dedup name
| rex field=pctMEM "(?<usage>\d+)" 
| where usage>20
| table  name pctMEM

    The return command tells Splunk to return up to 100 results and rename name to host. It's equivalent to fields name | rename name as host | format.