help on a drop-down list token

[jip31]

Hello
I use the search below which works fine

[| inputlookup host.csv 
    | table host] index="x" sourcetype="PerfmonMk:Process" process_name=chrome ("%_Processor_Time"=0) 
| dedup host 
| stats count as NbMESCrashByHost 
| appendcols 
    [| inputlookup host.csv 
    | stats count as NbIndHost] 
| eval Perc=round((NbMESCrashByHost/NbIndHost)*100,1). " %" + " / " + NbIndHost + " machines " 
| table Perc

Now I need to link this search with a drop-down list
The name of the drop-down list is $tok_filtersite2$

So I added 2 lines in my search (in bold) but it doesnt works

[| inputlookup host.csv 
    | table host] index="x" sourcetype="PerfmonMk:Process" process_name=chrome ("%_Processor_Time"=0) 
| dedup host 
**| lookup lookup_cmdb_fo_all.csv HOSTNAME as host output SITE** 
| stats count as NbMESCrashByHost by host SITE 
**| search SITE=$tok_filtersite2$** 
| appendcols 
    [| inputlookup host.csv 
    | stats count as NbIndHost] 
| eval Perc=round((NbMESCrashByHost/NbIndHost)*100,1). " %" + " / " + NbIndHost + " machines " 
| table Perc

could you help me please??

[VatsalJagani · Accepted solution]

Hi @jip31,

Please refer to my answer https://answers.splunk.com/answers/750072/how-to-fulfill-a-drop-down-list-automtically.html

[jip31]

Hummmm
Its not the same problem
What you have adviced me works fine
I have to use it in a table panel and in a single panel

In a table panel it works fine :
[| inputlookup host.csv
| table host] index="x" sourcetype="PerfmonMk:Process" process_name=chrome ("%_Processor_Time"=0)
| lookup lookup_cmdb_fo_all.csv HOSTNAME as host output SITE
| stats count(process_name) as Total by host SITE
| search SITE=$tok_filtersite|s$
| sort -Total limit=10

But not in the single panel
To my mind its not due to the drop down configuration but its due to a mistake in the search
The SITE field is not taken into account when I change value in the drill down
For example, if I choose * in the drill down, in the table panel I have 4 hosts corresponding on 3 differents SITE. So the eval Perc is 10% because I have 4 hosts on a total of 40 machines (10/40)
But in fact I have to have 7,5% because there is only 3 SITES (3/40)
what is the issue please???

[FrankVl]

According to the original search in your question (without the site filter), you divide the number of crashed hosts by the number of hosts. So that results in 10% for 4 crashed hosts on 40 total hosts. So far it makes sense to me.

Then you add a site filter, but while doing that, you also completely change the calculation, by changing the stats count to a stats count by host SITE. Since you just did a dedup host before that, the result of that stats will simply be count=1 for each host/SITE pair.

That is probably why it doesn't do what you want / expect it to do, but I don't understand what exactly you want / expect so don't have a suggestion right now on how to fix it. Please describe more clearly what exact calculation you are trying to do.

As @VatsalJagani mentioned, it might be helpful if you can share some example data and expected outcome based on that data.

Perhaps do some troubleshooting on your search by executing it step by step to see at which point it no longer does what you want / expect.

[jip31]

@ VatsalJagani
40 - number of hosts you have in host.csv file : YES
3 - number of SITEs that you have in lookup_cmdb_fo_all.csv : NO
3 is the number of host from host.csv where the condition "process_name=chrome ("%_Processor_Time"=0) " is TRUE
So when I divide 3 / 40 I am able to know the volumetry of host from host.csv where this condition is TRUE
Now what I want to do is to calculate the same volumetry but with one more condition which is a SITE filter from a drop down list
It means that I want to calculate the volumetry for a specific SITE
So I need to do a match between an host from host.csv, a specific condition ("process_name=chrome ("%_Processor_Time"=0) ") and a SITE
As you can imagine, 2 differents host can have a same SITE
So if I take the 3 host of the example, I need to calculate the number of host which have the same SITE in order to calculate the volumetry by SITE

@ Franck : its the reason why I have added "by SITE"
please tell me if my explanations are enough clear now

[FrankVl]

If you just want to do exactly the same as your original search, but filtered for a certain site, then keep your original stats command, but move the SITE filter above the stats command.

[VatsalJagani]

@jip31 - I agree with @FrankVI on point of moving of | search SITE=$tok_filtersite2$ above the stats command. One more question, In your first question you mentioned you want no. of sites(3) divide by the number of total hosts (40) that mathematically does not make sense as a percentage is part/total of the same entity.

Still what you want is (3/40) remove host from group by in stats | stats count as NbMESCrashByHost by SITE.

[jip31]

Sorry but I am lost...
I am doing a synthesis

From a dropdown list, I need to update 2 panels:

FIRST PANEL:
[| inputlookup host.csv
| table host] index="ai-wkst-perfmon-fr" sourcetype="PerfmonMk:Process" process_name=* ("%_Processor_Time"=0)
| lookup lookup_cmdb_fo_all.csv HOSTNAME as host output SITE
| search SITE=$tok_filtersite|s$
| stats count(process_name) as Total by host SITE
| sort -Total limit=10

*SECOND PANEL:*
[| inputlookup host.csv
| table host] index="ai-wkst-perfmon-fr" sourcetype="PerfmonMk:Process" process_name=* ("%_Processor_Time"=0)
| dedup host
| lookup lookup_cmdb_fo_all.csv HOSTNAME as host output SITE
| search SITE=$tok_filtersite$
| stats count as NbTOUCHNGOCrashByHost
| appendcols
[| inputlookup host.csv
| stats count as NbIndHost]
| eval Perc=round((NbTOUCHNGOCrashByHost/NbIndHost)*100,1). " %" + " / " + NbIndHost + " machines "
| rex mode=sed field=Perc "s/%/%\n/g"
| table Perc

When I choose a SITE from the dropdown list, the first panel is updated but the second display always 0%
Its very strange because when I put * in the dropdown list the second panel works fine

So what is the problem please??
remark:
in the first panel, I use "by host SITE" because I need to do a count by host and by SITE

[VatsalJagani]

@jip31 - Please give screenshot of your second search's job inspect.
And what you can do is check which line of search is removing results. Start removing one line at a time from bottom to see which line of search is having problem.

[jip31]

I have found!
it was due to | search SITE=$tok_filtersite|s$
I have added |s in order to undertsand the space in the drop down list....

[FrankVl]

Right, I spotted that, but that was only in the search for the table which you claimed was working fine. So I assumed that was a typo somehow.

Glad to hear you solved it 🙂

[VatsalJagani]

Let me understand this:
40 - number of hosts you have in host.csv file
3 - number of SITEs that you have in lookup_cmdb_fo_all.csv
4 - what is 4?

It would be great if you give sample host.csv file and lookup_cmdb_fo_all.csv and tell what result you need based on that.