Dashboards & Visualizations

help for filtering a single panel result from a dropdown list

jip31
Motivator

hi
I use the search below which works fine
But as you can see I use a token in order to filter Site field from a dropdown list
But the filter by site doesnt works
what I have to add in my search in order to be able to filter by site please??
I tried to add "by SITE" in the stats command but it doesnt works

[| inputlookup host.csv 
    | table host] 
index=toto sourcetype=tutu
| fields Name host 
| dedup host 
| eval "SPLUNK agent status"="SPLUNK Agent is present" 
| append 
    [| inputlookup host.csv 
    | eval "SPLUNK agent status"="No SPLUNK Agent" 
    | table host, "SPLUNK agent status"] 
| lookup test.csv HOSTNAME as host output SITE 
| stats values(SITE) as Site, first("SPLUNK agent status") as "SPLUNK agent status" by host 
| search host=$tok_filterhost$ 
| search Site=$tok_filtersite$ 
| rename host as Hostname 
| search "SPLUNK agent status"="SPLUNK Agent is present" 
| stats count("SPLUNK agent status")
Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @jip31,
probably you have more than one value in Site, You could try to filter hosts and SITE before the stats command

 [| inputlookup host.csv 
     | table host] 
 index=toto sourcetype=tutu
 | fields Name host 
 | dedup host 
 | eval "SPLUNK agent status"="SPLUNK Agent is present" 
 | append 
     [| inputlookup host.csv 
     | eval "SPLUNK agent status"="No SPLUNK Agent" 
     | table host, "SPLUNK agent status"] 
 | lookup test.csv HOSTNAME as host output SITE 
 | search host=$tok_filterhost$ 
 | search SITE=$tok_filtersite$ 
 | stats values(SITE) as Site, first("SPLUNK agent status") as "SPLUNK agent status" by host 
 | rename host as Hostname 
 | search "SPLUNK agent status"="SPLUNK Agent is present" 
 | stats count("SPLUNK agent status")

There's a thing that I don't understand in your search: if you want the hosts of the lookup that are sending logs, you don't need the append command that is useful to check if there are hosts in lookup that aren't sending!

Anyway, I'd solve your need using a search like this:

index=toto sourcetype=tutu
| lookup test.csv HOSTNAME as host output SITE
| search host=$tok_filterhost$ 
| search Site=$tok_filtersite$ 
| stats count

Or maintaining the append command

index=toto sourcetype=tutu
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup host.csv | eval host=lower(host), count=0 | fields host count ]
| lookup test.csv HOSTNAME as host output SITE
| search host=$tok_filterhost$ 
| search Site=$tok_filtersite$ 
| stats values(SITE) as Site sum(count) AS total by host 
| where total>0
| stats count

If instead you want a table with the status of each host

index=toto sourcetype=tutu
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup host.csv | eval host=lower(host), count=0 | fields host count ]
| lookup test.csv HOSTNAME as host output SITE
| search host=$tok_filterhost$ 
| search Site=$tok_filtersite$ 
| stats values(SITE) as Site sum(count) AS total by host 
| eval status=if(total=0,"SPLUNK Agent is present","No SPLUNK Agent"=
| table host Site status

That you can also insert in a dashboard in graphic mode.

Ciao.
Giuseppe

View solution in original post

0 Karma

woodcock
Esteemed Legend

First of all, why are you moving on to another question when you have not finished getting a good answer and Accepting it on your previous answer here (this kind of thing demotivates us from bothering to help)?
https://answers.splunk.com/answers/785025/help-on-eval-condition.html

Start by fixing your search the RIGHT way and then use the tok_filterhost filter the correct way, like this:

index=toto sourcetype="tutu" AND Type="Service" AND Name="SplunkForwarder" AND $tok_filterhost$
| rename COMMENT AS "This solution assumes that there is no field 'Name' in the 'host.csv' file" 
| inputlookup append=t host.csv 
| search $tok_filterhost$
| stats values(Name) AS Names dc(Name) AS NameCount BY host 
| lookup host.csv host OUTPUT host AS keepme
| where isnotnull(keepme)
| fields - keepme
| eval Name = if(Name=="SplunkForwarder", "SPLUNK Agent is present", "No SPLUNK Agent") 
| rename Name AS "SPLUNK agent status"

Then make sure that you are using something like this in your fieldset:

 <prefix>(</prefix>
 <suffix>)</suffix>
 <valuePrefix>host="</valuePrefix>
 <valueSuffix>"</valueSuffix>
 <delimiter> OR </delimiter>
 <choice value="*">ALL</choice>
0 Karma

jip31
Motivator

Maybe you are right but for me it's another problem but i agree in the same search sorry
concerning your proposal it doesnt works and my dropdown list is based on | search Site=$tok_filtersite$
I can see it in your example

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @jip31,
probably you have more than one value in Site, You could try to filter hosts and SITE before the stats command

 [| inputlookup host.csv 
     | table host] 
 index=toto sourcetype=tutu
 | fields Name host 
 | dedup host 
 | eval "SPLUNK agent status"="SPLUNK Agent is present" 
 | append 
     [| inputlookup host.csv 
     | eval "SPLUNK agent status"="No SPLUNK Agent" 
     | table host, "SPLUNK agent status"] 
 | lookup test.csv HOSTNAME as host output SITE 
 | search host=$tok_filterhost$ 
 | search SITE=$tok_filtersite$ 
 | stats values(SITE) as Site, first("SPLUNK agent status") as "SPLUNK agent status" by host 
 | rename host as Hostname 
 | search "SPLUNK agent status"="SPLUNK Agent is present" 
 | stats count("SPLUNK agent status")

There's a thing that I don't understand in your search: if you want the hosts of the lookup that are sending logs, you don't need the append command that is useful to check if there are hosts in lookup that aren't sending!

Anyway, I'd solve your need using a search like this:

index=toto sourcetype=tutu
| lookup test.csv HOSTNAME as host output SITE
| search host=$tok_filterhost$ 
| search Site=$tok_filtersite$ 
| stats count

Or maintaining the append command

index=toto sourcetype=tutu
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup host.csv | eval host=lower(host), count=0 | fields host count ]
| lookup test.csv HOSTNAME as host output SITE
| search host=$tok_filterhost$ 
| search Site=$tok_filtersite$ 
| stats values(SITE) as Site sum(count) AS total by host 
| where total>0
| stats count

If instead you want a table with the status of each host

index=toto sourcetype=tutu
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup host.csv | eval host=lower(host), count=0 | fields host count ]
| lookup test.csv HOSTNAME as host output SITE
| search host=$tok_filterhost$ 
| search Site=$tok_filtersite$ 
| stats values(SITE) as Site sum(count) AS total by host 
| eval status=if(total=0,"SPLUNK Agent is present","No SPLUNK Agent"=
| table host Site status

That you can also insert in a dashboard in graphic mode.

Ciao.
Giuseppe

0 Karma

jip31
Motivator

Yes before the stats it works thanks!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...