help on a filter token which falsify the results

[jip31]

hi

I am doing a basic count with the xml below

    <input type="dropdown" token="tok_filtersite" searchWhenChanged="true">
      <label>Site</label>
      <choice value="N">N</choice>
      <choice value="SE">SE</choice>
      <initialValue>N</initialValue>
      <default>N</default>
    </input>
    <input type="dropdown" token="tok_filtercategory" searchWhenChanged="true">
      <label>Category.</label>
      <default>*</default>
      <choice value="*">*</choice>
      <choice value="HW/PC TABLET">HW/PC TABLET</choice>
      <choice value="HW/PC LAPTOP">HW/PC LAPTOP</choice>
      <choice value="HW/PC DESKTOP">HW/PC DESKTOP</choice>
      <choice value="Hardware">Hardware</choice>
      <initialValue>*</initialValue>
    </input>
    <input type="text" token="tok_filterdepartment" searchWhenChanged="true">
      <label>Department</label>
      <default>*</default>
      <initialValue>*</initialValue>
    </input>
    <input type="text" token="tok_filterresponsible" searchWhenChanged="true">
      <label>Responsible (Use *_* or "_")</label>
      <default>*</default>
      <initialValue>*</initialValue>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Number of devices with "Production" STATUS</title>
      <single>
        <title>Source : ServiceNow</title>
        <search>
          <query>| inputlookup fo_all where TYPE="PC" (DOMAIN=I OR DOMAIN=B) (CATEGORY = "HW/PC LAPTOP" OR CATEGORY ="HW/PC TABLET" OR CATEGORY ="HW/PC DESKTOP") (STATUS = "Production") 
| search SITE=$tok_filtersite|s$ 
| search CATEGORY=$tok_filtercategory|s$ 
| search DEPARTMENT=$tok_filterdepartment$ 
| search RESPONSIBLE_USER=$tok_filterresponsible|s$ 
| stats dc(HOSTNAME)</query>

But the count is right only when I delete the DEPARTMENT token and I dont know why

The only think I can say is that most the time, the DEPARTMENT field is empty

| search DEPARTMENT=$tok_filterdepartment$ 

And if cumulate the number of events when I add this at the end of my search :

 search NOT DEPARTMENT=""

 search DEPARTMENT=""

the number of results is right...

What is the problem please?

[bowesmana]

The problem is as you suspect, if the department field is not present, then searching for department=* will not find rows where department is null.

You could replace your search commands with

| search SITE=$tok_filtersite|s$ CATEGORY=$tok_filtercategory|s$ RESPONSIBLE_USER=$tok_filterresponsible|s$ 
| where DEPARTMENT=$tok_filterdepartment|s$ OR $tok_filterdepartment|s$="*"

 where the DEPARTMENT search is done with a where clause and says

'department matches filtered department OR filtered department is your default wildcard entry', i.e. the last part will match everything if it's ALL

[jip31]

have you an idea please??

[jip31]

like this, I have the good result but.....

the input text value for department field is not taken into account

If I put a value in department input text (*O* for example), the count has to be done on this value

But instead this, i have 0 in the results...

[bowesmana · Accepted solution]

This is because there where clause does not work quite the same way as 'search', i.e. where

| where DEPARTMENT=$tok_filterdepartment|s$

is doing a string literal equals, and not using the wildcards. You need a match+regex for where clauses.

So, I suggest this

| search SITE=$tok_filtersite|s$ CATEGORY=$tok_filtercategory|s$ RESPONSIBLE_USER=$tok_filterresponsible|s$ 
| fillnull value="" DEPARTMENT
| search DEPARTMENT=$tok_filterdepartment|s$

which will convert your null DEPARTMENT fields to empty fields and then use search to handle your input wildcards easily.

Hope this helps

[jip31]

thanks it works now 😉