topic Re: How to filter lookup data through a text box in Dashboards & Visualizations

How to filter lookup data through a text box

aditsss — Fri, 11 Dec 2020 18:15:23 GMT

Hi Everyone,

I have one requirement .

I have one panel in the dashboard which consists of various fields. There is one field "parent_chain" which is coming from the lookup "parent_chains.csv"

Below is the code for the panel:

<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 |join type=outer id [inputlookup parent_chains.csv]|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>

The parent chain data is like this:

MAIN-->root-->Oneforce

MAIN-->root-->Oneforce-->AP_Automation

MAIN-->root-->BLAZE - E3-->Lead IB jobs

MAIN-->root-->BLAZE - E3-->Blaze Transformation - Data Ingestion

.....................................

.......................................

My requirement is that I need to create one text box and when I type like "Data Ingestion" in text box then all the parent_chain which consists of Data Ingestion should come.

If I write suppose "Blaze Transformation" in text box then all the parent_chain which consists of "Blaze Transformation" should come.

I have created text box like this:

</input>
<input type="text" token="ckey" searchWhenChanged="true">
<label>Parent Chain</label>
<prefix>parent_chain="*</prefix>
<suffix>*"</suffix>
</input>

Can anyone guide me on this. How I can achieve this.

Re: How to filter lookup data through a text box

aditsss — Fri, 11 Dec 2020 19:58:26 GMT

Can someone please guide me on this.

Re: How to filter lookup data through a text box

aditsss — Sat, 12 Dec 2020 15:46:00 GMT

Can someone please guide me on this.

Re: How to filter lookup data through a text box

nickhills — Sat, 12 Dec 2020 16:26:16 GMT

Your requirement is a bit unclear, but try this:

Don't join on a lookup, use it as a lookup.

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 parent_chains.csv id |search $ckey$ $Teams$

You don't specify where $Teams$ comes from - I assume it's another token and it matches in the lookup file?

Re: How to filter lookup data through a text box

aditsss — Sun, 13 Dec 2020 21:08:46 GMT

@nickhills

I tried what you have suggested but its showing "Result not found"

Can you guide me where I have gone wrong.

My requirement is this :

I have one panel which consists of various fields. There is one field parent_chain which is coming from lookup "parent_chains.csv".

parent_chain data looks like this:

MAIN-->root-->Oneforce

MAIN-->root-->Oneforce-->AP_Automation

MAIN-->root-->BLAZE - E3-->Lead IB jobs

MAIN-->root-->BLAZE - E3-->Blaze Transformation - Data Ingestion.

........................

My requirement is this I need to create one text box and need to put filter on any word of the parent_chain.

Suppose I type "Data Ingestion" in text box then all the parent_chain which consists of Data Ingestion in parent chain should be shown.

Suppose I type "Lead IB jobs" in text box then all the parent_chain which includes "Lead IB jobs" word in parent_chain should come.

Below is my code for the text box:

<input type="text" token="ckey" searchWhenChanged="true">
<label>Parent Chain</label>
<prefix>parent_chain="*</prefix>
<suffix>*"</suffix>
</input>

Below is my code for the panel:

<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 |join type=outer id [inputlookup parent_chains.csv]|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>

This $Teams$ is another filter which I have put on parent_chain. Its a multiselect drop-down filter.

How should I pass another token "ckey" for my text box filter here.

@nickhills Can you guide me on this.

Let me know if any other information is required.

Thanks in advance

Re: How to filter lookup data through a text box

aditsss — Sun, 13 Dec 2020 21:03:51 GMT

Can someone please guide me on this.

Re: How to filter lookup data through a text box

aditsss — Sun, 13 Dec 2020 21:11:18 GMT

Can someone please guide me on this.

Re: How to filter lookup data through a text box

aditsss — Mon, 14 Dec 2020 05:42:58 GMT

Can someone please look into my issue.

Re: How to filter lookup data through a text box

aditsss — Mon, 14 Dec 2020 09:33:05 GMT

Can someone please guide me on my issue.

Re: How to filter lookup data through a text box

nickhills — Mon, 14 Dec 2020 09:34:20 GMT

Start by running this search and see if it produces the results you require.

If not, be sure to update us with the results you do get and your expectations:

Also, I note you do a rename [|rename count as "Request Counts" ] which you don't use. Is that intentional?

Re: How to filter lookup data through a text box

aditsss — Mon, 14 Dec 2020 13:50:39 GMT

@nickhills

This is not showing any results.

My Requirement is like this:

I have one panel which consists of several fields . There is one field parent_chain which is coming from lookup parent_chains.csv rest all fields are coming from events.

parent chain data look like this:

MAIN-->root-->Oneforce

MAIN-->root-->Oneforce-->AP_Automation

MAIN-->root-->BLAZE - E3-->Lead IB jobs

MAIN-->root-->BLAZE - E3-->Blaze Transformation - Data Ingestion.

Now my requirement is I need to create one text box and I need to put filter on any word of the parent chain

Example:

If I type "Data Ingestion" in text box then all the parent_chain which consists of Data Ingestion in parent chain should be shown.

If I type "Lead IB jobs" in text box then all the parent_chain which includes "Lead IB jobs" word in parent_chain should come.

In short I want filter on parent_chain words

I have created text box. Below is the code for it.

<input type="text" token="ckey" searchWhenChanged="true">
<label>Parent Chain</label>
<prefix>parent_chain="*</prefix>
<suffix>*"</suffix>
</input>

Code for panel:

<row>
<table>
<search>
<query>index=abc sourcetype=xyz source="user.log" process-groups| convert timeformat="%Y-%m-%d" ctime(_time) AS Date |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 parent_chains.csv]|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>

This $Teams$ is another filter which I have put on parent_chain. Its a multiselect drop-down filter.

Code for multiselect drop-down.

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

How should I pass another token "ckey" for my text box filter on parent_Chain in the panel.

Can someone guide me on this.

Thanks in advance

Re: How to filter lookup data through a text box

aditsss — Mon, 14 Dec 2020 13:55:32 GMT

Can someone please guide me on this.

Re: How to filter lookup data through a text box

nickhills — Mon, 14 Dec 2020 14:01:19 GMT

Ok, I understand the question and the dashboard requirements (you don't need to keep posting it) but the first thing you need to do is get the basic search to work.

If the search returns no results you need to figure out why.
Does the first part of the query work?

If the first part works, then try adding the lookup:

Do you now have additional columns added from the lookup - do you now see a column called "parent_chain"?

When you have the necessary fields returned in your search you can move on to building the dashboard.

Re: How to filter lookup data through a text box

aditsss — Mon, 14 Dec 2020 14:13:10 GMT

@nickhills

This query is returning the result .

<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 |join type=outer id [inputlookup nifi_api_parent_chains.csv]|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>

I want to add my text box filter token in this working query. Can you guide me how can I add my text box filter token here.

This is code for my text box.

<input type="text" token="ckey" searchWhenChanged="true">
<label>Parent Chain</label>
<prefix>parent_chain="*</prefix>
<suffix>*"</suffix>
</input>

Re: How to filter lookup data through a text box

nickhills — Mon, 14 Dec 2020 14:27:46 GMT

Put:

|search $ckey$

at the end of your search

Re: How to filter lookup data through a text box

aditsss — Mon, 14 Dec 2020 17:21:58 GMT

@nickhills

I have tried below code for my text box filter:

Text box code:

<input type="text" token="ckey" searchWhenChanged="true">
<label>Parent Chain</label>
<prefix>parent_chain="*</prefix>
<suffix>*"</suffix>
</input>

Panel code:

<row>
<table>
<search>
<query>index=abc sourcetype=xyz source="/var/log/nifi/nifi-user.log" process-groups | convert timeformat="%Y-%m-%d" ctime(_time) AS Date |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 nifi_api_parent_chains.csv]|where $Teams$| search $ckey$</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>

when I am typing in text box its showing the result but it has break my first token "$Teams$" code.

Can you guide me where I have gone wrong.

Until unless I am not typing anything in the text box the panel is coming empty.

Can you guide me on this.

Teams drop-down code which is displaying the data:

Thanks is advance.

Re: How to filter lookup data through a text box

aditsss — Mon, 14 Dec 2020 17:25:09 GMT

Can someone please guide me on this.

Re: How to filter lookup data through a text box

nickhills — Mon, 14 Dec 2020 18:15:55 GMT

Please stop posting "Can someone please guide me on this" It is unnecessary and makes the thread difficult to follow.

Set a default and initial value on ckey to "*" That will allow your search to run without any input.

The teams input (which you have only just shared) is based on the results of the search and the value you are searching for with ckey.

Set teams to be :

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

remove the |where $Teams$ from the end of the search and make it just

| search $ckey$ $Teams$

Re: How to filter lookup data through a text box

aditsss — Tue, 15 Dec 2020 08:21:39 GMT

@nickhills

Thank you for the solution.