How to pass query results related to a bar to a panel?

[mg12345]

Hi,

I've classifying different kind of address mistakes and show the amount of these classes in a bar chart. For example "addresses missing a zip", "street addresses without street name", "street address without an apartment number", "addresses with a zip 00000", etc.

As understood this kind of calculation must be done by defining each rule separately and thus, a basic 'stats count by' doesn't work. I've created the cases with if-statements using one variable for each different rule. Then I put these variables in one stats statement.

What I next need is the possibility to drill down to the selected bar and show the addresses belonging to the select bar in a different panel. For example the bar "addresses a zip 00000" could have 4 cases and selecting the bar would show those 4 address in a separate panel like

Streetname1 A 5 00000 Helsinki

Streenname2 45 00000 Tampere

Streetname3 3-5a 56 C 00000 Turku

Streetname4 120/3 00000 Rovaniemi

Unfortunately, I haven't found any answers how to do this.

[ITWhisperer]

In your drilldown, evaluate a token to be the condition you want to apply to your search in your second panel.

[mg12345]

Hi,

Thank you for a quick reply. The problem is - as far as I understand - that stats removes the content. The zip is in the i_postcode field.

"original search" | eval zipZero=if(i_postcode="00000"),"yes",NULL) | stats count(eval(zipZero="yes")) AS "zipZero"

If I make a search to zipZero after stats the content is empty. When I remove the 'stats' and make the search to zipZero has the content. I found that others have had the same problem. Any solution for this? I tried using a token defined in '<init>' part of the XML, trying to use this as a "global variable". Unfortunately, it didn't wok

[ITWhisperer]

As I understand what you have said, and please correct me if I am wrong, is that you want a bar chart such that when you click on the zipZero bar (for example) the other panel filters your results to show the corresponding events?

In the following example, when a bar in the column chart is clicked, the filter token is set to a value that can be used in the where clause of the subsequent panel e.g. Nov_20 != 0. In your case, if the zipZero bar is clicked, you could set your token to i_postcode="00000" and use that in your subsequent panel.

  <row>
    <panel>
      <chart>
        <title>Message received</title>
        <search>
          <query>| makeresults | eval _raw="Messages,Nov_20,Dec_20,Jan_21,Feb_21
Messge 0,0,1,0,0
Messge 1,1,3,1,1
Messge 2,11,0,0,0
Messge 3,1,0,0,0
Messge 4,9,5,0,0
Messge 5,1,1,0,0
Messge 6,1,1,0,0
Messge 7,0,1,0,0"
| multikv forceheader=1
| fields - _raw _time linecount
| fields - _mkv*
| fields Messages *</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">column</option>
        <option name="charting.drilldown">all</option>
        <option name="refresh.display">progressbar</option>
        <drilldown>
          <eval token="filter">
            case($click.name2$="Nov_20","Nov_20!=0",$click.name2$="Dec_20","Dec_20!=0",$click.name2$="Jan_21","Jan_21!=0",$click.name2$="Feb_21","Feb_21!=0")
          </eval>
        </drilldown>
      </chart>
    </panel>
    <panel depends="$filter$">
      <table>
        <title>Selected dates</title>
        <search>
          <query>| makeresults | eval _raw="Messages,Nov_20,Dec_20,Jan_21,Feb_21
Messge 0,0,1,0,0
Messge 1,1,3,1,1
Messge 2,11,0,0,0
Messge 3,1,0,0,0
Messge 4,9,5,0,0
Messge 5,1,1,0,0
Messge 6,1,1,0,0
Messge 7,0,1,0,0"
| multikv forceheader=1
| fields - _raw _time linecount
| fields - _mkv*
| fields Messages *
| where $filter$</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>