Hi guys,
I am running a search based on an SQL query. I would like to extract field names only from the search and populate a multi select input element with all the field names from a table. A user will then select which fields they want to view on a table on another panel. Am trying to achieve a situation where someone can add and remove columns on a table below. Please assist as I am completely stuck.
Thanks
Hey guys
I managed to find a solution to to it after chewing on it for a while. Its actually quite a simple procedure:
To get the fieldnames for any given search I used this:
<my search> | stats dc() as *
This will give you a single row with one column for every field, where the cell values are the distinct counts. It creates a table on the fly. To flip the table around:
<my search> | stats dc() as * | transpose
This creates a table with a single column called 'column' that contains a list of all field names.
From this table I populated the multiselect element using this table by setting the required parameters ie the column name. This table is invisible. However to view it, for checking purposes you will need to create a panel for it. Anyway the code worked for me.
<input type="multiselect" token="sourcetype" searchWhenChanged="true">
<label>Click to Add / Remove Columns</label>
<populatingSearch fieldForValue="column" fieldForLabel="column"> | dbquery "select * from performance" | stats dc() as * | transpose</populatingSearch>
<fieldForLabel>sourcetype</fieldForLabel>
<fieldForValue>sourcetype</fieldForValue>
<prefix>(</prefix>
<suffix>)</suffix>
<valuePrefix>sourcetype="</valuePrefix>
<valueSuffix>"</valueSuffix>
<delimiter>OR</delimiter>
<choice value="*">All</choice>
<default>*</default>
</input>
Cheers
Hey guys
I managed to find a solution to to it after chewing on it for a while. Its actually quite a simple procedure:
To get the fieldnames for any given search I used this:
<my search> | stats dc() as *
This will give you a single row with one column for every field, where the cell values are the distinct counts. It creates a table on the fly. To flip the table around:
<my search> | stats dc() as * | transpose
This creates a table with a single column called 'column' that contains a list of all field names.
From this table I populated the multiselect element using this table by setting the required parameters ie the column name. This table is invisible. However to view it, for checking purposes you will need to create a panel for it. Anyway the code worked for me.
<input type="multiselect" token="sourcetype" searchWhenChanged="true">
<label>Click to Add / Remove Columns</label>
<populatingSearch fieldForValue="column" fieldForLabel="column"> | dbquery "select * from performance" | stats dc() as * | transpose</populatingSearch>
<fieldForLabel>sourcetype</fieldForLabel>
<fieldForValue>sourcetype</fieldForValue>
<prefix>(</prefix>
<suffix>)</suffix>
<valuePrefix>sourcetype="</valuePrefix>
<valueSuffix>"</valueSuffix>
<delimiter>OR</delimiter>
<choice value="*">All</choice>
<default>*</default>
</input>
Cheers
I still haven't got a solution yet. Here is the code that I have tried. There is no error but nothing happens as well.
<input type="multiselect" token="sourcetype" searchWhenChanged="true">
<label>Click to Add / Remove Columns</label>
<search>
<query> | dbquery AdWordsROI limit=1000 "select * from account_performance" | stats dc() as * </query>
</search>
<fieldForLabel>sourcetype</fieldForLabel>
<fieldForValue>sourcetype</fieldForValue>
<prefix>(</prefix>
<suffix>)</suffix>
<valuePrefix>sourcetype="</valuePrefix>
<valueSuffix>"</valueSuffix>
<delimiter>OR</delimiter>
<choice value="*">All</choice>
<default>*</default>
</input>
Regards