Tutorial: Let others work for you – Give them their data!

Recently I had a great discussion with some folks from the communbeach_chiar_pc_800_clr_3539ity –  they told me that in most cases the Splunkers within an organization are the ones with the best visibility and inform other departments about issues/problems/breaches. As a result their peers in the organizations want to have the same information advantage and visibility. However, often they are just interested in their own systems and services, not from others within a large organization.

What’s the easiest and fastest approach to give them the visibility they want?

The Answer: Lookups and drop down menus

Based on the Qualys App for Splunk Enterprise I’ll explain to you how you can modify an existing dashboard that shows all vulnerabilities and how to, for example, filter the view just for Domain Controllers in your network. This enables you to give the dashboards to the relevant owners and they can start working/fixing/patching immediately rather than you needing to act as middle man. You’ll become a manager looking for things that haven’t patched in a given time frame and chasing them – friendly of course 😉

The raw event

My events look as follows:

HOSTVULN: HOST_ID=99639765, IP="", TRACKING_METHOD="IP", OS="Solaris 9-10", DNS="demo5.sea.qualys.com", LAST_SCAN_DATETIME="2015-07-12T01:16:16Z", SEVERITY=2, QID="86473", TYPE="CONFIRMED", PORT="898", PROTOCOL="TCP", SSL="0", STATUS="ACTIVE", FIRST_FOUND_DATETIME="2015-05-30T23:07:07Z", LAST_FOUND_DATETIME="2015-07-11T23:09:07Z", LAST_TEST_DATETIME="2015-07-11T23:09:07Z", LAST_UPDATE_DATETIME="2015-07-12T01:16:16Z"
I decide that the IP attribute in my event is for me the indicator of who is the owner or department that is responsible for that device.

Creating a CMDB Lookup Table

cmdbFirst we need information about which system belongs to which department or owner. You need to create that or pull it down from an existing information platform. I simply created a list in text editor. The first attribute is my IP and the other attributes can be whatever you want. In my case I added Owner and Department. You might additionally add things like phone numbers, e-mail addresses, location information, latitude and longitude (yeah then you can put it on a map! even internal IPs!).


Upload the lookup table file

Bildschirmfoto 2015-07-14 um 10.56.07Now it’s time to get into your Splunk instance. Within Settings -> Lookups you can create a new Lookup table file. Select the destination app – that will be the location where the lookup table will be stored – in case you want to update the file later via an automated third party process. In my case the file path will be  /$Splunk_Directory$ / etc / apps / Splunk_TA_qualys / lookups / cmdb.csv


HINT: Once done, make sure the configuration is set for sharing to global in the permissions so the lookup list can be used from everyone in every app context on your Splunk instance. By default it’s private – in case something is not working – check that first ;-).

Create the lookup definition

NoBildschirmfoto 2015-07-14 um 11.04.33w you have to create the lookup definition. It’s in the same lookup menu within Settings.  Just select the app context, choose a name for the lookup and select your lookup csv. Later you can use this to change to a new version of a lookup table if you upload a new one rather then overwriting the existing one.

Testing the lookup

Bildschirmfoto 2015-07-14 um 11.07.09You can already test  your lookup and load the file into Splunk to display. You can use the | inputlookup command for that.

| inputlookup cmdb_lookup

HINT: In case you don’t get any results – check the sharing permission for the two configurations you made and check that there is no odd csv formatting of the lookup table (open with text editor).


Bildschirmfoto 2015-07-14 um 11.32.04You can start searching for data and enriching the data with the lookup command:

index=qualys sourcetype=”qualys_vm_detection” | lookup cmdb_lookup IP

We can now see the mapping and distribution of how many events we have per department correlated through the lookup table.


Automating the lookup

Bildschirmfoto 2015-07-14 um 11.35.07As I’m lazy, I don’t always want to add  | lookup cmd_lookup IP behind my search – so it’s easy to set it up as automated lookup. So if I’m doing a search I don’t need to worry about lookups and as the lookup table is small it has no performance impact anyway in my case. Within the Setting / Lookups menu add an “Automatic Lookup” – just fill in the parameter of the lookup table. The input field is our IP field that is also in the lookup list, called IP.


Cloning the Dashboard

I like the “Search for Vulnerabilities” dashboard in the Qualys App. It’s simple, straightforward and gives me all information of specific vulnerabilities on my hosts with some filtering capabilities.

Bildschirmfoto 2015-07-14 um 11.37.55First we clone this dashboard so we have our own. That can be done via the gui – just select “Edit” in the Dashboard Editor and “Clone” the dashboard.

Add Dropdown Inputs to the dashboard

Bildschirmfoto 2015-07-14 um 11.43.57First we add a dropdown input to the dashboard. We label it as department and the token will be called departement. Additionally we set a token prefix and suffix with ” to make sure the department value is fully interpreted if it contains spaces or any other special characters.

We also set a static parameter called “All” with a value of * – we will take this as the default value if nothing is set so the dashboard will load without any filters.

Bildschirmfoto 2015-07-14 um 11.44.03

In the dynamic options – we will build out the drop down based on a search. This eliminates any maintenance if new values (Departement names) occur through the lookup. We’ll do a de duplication of the departments to make sure we just have one entry for each.


We need to do the same drop down for our Owners.

Once finished it should look like this:

Bildschirmfoto 2015-07-14 um 11.48.40


We now have drop downs with Departments and Owners – with a static value of All which is a * and the names of the Departements and Owners defined.


Configure the tokens in the dashboard search

The drop downs just sit there at the moment – so if you click them, they have no impact on the report shown. They are not yet linked to the search. That is what we are going to add now. We are going to edit the search string of the table on the dashboard.

Original One:
index=qualys HOSTVULN $type$ $t_type$ | dedup HOST_ID, QID | lookup qualys_kb_lookup QID                         $patch$ | search                         TITLE="*$vuln_token$*" OR QID="$vuln_token$" | eval                         Found=strftime(strptime(FIRST_FOUND_DATETIME,"%Y-%m-%d"),"%m/%d/%Y") | stats list(TITLE) as                         Title, by QID, IP, DNS, OS, Found, STATUS
New / Edited:
index=qualys HOSTVULN $type$ $t_type$ Departement=$departement$ Owner=$owner$ | dedup HOST_ID, QID | lookup qualys_kb_lookup QID                         $patch$ | search                         TITLE="*$vuln_token$*" OR QID="$vuln_token$" | eval                         Found=strftime(strptime(FIRST_FOUND_DATETIME,"%Y-%m-%d"),"%m/%d/%Y") | stats list(TITLE) as                         Title, by QID, IP, DNS, OS, Found, STATUS,Owner,Departement

In the beginning we are adding the token’s with Departement=$department$ and Owner=$owner$ – these represent the values selected in the drop down menu. remember – default is All which is equal to * to make sure if nothing is selected, it shows simply everything. Based on that configuration you can even select a combination of Department and Owner. 

The later addition of Owner and Department to the stats list command is to display in a column the Owner and Department information next to vulnerability information.

Bildschirmfoto 2015-07-14 um 12.04.08

Now you have added context to your machine data and you can send the dashboard out to the teams so they can start to work on the information provided in the dashboards and you can sit back, and have a coffee!

Because Ninja’s are too busy!

Happy Splunking,