topic Re: How to filter the data of lookup based on the drop-down values in Dashboards & Visualizations

How to filter the data of lookup based on the drop-down values

aditsss — Sun, 15 Nov 2020 14:46:35 GMT

Hi Everyone,

I have one drop -down in the dashboard .Below is code for the drop-down.

<input type="multiselect" token="Teams" searchWhenChanged="true">
<label>Teams</label>
<choice value="*">All</choice>
<choice value="Force">Force</choice>
<choice value="Blaz">BLAZ</choice>
<choice value="OneForce">Oneforce</choice>
<choice value="Auto">Auto</choice>
<fieldForLabel>Teams</fieldForLabel>
<prefix>(</prefix>
<valuePrefix>Teams ="</valuePrefix>
<valueSuffix>"</valueSuffix>
<delimiter> OR </delimiter>
<suffix>)</suffix>
<initialValue>*</initialValue>
<default>*</default>
</input>

I have one panel in same dashboard which is showing below fields. URL and Parent chain and many other fields.

URL PARENT CHAIN

1) https:/abcd/process-groups/ MAIN-->root-->BLAZ - E1-->Blaz Transformation - Data Ingestion

2)https:/abc/process-groups/ MAIN-->root-->BLAZ - E3

3)https:/abc/process-groups/ MAIN-->root-->Oneforce-->FXIP

My parent chain is coming from inputlookup that I have highlighted. Below is the code for the query:

<row>
<table>
<search>
<query>index=abc sourcetype=xyz source="/user.log" process-groups|rename count as "Request Counts" |rex field=Request_URL "(?<id>[A_Za-z0-9]{8}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{12})"|stats count by Date ADS_Id Request_Type id ClickHere Request_URL|sort - ADS_Id |join type=outer id [inputlookup chains.csv]</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
<sampleRatio>1</sampleRatio>
</search>
<fields>"Date", "ADS_Id","Request_Type", "Request_URL", "id", "parent_chain"</fields>
<option name="count">100</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">cell</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>
</row>

I want when I select say "Blaz" from the teams drop-down then the parent chain which consists of 3rd word as "-->BLAZ" like :

MAIN-->root-->BLAZ - E1-->Blaz Transformation - Data Ingestion

MAIN-->root-->BLAZ - E3

when I select say Oneforce from the teams drop-down then the parent chain which consists of 3rd word as "-->Oneforce" like :

MAIN-->root-->Oneforce-->FXIP.

Can someone guide me on this.

Thanks in advance.

Re: How to filter the data of lookup based on the drop-down values

aditsss — Sun, 15 Nov 2020 17:32:57 GMT

Can anyone please guide me on this.

Re: How to filter the data of lookup based on the drop-down values

aditsss — Mon, 16 Nov 2020 06:43:37 GMT

Can someone please guide me on this.

Re: How to filter the data of lookup based on the drop-down values

aditsss — Mon, 16 Nov 2020 12:31:48 GMT

Can someone please guide me on this please.

I have made some changes like this.

Drop-down code:

<input type="multiselect" token="teams" searchWhenChanged="true">
<label>Teams</label>
<choice value="All">All Teams</choice>
<choice value="BLAZE">BLAZE</choice>
<choice value="Oneforce">Oneforce</choice>
<fieldForLabel>Teams</fieldForLabel>
<prefix>(</prefix>
<valuePrefix>Teams ="</valuePrefix>
<valueSuffix>"</valueSuffix>
<delimiter> OR </delimiter>
<suffix>)</suffix>
<initialValue>All</initialValue>
<default>All</default>
<change>
<eval token="form.teams">case(mvcount('form.teams')=0,"All",mvcount('form.teams')>1 AND mvfind('form.teams',"All")>0,"All",mvcount('form.teams')>1 AND mvfind('form.teams',"All")=0,mvfilter('form.teams'!="All"),1==1,'form.teams')</eval>
<eval token="BLAZE">if(isnull(mvfind('form.teams',"BLAZE")),mvfind('form.teams',"All"),1)</eval>
<eval token="Oneforce">if(isnull(mvfind('form.teams',"Oneforce")),mvfind('form.teams',"All"),1)</eval>
<eval token="org_choice">if(mvfind('form.teams',"All")=0,$teams$)</eval>
</change>
</input>

Query code I have passed token in the queries like this but its not working.

<row>
<table>
<search>
<query>index=abc sourcetype=xyz source="/user.log" process-groups $BLAZE$ OR $Oneforce$|rename count as "Request Counts" |rex field=Request_URL "(?<id>[A_Za-z0-9]{8}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{12})"|stats count by Date ADS_Id Request_Type id ClickHere Request_URL|sort - ADS_Id |join type=outer id [inputlookup chains.csv]</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
<sampleRatio>1</sampleRatio>
</search>
<fields>"Date", "ADS_Id","Request_Type", "Request_URL", "id", "parent_chain"</fields>
<option name="count">100</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">cell</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>
</row>

Can someone please guide me on this.

Re: How to filter the data of lookup based on the drop-down values

aditsss — Mon, 16 Nov 2020 15:48:47 GMT

Someone please help me out in this .please

Re: How to filter the data of lookup based on the drop-down values

ITWhisperer — Mon, 16 Nov 2020 19:18:40 GMT

Shouldn't "[A_Za-z0-9]" be "[A-Za-z0-9]"?

Is parent chain returned by the lookup or is it in your initial search events?

Re: How to filter the data of lookup based on the drop-down values

aditsss — Mon, 16 Nov 2020 19:32:47 GMT

@ITWhisperer

The parent chain is returned by the lookup.

Requirement:

I have one dashboard which consists of drop down and panels.

I have one drop down as "Teams" whose code is as below:

<input type="multiselect" token="teams" searchWhenChanged="true">
<label>Teams</label>
<choice value="All">All Teams</choice>
<choice value="BLAZE">BLAZE</choice>
<choice value="Oneforce">Oneforce</choice>
<fieldForLabel>Teams</fieldForLabel>
<prefix>(</prefix>
<valuePrefix>Teams ="</valuePrefix>
<valueSuffix>"</valueSuffix>
<delimiter> OR </delimiter>
<suffix>)</suffix>
<initialValue>All</initialValue>
<default>All</default>
<change>
<eval token="form.teams">case(mvcount('form.teams')=0,"All",mvcount('form.teams')>1 AND mvfind('form.teams',"All")>0,"All",mvcount('form.teams')>1 AND mvfind('form.teams',"All")=0,mvfilter('form.teams'!="All"),1==1,'form.teams')</eval>
<eval token="BLAZE">if(isnull(mvfind('form.teams',"BLAZE")),mvfind('form.teams',"All"),1)</eval>
<eval token="Oneforce">if(isnull(mvfind('form.teams',"Oneforce")),mvfind('form.teams',"All"),1)</eval>
<eval token="org_choice">if(mvfind('form.teams',"All")=0,$teams$)</eval>
</change>
</input>

I have one panel which is showing multiple fields including parent chain and parent chain is coming from inputlookup.

parent_chain

MAIN-->root-->BLAZE - E1-->Blaz Transformation - Data

MAIN-->root-->BLAZE - E3

MAIN-->root-->Oneforce-->FXIP

Below is the code of the panel. The parent chain is coming from inputlookup chains.csv .

what I want is when I select "BLAZE" from the teams drop-down all the parent chain which consists of 3rd word as "BLAZE" should be shown .Basically the parent chain which include the word as "BLAZE" should come. Like below:

MAIN-->root-->BLAZE - E1-->Blaz Transformation - Data

MAIN-->root-->BLAZE - E3

when I select the word "Oneforce" from teams drop-down the parent chain which consists of word "Oneforce" should come like this:

MAIN-->root-->Oneforce-->FXIP

MAIN-->root-->Oneforce-->Support_Tools

I have passed the tokens as $BLAZE$ OR $Oneforce$ in query but still result is not filtering.

Below is the code for it.

<row>
<table>
<search>
<query>index=abc sourcetype=xyz source="/user.log" process-groups $BLAZE$ OR $Oneforce$|rename count as "Request Counts" |rex field=Request_URL "(?<id>[A-Za-z0-9]{8}[\-][A-Za-z0-9]{4}[\-][A-Za-z0-9]{4}[\-][A-Za-z0-9]{4}[\-][A-Za-z0-9]{12})"|stats count by Date ADS_Id Request_Type id ClickHere Request_URL|sort - ADS_Id |join type=outer id [inputlookup chains.csv]</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
<sampleRatio>1</sampleRatio>
</search>
<fields>"Date", "ADS_Id","Request_Type", "Request_URL", "id", "parent_chain"</fields>
<option name="count">100</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">cell</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>
</row>

@ITWhisperer please guide me. Your solutions always works for me.

Thanks in advance.

Re: How to filter the data of lookup based on the drop-down values

aditsss — Fri, 04 Dec 2020 12:58:07 GMT

@ITWhisperer

Your solutions always work for me. Please can you guide me on this.

Re: How to filter the data of lookup based on the drop-down values

aditsss — Fri, 04 Dec 2020 13:11:54 GMT

@ITWhisperer

Can you please guide me on this.

Re: How to filter the data of lookup based on the drop-down values

aditsss — Fri, 04 Dec 2020 19:24:45 GMT

@ITWhisperer

Can you please guide me on this.

Re: How to filter the data of lookup based on the drop-down values

aditsss — Sat, 05 Dec 2020 18:36:58 GMT

@ITWhisperer

Your solutions always help me out. Can you please guide me on this.

Re: How to filter the data of lookup based on the drop-down values

aditsss — Sat, 05 Dec 2020 19:33:29 GMT

@ITWhisperer

Please guide me on this. Your solutions are always helpful.

Re: How to filter the data of lookup based on the drop-down values

somesoni2 — Sat, 05 Dec 2020 21:07:17 GMT

Try something like this (changed how token was formatted in your multiselect, replaced join with lookup command and used token in correct place).

<?xml version="1.0" encoding="UTF-8"?> <form> <fieldset> <input type="multiselect" token="Teams" searchWhenChanged="true"> <label>Teams</label> <choice value="*">All</choice> <choice value="Force">Force</choice> <choice value="Blaz">BLAZ</choice> <choice value="OneForce">Oneforce</choice> <choice value="Auto">Auto</choice> <fieldForLabel>Teams</fieldForLabel> <prefix>(</prefix> <valuePrefix> parent_chain="*</valuePrefix> <valueSuffix>*"</valueSuffix> <delimiter> OR </delimiter> <suffix>)</suffix> <initialValue>*</initialValue> <default>*</default> </input> </fieldset> <row> <table> <search> <query>index=abc sourcetype=xyz source="/user.log" process-groups|rename count as "Request Counts" |rex field=Request_URL "(?<id>[A_Za-z0-9]{8}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{12})"|stats count by Date ADS_Id Request_Type id ClickHere Request_URL|sort - ADS_Id |lookup chains.csv id OUTPUT parent_chain | where $Teams$</query> <earliest>$field1.earliest$</earliest> <latest>$field1.latest$</latest> <sampleRatio>1</sampleRatio> </search> <fields>"Date", "ADS_Id","Request_Type", "Request_URL", "id", "parent_chain"</fields> <option name="count">100</option> <option name="dataOverlayMode">none</option> <option name="drilldown">cell</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> </row> </form>

Re: How to filter the data of lookup based on the drop-down values

aditsss — Tue, 08 Dec 2020 08:30:44 GMT

@somesoni2

I have tried this but nothing is coming.

Its showing Result not found.

Can you let me know where I have gone wrong:

Multiselect code:

<input type="multiselect" token="Teams" searchWhenChanged="true">
<label>Teams</label>
<choice value="*">All</choice>
<choice value="BLAZE">BLAZE</choice>
<choice value="Oneforce">Oneforce</choice>
<fieldForLabel>Teams</fieldForLabel>
<prefix>(</prefix>
<valuePrefix> parent_chain="*</valuePrefix>
<valueSuffix>*"</valueSuffix>
<delimiter> OR </delimiter>
<suffix>)</suffix>
<initialValue>*</initialValue>
<default>*</default>
</input>

My panel code from where parent chain is coming:

<row>
<table>
<search>
<query>index=abc sourcetype=xyz source="user.log" process-groups | convert timeformat="%Y-%m-%d" ctime(_time) AS Date|rename count as "Request Counts" |rex field=Request_URL "(?<id>[A_Za-z0-9]{8}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{12})"|stats count by Date ADS_Id Request_Type id ClickHere Request_URL|sort - ADS_Id |lookup nifi_api_parent_chains.csv id OUTPUT parent_chain | where $Teams$</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
<sampleRatio>1</sampleRatio>
</search>
<fields>"Date", "ADS_Id","Request_Type", "Request_URL", "id", "parent_chain"</fields>
<option name="count">100</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">cell</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>
<drilldown>
<condition field="Request_URL">
<link target="_blank">$row.ClickHere|n$</link>
</condition>
</drilldown>
</table>
</row>

Can you guide me on that.

Re: How to filter the data of lookup based on the drop-down values

aditsss — Tue, 08 Dec 2020 20:05:58 GMT

@somesoni2

I have tried this but nothing is coming.

Its showing Result not found.

Can you let me know where I have gone wrong:

Multiselect code:

My panel code from where parent chain is coming.

Can you please guide me.

Re: How to filter the data of lookup based on the drop-down values

ITWhisperer — Wed, 09 Dec 2020 12:09:20 GMT

What do you get if you remove the "where $Teams$"? Do you get the results you expect just unfiltered?

Re: How to filter the data of lookup based on the drop-down values

aditsss — Wed, 09 Dec 2020 12:46:16 GMT

@ITWhisperer

Yes I am able to get the result unfiltered. All the parent chains are coming. when I remove where $Teams$ from the query.

Re: How to filter the data of lookup based on the drop-down values

ITWhisperer — Wed, 09 Dec 2020 12:50:01 GMT

What about if you use

| where (parent_chain="*BLAZE*")

Re: How to filter the data of lookup based on the drop-down values

aditsss — Wed, 09 Dec 2020 13:24:10 GMT

@ITWhisperer

Can you guide me is that correct I have tried something like this:

multiselect code:

<input type="multiselect" token="Teams" searchWhenChanged="true">
<label>Teams</label>
<choice value=" ">All</choice>
<choice value="BLAZE">BLAZE</choice>
<choice value="Oneforce">Oneforce</choice>
<choice value="MerchantForce">MerchantForce</choice>
<choice value="MDT">MDT</choice>
<choice value="ConsumerForce">ConsumerForce</choice>
<choice value="Mkt-Auto">Mkt-Auto</choice>
<choice value="MarketingForce">MarketingForce</choice>
<choice value="PremiumProductPlatform">PremiumProductPlatform</choice>
<fieldForLabel>Teams</fieldForLabel>
<valuePrefix>%</valuePrefix>
<valueSuffix>%</valueSuffix>
<delimiter> OR </delimiter>
<initialValue>All</initialValue>
<default>All</default>
</input>

panel code:

<row>
<table>
<search>
<query>index=abc sourcetype=xyz source="user.log" process-groups | convert timeformat="%Y-%m-%d" ctime(_time) AS Date|rename count as "Request Counts" |rex field=Request_URL "(?<id>[A_Za-z0-9]{8}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{4}[\-][A_Za-z0-9]{12})"|fillnull value="" id| eval ClickHere= "https://com/abc/?processGroupId=".id|stats count by Date ADS_Id Request_Type id ClickHere Request_URL|sort - ADS_Id |join type=outer id [inputlookup nifi_api_parent_chains.csv]|where like(parent_chain, "$Teams$")</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
<sampleRatio>1</sampleRatio>
</search>
<fields>"Date", "ADS_Id","Request_Type", "Request_URL", "id", "parent_chain"</fields>
<option name="count">100</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">cell</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>
<drilldown>
<condition field="Request_URL">
<link target="_blank">$row.ClickHere|n$</link>
</condition>
</drilldown>
</table>
</row>

when I am selecting "BLAZE" from multiselect I am able to get the "BLAZE" result from parent chain.

when I am selecting "OneForce" from multiselect I am able to get the "OneForce" result from the parent chain.

The issue I am facing is when I am selecting both "BLAZE" and "OneForce" from multiselect its showing "NO result Found". Its only working individually.

Also when I am selecting "ALL" its not showing the rows which does not include the parent_chain.

Can you please guide me where I have gone wrong.

Thanks in advance.

Re: How to filter the data of lookup based on the drop-down values

ITWhisperer — Wed, 09 Dec 2020 13:45:14 GMT

Try this in your dropdown

and

<prefix>(</prefix> <valuePrefix>like(parent_chain, "%</valuePrefix> <valueSuffix>%")</valueSuffix> <delimiter> OR </delimiter> <suffix>)</suffix>

and in your query

where $Teams$