Solved: Re: Table shows no record if multiple values selec...

vikas_gopal · ‎02-20-2014

Hi Guys,
Plz Plz help me .I am preparing a dashboard which has a multiselect box and a table. On the basis of selected value table shows records.

Problem:- When I select multiple values in the multiselect box table shows no record it's fine with single value.Here is my XML code .

<dashboard script="test21.js , multi_value_token24.js">
<label>Multi Select</label>
<!-- Leverage row grouping to put the input in the panel -->
<row>
    <!-- Use an html panel to have a place for multi_select.js to put the multi select -->
    <html>
        <label>period_name</label>
        <div id="multi_value_input"/>
    </html> 
<table>
  <title>Multi Value Search</title>
      <searchString>| dbquery "Finance Database" "select  * from fa_deprn_periods where perioperiod_name like '$multiTokenQuery$'" </searchString>
   </table> 
 </row>
</dashboard>

I am not sure where could be the problem I mean is it with javaScript or with the query which fills data in the table..please suggest...

aelliott · ‎02-20-2014

you should probably do where perioperiod_name IN('$multiTokenQuery$')

Edit: try this instead:
WHERE CHARINDEX ( perioperiod_name,'$multiTokenQuery$') > 0

or this in oracle:
Instr('$multiTokenQuery$',perioperiod_name)

View solution in original post

aelliott · ‎02-20-2014

you should probably do where perioperiod_name IN('$multiTokenQuery$')

Edit: try this instead:
WHERE CHARINDEX ( perioperiod_name,'$multiTokenQuery$') > 0

or this in oracle:
Instr('$multiTokenQuery$',perioperiod_name)

vikas_gopal · ‎02-20-2014

it works fine with or period_name like '$multiTokenQuery$' Thanks for your effort and time....

aelliott · ‎02-20-2014

Actually you could say and '$multiTokenQuery$' != ''

vikas_gopal · ‎02-20-2014

Come to India Man wanna give u a Party It works Yipieee.....Just one thing left if the selection box is empty then table shows overall data...

aelliott · ‎02-20-2014

ah it would be Instr('$multiTokenQuery$',perioperiod_name)

vikas_gopal · ‎02-20-2014

Actually I am hitting to Oracle database so CharIndex is not working Splunk says "CharIndex: Invalid Identifier"..So I used Instr but still no result..:(

aelliott · ‎02-20-2014

I've modified my answer, while that way will probably work as well, you could use the CharIndex in the SQL query so then no javascript changes

vikas_gopal · ‎02-20-2014

U r absolutely correct I guess we are on same page now ..Here I have a question how to put single quotes around these two values so that query can read it .I guess JS file is handling that part ...JS file code
var setMultiTokenQuery = function(value){
//Handle the default cases, if we aren't passed a value, search everything
value = value.join('') || [];
if (value.length === 0) {
value.unshift("%");
}
alert(value);
//Produce the search query and set the token
var multiTokenQuery = value.join(',');

aelliott · ‎02-20-2014

It looks like the issue is that it returns 'Dec-13,Jan-14' instead of 'Dec-13','Jan-14' , which is correct sql syntax for the IN operator.

vikas_gopal · ‎02-20-2014

I did some research on JS file I mean I put an alert to chk the token value .It returns values like Dec-13,Jan-14 which is absolutely fine if I select these two values in box.These values should retrieve data with IN operator .As you suggest I need a SQL Profiler .....God know how I am going to solve this issue...

aelliott · ‎02-20-2014

Apparently I thought for some reason you were using web framework but that is not the case. You would need to find out what token value that splunk was sending your query via SQL Profiler or a like function.

vikas_gopal · ‎02-20-2014

Ahhh!! here is the problem then I don't know how to debug javaScript as I never worked on java Scripts actually I am preparing this dashboard with the help of Splunk 6 dashboard example.Can you help in JS if I paste my code here..?

aelliott · ‎02-20-2014

Can you debug your javascript? if so after everything is rendered, you could inspect the search object and find the query that is being run. Alternatively you could use SQL profiler to find out what query is being run to see exactly what is getting added into the token

vikas_gopal · ‎02-20-2014

@Aelliott Thanks for the quick response but I tried with IN as well even that did not work .Why I was using LIKE because with this table shows overall data if multiselect box is empty .Even that stops working after applying IN operator ......any other suggestion that u have plz...

Table shows no record if multiple values selected in the multiselect box

Stay Connected: Your Guide to May Tech Talks, Office Hours, and Webinars!

They're back! Join the SplunkTrust and MVP at .conf24

Enterprise Security Content Update (ESCU) | New Releases