Table row expansion with dynamic search in the JS

[spkriyaz]

Hi,

Below is the JS which I have for table row expansion which works fine for my requirement. I want the search given in the JS should be passed from my Splunk dashboard instead of hardcoding it here. This is because I don't want to update the JS whenever I want to add or delete anything in my search. 

Below search in JS which I want to be passed through Splunk dynamically instead of hardcoding in JS:

this._searchManager.set({ search: 'index=idx_omn source=vs.csv | eval key=process+" "+oprocess_start_date | search key="' + processCell.value + '" | stats values(process) as process values(oprocess_start_date) as oprocess_start_date values(oprocess_end_date) as oprocess_end_date values(otrans) as otrans values(otpm) as otpm values(oelapsed_mins) as oelapsed_mins values(total_count) as total_count values(local_currency) as local_currency values(local_swift) as local_swift values(local_amt) as local_amt values(eur_local) as eur_local BY file_number_list| table process,oprocess_start_date,oprocess_end_date,file_number_list,otrans,otpm,oelapsed_mins,total_count,local_currency,local_swift,local_amt,eur_local '});

XML:

<dashboard script="custom_table_row_expansion1.js">
  <label>Visa row expansion</label>
  <row>
    <panel>
      <table id="expand_with_events">
        <search>
          <query>index="idx_omn" source="vs.csv"
| eval key=process+" "+oprocess_start_date
| stats values(process) as process values(oprocess_start_date) as oprocess_start_date values(oprocess_end_date) as oprocess_end_date values(otrans) as otrans values(otpm) as otpm values(oelapsed_mins) as oelapsed_mins  sum(total_count) as total_count values(local_currency) as local_currency values(local_swift) as local_swift sum(local_amt) as local_amt sum(eur_local) as eur_local BY key
| table process oprocess_start_date oprocess_end_date otrans otpm oelapsed_mins total_count local_currency local_swift local_amt eur_local key</query>
          <earliest>0</earliest>
          <latest></latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</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>
    </panel>
  </row>
</dashboard>

JS:

require([
    'splunkjs/mvc/tableview',
    'splunkjs/mvc/chartview',
    'splunkjs/mvc/searchmanager',
    'splunkjs/mvc',
    'underscore',
    'splunkjs/mvc/simplexml/ready!'],function(
    TableView,
    ChartView,
    SearchManager,
    mvc,
    _
    ){

    var EventSearchBasedRowExpansionRenderer = TableView.BaseRowExpansionRenderer.extend({
        initialize: function(args) {
            // initialize will run once, so we will set up a search and a chart to be reused.
            this._searchManager = new SearchManager({
                id: 'details-search-manager',
                preview: false
            });
            //this._chartView = new ChartView({
            //    managerid: 'details-search-manager',
            //    'charting.legend.placement': 'none'
            //});
			this._TableView = new TableView({
                    id: 'TestTable',
                    managerid: 'details-search-manager',
                    drilldown: 'cell'
            });
        },

        canRender: function(rowData) {
            // Since more than one row expansion renderer can be registered we let each decide if they can handle that
            // data
            // Here we will always handle it.
            return true;
        },

        render: function($container, rowData) {
            // rowData contains information about the row that is expanded.  We can see the cells, fields, and values
            // We will find the key(process and start date) cell to use its value
            var processCell = _(rowData.cells).find(function (cell) {
               return cell.field === 'key';
            });
			

            //update the search with the key that we are interested in
            this._searchManager.set({ search: 'index=idx_omn source=vs.csv | eval key=process+" "+oprocess_start_date | search key="' + processCell.value + '" | stats values(process) as process values(oprocess_start_date) as oprocess_start_date values(oprocess_end_date) as oprocess_end_date values(otrans) as otrans values(otpm) as otpm values(oelapsed_mins) as oelapsed_mins values(total_count) as total_count values(local_currency) as local_currency values(local_swift) as local_swift values(local_amt) as local_amt values(eur_local) as eur_local BY file_number_list| table process,oprocess_start_date,oprocess_end_date,file_number_list,otrans,otpm,oelapsed_mins,total_count,local_currency,local_swift,local_amt,eur_local '});

            // $container is the jquery object where we can put out content.
            // In this case we will render our chart and add it to the $container
            //$container.append(this._chartView.render().el);
			$container.append(this._TableView.render().el);
        }
    });

    var tableElement = mvc.Components.getInstance("expand_with_events");
    tableElement.getVisualization(function(tableView) {
        // Add custom cell renderer, the table will re-render automatically.
        tableView.addRowExpansionRenderer(new EventSearchBasedRowExpansionRenderer());
    });
});

I did try by creating a field called "query" which has the query for the row expansion and passed it to the below JS but it didn't return any result. Please ignore this approach if there is any better solution available.

XML for passing the entire query to column called query:

<dashboard script="custom_table_row_expansion2.js">
  <label>Visa row expansion Clone</label>
  <row>
    <panel>
      <table id="expand_with_events">
        <search>
          <query>index="idx_omn" source="vs.csv"
| eval key=process+" "+oprocess_start_date
| eval query1 = "idx_omn source=\"visabase.csv\" | search key =\""
| eval query2 = "\"| stats values(process) as process values(oprocess_start_date) as oprocess_start_date values(oprocess_end_date) as oprocess_end_date values(otrans) as otrans values(otpm) as otpm values(oelapsed_mins) as oelapsed_mins values(total_count) as total_count values(local_currency) as local_currency values(local_swift) as local_swift values(local_amt) as local_amt values(eur_local) as eur_local BY file_number_list"
| eval query = query1+key+query2
| stats values(process) as process values(oprocess_start_date) as oprocess_start_date values(oprocess_end_date) as oprocess_end_date values(otrans) as otrans values(otpm) as otpm values(oelapsed_mins) as oelapsed_mins  sum(total_count) as total_count values(local_currency) as local_currency values(local_swift) as local_swift sum(local_amt) as local_amt sum(eur_local) as eur_local values(query) as query BY key
| table process oprocess_start_date oprocess_end_date otrans otpm oelapsed_mins total_count local_currency local_swift local_amt eur_local key query</query>
          <earliest>0</earliest>
          <latest></latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</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>
    </panel>
  </row>
</dashboard>

JS for the new try which didn't redturn any result:

require([
    'splunkjs/mvc/tableview',
    'splunkjs/mvc/chartview',
    'splunkjs/mvc/searchmanager',
    'splunkjs/mvc',
    'underscore',
    'splunkjs/mvc/simplexml/ready!'],function(
    TableView,
    ChartView,
    SearchManager,
    mvc,
    _
    ){

    var EventSearchBasedRowExpansionRenderer = TableView.BaseRowExpansionRenderer.extend({
        initialize: function(args) {
            // initialize will run once, so we will set up a search and a chart to be reused.
            this._searchManager = new SearchManager({
                id: 'details-search-manager',
                preview: false
            });
            //this._chartView = new ChartView({
            //    managerid: 'details-search-manager',
            //    'charting.legend.placement': 'none'
            //});
			this._TableView = new TableView({
                    id: 'TestTable',
                    managerid: 'details-search-manager',
                    drilldown: 'cell'
            });
        },

        canRender: function(rowData) {
            // Since more than one row expansion renderer can be registered we let each decide if they can handle that
            // data
            // Here we will always handle it.
            return true;
        },

        render: function($container, rowData) {
            // rowData contains information about the row that is expanded.  We can see the cells, fields, and values
            // We will find the sourcetype cell to use its value
            var processCell = _(rowData.cells).find(function (cell) {
               return cell.field === 'query';
            });
			

            //update the search with the sourcetype that we are interested in
            this._searchManager.set({ search: 'index= "' + processCell.value + '" '});

            // $container is the jquery object where we can put out content.
            // In this case we will render our chart and add it to the $container
            //$container.append(this._chartView.render().el);
			$container.append(this._TableView.render().el);
        }
    });

    var tableElement = mvc.Components.getInstance("expand_with_events");
    tableElement.getVisualization(function(tableView) {
        // Add custom cell renderer, the table will re-render automatically.
        tableView.addRowExpansionRenderer(new EventSearchBasedRowExpansionRenderer());
    });
});

 
Thanks,

[kamlesh_vaghela]

@spkriyaz 

I tried you 2nd approach and after doing some correction it's working. 

require([
    'splunkjs/mvc/tableview',
    'splunkjs/mvc/chartview',
    'splunkjs/mvc/searchmanager',
    'splunkjs/mvc',
    'underscore',
    'splunkjs/mvc/simplexml/ready!'
], function(
    TableView,
    ChartView,
    SearchManager,
    mvc,
    _
) {
    console.log("HIe");
    var EventSearchBasedRowExpansionRenderer = TableView.BaseRowExpansionRenderer.extend({
        initialize: function(args) {
            // initialize will run once, so we will set up a search and a chart to be reused.
            this._searchManager = new SearchManager({
                preview: false
            }, { tokens: true, tokenNamespace: "submitted" });
            //this._chartView = new ChartView({
            //    managerid: 'details-search-manager',
            //    'charting.legend.placement': 'none'
            //});
            this._TableView = new TableView({
                managerid: this._searchManager.name,
                drilldown: 'cell'
            });
        },

        canRender: function(rowData) {
            // Since more than one row expansion renderer can be registered we let each decide if they can handle that
            // data
            // Here we will always handle it.
            return true;
        },

        render: function($container, rowData) {
            // rowData contains information about the row that is expanded.  We can see the cells, fields, and values
            // We will find the sourcetype cell to use its value
            var processCell = _(rowData.cells).find(function(cell) {
                return cell.field === 'query';
            });


            //update the search with the sourcetype that we are interested in
            // this._searchManager.set({ search: 'index= "' + processCell.value + '" ' });
            this._searchManager.set({ search: '| makeresults count=5 | eval query="' + processCell.value + '" ' });

            // $container is the jquery object where we can put out content.
            // In this case we will render our chart and add it to the $container
            //$container.append(this._chartView.render().el);
            $container.append(this._TableView.render().el);
        }
    });

    var tableElement = mvc.Components.getInstance("expand_with_events");
    tableElement.getVisualization(function(tableView) {
        // Add custom cell renderer, the table will re-render automatically.
        tableView.addRowExpansionRenderer(new EventSearchBasedRowExpansionRenderer());
        tableView.render();
    });
});

Can you please try this?

KV

[spkriyaz]

Hi @kamlesh_vaghela,

I changed the search like below in your JS and tried. It didn't work. Still the row expand shows "No result found"

//update the search with the sourcetype that we are interested in
this._searchManager.set({ search: 'query="' + processCell.value + '" ' }); 

Thanks,

[kamlesh_vaghela]

@spkriyaz 

Can you please console the search and try this?

console.log(processCell.value)
this._searchManager.set({ search: processCell.value });

[spkriyaz]

Thanks @kamlesh_vaghela,

I could find there was an issue with the search I corrected it and the could see the value in the expanded row.

But, I have one more issue like the "query" column is getting populated in the parent table. If I try to hide using the fields option or css then "query" column won't be available for the JS to do the row expand. Is there any way to hide the "query" column from the parent table and make the row expansion work?

[kamlesh_vaghela]

@spkriyaz 

Can you please try this?

require([
    'splunkjs/mvc/tableview',
    'splunkjs/mvc/chartview',
    'splunkjs/mvc/searchmanager',
    'splunkjs/mvc',
    'underscore',
    'splunkjs/mvc/simplexml/ready!'
], function(
    TableView,
    ChartView,
    SearchManager,
    mvc,
    _
) {
    console.log("HIe");
    var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
        canRender: function(cell) {
            return cell.field;
        },
        render: function($container, rowData) {
            if (rowData.field === "key") {
                $container.html(rowData.value.split("@@")[0])
            } else {
                $container.html(rowData.value)
            }
        }
    });
    var EventSearchBasedRowExpansionRenderer = TableView.BaseRowExpansionRenderer.extend({
        initialize: function(args) {
            // initialize will run once, so we will set up a search and a chart to be reused.
            this._searchManager = new SearchManager({
                preview: false
            }, { tokens: true, tokenNamespace: "submitted" });
            //this._chartView = new ChartView({
            //    managerid: 'details-search-manager',
            //    'charting.legend.placement': 'none'
            //});
            this._TableView = new TableView({
                managerid: this._searchManager.name,
                drilldown: 'cell'
            });
        },

        canRender: function(rowData) {
            // Since more than one row expansion renderer can be registered we let each decide if they can handle that
            // data
            // Here we will always handle it.
            return true;
        },

        render: function($container, rowData) {
            // rowData contains information about the row that is expanded.  We can see the cells, fields, and values
            // We will find the sourcetype cell to use its value
            var processCell = _(rowData.cells).find(function(cell) {
                return cell.field === 'key';
            });


            //update the search with the sourcetype that we are interested in
            // this._searchManager.set({ search: 'index= "' + processCell.value + '" ' });
            processCell.value = processCell.value.split("@@")[1]
            console.log(processCell.value)
            this._searchManager.set({ search: processCell.value });

            // $container is the jquery object where we can put out content.
            // In this case we will render our chart and add it to the $container
            //$container.append(this._chartView.render().el);
            $container.append(this._TableView.render().el);
        }
    });

    var tableElement = mvc.Components.getInstance("expand_with_events");
    tableElement.getVisualization(function(tableView) {
        // Add custom cell renderer, the table will re-render automatically.
        tableView.table.addCellRenderer(new CustomRangeRenderer());
        tableView.render();
        tableView.addRowExpansionRenderer(new EventSearchBasedRowExpansionRenderer());
    });
});

You also need to add following block at the end of  your table search,

| eval key=key."@@".query | fields - query

Thanks
KV
▄︻̷̿┻̿═━一   ?

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[spkriyaz]

Thanks @kamlesh_vaghela,

I did find a solution to get rid of the key column, I replaced the key with the process in the JS.  Since, process column is required in the parent table now process will act as a click event and make the row expansion work as I expected. 

I found a bug. When I try to expand the row in a sequence it works fine but when I try to expand the row which I already expanded once then I am getting error. For example, I have 3 rows 1,2 and 3. If I click on 1 then 2 then 3 and again if I click on 1 it throws below error. The loop doesn't work.

[kamlesh_vaghela]

@spkriyaz 

You can concat query field with any field instead of key which is visible in table and use in JS. It will work 🙂 

[spkriyaz]

Yes, I did that to get rid of the query and key column from the parent table. But, there is bug which I mentioned with the screenshot. Did you get a chance to see my screenshot? When I flip back to the same row which I opened earlier this error pops out.

[kamlesh_vaghela · Accepted solution]

@spkriyaz 

Can you please try this?

require([
    'splunkjs/mvc/tableview',
    'splunkjs/mvc/chartview',
    'splunkjs/mvc/searchmanager',
    'splunkjs/mvc',
    'underscore',
    'splunkjs/mvc/simplexml/ready!'
], function(
    TableView,
    ChartView,
    SearchManager,
    mvc,
    _
) {
    console.log("HIe");
    var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
        canRender: function(cell) {
            return cell.field;
        },
        render: function($container, rowData) {
            console.log(rowData.value);
            if (rowData.field === "key") {
                $container.html(rowData.value.split("@@")[0])
            } else {
                $container.html(rowData.value)
            }
        }
    });
    var EventSearchBasedRowExpansionRenderer = TableView.BaseRowExpansionRenderer.extend({
        initialize: function(args) {
            // initialize will run once, so we will set up a search and a chart to be reused.
            this._searchManager = new SearchManager({
                preview: false
            }, { tokens: true, tokenNamespace: "submitted" });
            //this._chartView = new ChartView({
            //    managerid: 'details-search-manager',
            //    'charting.legend.placement': 'none'
            //});
            this._TableView = new TableView({
                managerid: this._searchManager.name,
                drilldown: 'cell'
            });
        },

        canRender: function(rowData) {
            // Since more than one row expansion renderer can be registered we let each decide if they can handle that
            // data
            // Here we will always handle it.
            return true;
        },

        render: function($container, rowData) {
            // rowData contains information about the row that is expanded.  We can see the cells, fields, and values
            // We will find the sourcetype cell to use its value
            var processCell = _(rowData.cells).find(function(cell) {
                return cell.field === 'key';
            });

                //update the search with the sourcetype that we are interested in
                // this._searchManager.set({ search: 'index= "' + processCell.value + '" ' });
            value = processCell.value.split("@@")
            value = value[value.length - 1];
            this._searchManager.set({ search: value });

            // $container is the jquery object where we can put out content.
            // In this case we will render our chart and add it to the $container
            //$container.append(this._chartView.render().el);
            $container.append(this._TableView.render().el);
        }
    });

    var tableElement = mvc.Components.getInstance("expand_with_events");
    tableElement.getVisualization(function(tableView) {
        // Add custom cell renderer, the table will re-render automatically.
        tableView.table.addCellRenderer(new CustomRangeRenderer());
        tableView.render();
        tableView.addRowExpansionRenderer(new EventSearchBasedRowExpansionRenderer());
    });
});

[spkriyaz]

It's perfect now 😃 Thank you @kamlesh_vaghela