Dashboards & Visualizations

How to filter lookup data through a text box

aditsss
Builder

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 "(?&lt;id&gt;[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.

Labels (6)
0 Karma
1 Solution

nickhills
Ultra Champion

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$

 

 

 

If my comment helps, please give it a thumbs up!

View solution in original post

nickhills
Ultra Champion

Put:

|search $ckey$

at the end of your search

If my comment helps, please give it a thumbs up!
0 Karma

aditsss
Builder

@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 "(?&lt;id&gt;[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:

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

Thanks is advance.

0 Karma

aditsss
Builder

Can someone please guide me on this.

0 Karma

nickhills
Ultra Champion

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$

 

 

 

If my comment helps, please give it a thumbs up!

View solution in original post

aditsss
Builder

@nickhills 

Thank you for the solution.

0 Karma

nickhills
Ultra Champion

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?

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

If the first part works, then try adding the 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
|lookup parent_chains.csv id

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.


If my comment helps, please give it a thumbs up!
0 Karma

aditsss
Builder

@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 "(?&lt;id&gt;[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>

0 Karma

nickhills
Ultra Champion

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?

If my comment helps, please give it a thumbs up!
0 Karma

aditsss
Builder

@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 "(?&lt;id&gt;[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

0 Karma

nickhills
Ultra Champion

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:

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 parent_chain=*

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

 

If my comment helps, please give it a thumbs up!
0 Karma

aditsss
Builder

@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 "(?&lt;id&gt;[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, &quot;%BLAZE%&quot;)">BLAZE</choice>
<choice value="like(parent_chain, &quot;%Oneforce%&quot;)">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

0 Karma

aditsss
Builder

Can someone please guide me on this.

0 Karma

aditsss
Builder
Can someone please guide me on this.
0 Karma

aditsss
Builder

Can someone please guide me on this.

0 Karma

aditsss
Builder

Can someone please look into my issue.

0 Karma

aditsss
Builder

Can someone please guide me on my issue.

0 Karma

aditsss
Builder

Can someone please guide me on this.

0 Karma

aditsss
Builder
Can someone please guide me on this.
0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.