Dynamically Editing Lookup Tables

From Splunk Wiki

Jump to: navigation, search

--Kmattern 10:04, 8 April 2013 (PDT)

Background

In our business we use a lot of lookup tables. Most of them are static but a few need to be updated on a regular basis. The maintenance of those lookup tables can be a real chore. The server they live on is on a secure network and our team has no direct access to the machine except through the Splunk interface. This makes it very difficult to do many things. Currently, to edit a lookup table we do the following.

  • Run an inputlookup search on the file and export it to Excel
  • Edit the table in Excel and save it locally
  • From the Splunk manager, delete the existing lookup table
  • Upload the edited version
  • Set the permissions so that all can use it

This is cumbersome but it works. However, if the lookup lives in /etc/system/lookups we cannot upload it but, instead, have to pass the edited file to a system administrator and have them manually copy the file to the destination folder. That can take up to a day, or more, and that is not acceptable.


A Solution

This wiki article will discuss a system that I created that allows the on-line and dynamic editing of lookup tables in the etc/system/lookups folder. All it requires is two advanced XML forms, Sideview Utils and a lookup table.

The first form lists the contents of the table to be edited and, through Sideview Utils, has drilldown capability. When a row on the table is clicked the fields from that row are passed to the second form which populates text boxes with the selected fields and displays the contents of the table as well. Any of the fields may be edited, except for the key field. When the update button is clicked the lookup table is updated and the first form displays the edited table.

How it Works

Let’s start with a simple lookup table listing clients, their locations and their current status. A search on this client list is run daily and any client that is not “In Transit” or “Processing” should be monitored. If the status changes it must be updated in the lookup table. A sample lookup table named Client-List.csv might look like this. It would be maintained in the etc/system/lookups folder.

clientidnamecitystatestatus
jstrongJohn StrongNew YorkNYIn Transit
kwollKarl WollRaleighNCProcessing
matwoodMark AtwoodHarrisburgPAUNK
ctrappCarl TrappAthensALIn Transit
ebakerEd BakerBristolTNIn Transit
lsantosLaurel SantosBozmanMTUNK
vlarsenVic LarsenRoanoakVAProcessing
jsmithJerry SmithSalisbutyMDLeave

The XML

This example uses Advanced XML and Sideview UTILS. If you are not familiar with Advanced XML you should review the documentation found here: http://docs.splunk.com/Documentation/Splunk/5.0.2/AdvancedDev/AdvancedIntro. If you aren’t familiar with Sideview Utils I would strongly recommend downloading the app and reading the excellent documentation.

The List Table Form

The ListTable.XML form is a very simple form utilizing a HiddenSearch, a Table and a Redirector module. These examples do not include the view's header information, such as appHeader, viewHeader modules. The HiddenSearch Module

  <module name="HiddenSearch" layoutPanel="panel_row1_col1" group="Lookup Contents" autoRun="true">
   <param name="search"> | inputlookup Client-List.csv    | table clientid, name, city, state, status</param>

Note that this module encloses everything to follow

The next module is the Table module and consists of only one line

           <module name="Table">

And is followed by the Redirector module. This is where the magic of Sideview Utils comes into play. We’re making a drilldown table here with a minimum of fuss. The redirector passes all of the table fields to the second form where the field edits will take place. The second form will be named TableEditor.XML

         <module name="Redirector">
           <param name="url">TableEditor</param>
           <param name="arg.clientid">$row.fields.clientid$</param>
           <param name="arg.name">$row.fields.name$</param>
           <param name="arg.city">$row.fields.city$</param>
           <param name="arg.state">$row.fields.state$</param>
           <param name="arg.status">$row.fields.status$</param>
          </module>
         </module>

Breaking down the Redirector module we begin with the url param. This contains the name of the second form, TableEditor.XML.

The following set of fields name the arguments that are passed to TableEditor.XML and the value of the argument. Note that each argument is named to match the field name in the lookup table. This makes it easy to know which field is which.

That’s it! This is all that is needed to display the contents of the lookup table and make it drillable.

The Edit Table Form

The second form, TableEditor.XML is a bit more complex but won’t be difficult to understand. It uses the following modules, URLLoader, Search, TextField, Button, Table and Redirector. Again header information and extraneous XML won’t be displayed here.

Let’s begin with the URLLoader module. All the modules listed below are wrapped inside this module.

  <module name="URLLoader" layoutPanel="panel_row1_col1" autoRun="True">

Next up is the Search module. This module simply loads the lookup table for the selected clientid so that the TextFields will be populated

  <module name="Search" layoutPanel="panel_row1_col1" autoRun="false">
   <param name="search">| inputlookup Client-List.csv | search clientid=$clientid$</param>

After the search completes it will load the fields of the selected row into the TextFields.

<module name="TextField" layoutPanel="panel_row1_col1" autoRun="false">
   <param name="name">clientid</param>
   <param name="float">left</param>
   <param name="template">$clientid$</param>
   <param name="label">Client ID</param> 
 <module name="TextField" layoutPanel="panel_row1_col1" autoRun="false">
   <param name="name">name</param>
   <param name="float">left</param>
   <param name="template">$name$</param>
   <param name="label">Name</param> 
 <module name="TextField" layoutPanel="panel_row1_col1" autoRun="false">
   <param name="name">city</param>
   <param name="float">left</param>
   <param name="template">$city$</param>
   <param name="label">City</param> 
 <module name="TextField" layoutPanel="panel_row1_col1" autoRun="false">
   <param name="name">state</param>
   <param name="float">left</param>
   <param name="template">$state$</param>
   <param name="label">State</param> 
 <module name="TextField" layoutPanel="panel_row1_col1" autoRun="false">
   <param name="name">status</param>
   <param name="float">left</param>
   <param name="template">$status$</param>
   <param name="label">status</param>

At this point the lookup table contents will be displayed in full below the TextFields and the Update button. The next module is the button module.

 <module name="Button" layoutPanel="panel_row1_col1" autoRun="false">
   <param name="label">Update</param>

And that is followed by the search that updates the table. The real magic here is in the use of eval and if statements. In essence the search loads the lookup table and changes the output of only the row selected for editing. Everything else passes right through the filter. At the end the table is saved, overwriting itself.

Now the search

The only field that cannot be edited is the clientid, which is the key to the table.

     <module name="Search" autoRun="false">
       <param name="search"> 
| inputlookup Client-List.csv
| eval name=if(clientid="$clientid$","$name$",name) 
| eval city=if(clientid="$clientid$","$city$",city)
| eval state=if(clientid="$clientid$","$state$",state)
| eval status=if(clientid="$clientid$","$status$",status)
| table clientid, name, city, state, status
| outputlookup Client-List.csv createinapp=False
       </param>

The search is followed by the Table and Redirector module. Note the URL in the Redirector module. It points back to the first form,ListTable.XML

     <module name="Table">
       <param name="count">500</param>
     </module>
     <module name="Redirector">
 	<param name="url">ListTable</param>
     </module>

The view is closed off with the closing module tag for the search, each of the TextFields and the Button modules.

That’s it. Try it and build on it to handle your lookup table editing needs. Just remember that you need Sideview Utils to make it work.

Personal tools
Hot Wiki Topics


About Splunk >
  • Search and navigate IT data from applications, servers and network devices in real-time.
  • Download Splunk