Using a Lookup Table in Splunk

hi Travis with Splunk here

in this video I want to go over look up

tables and give you an example of how I

use lookup tables

I've pulled up a search here that shows

the you know activity of the different

devices on my home network

I can see there is a spike in data and

instead of me having to remember you

know the IP address

of that device I can have a lookup table

translate that IP to a host name so that

when I hover over this Spike of data

you know I get a name instead of an IP

address

and this is very helpful because I don't

want to remember all the IP addresses

for all the 30 plus devices that are you

know in my house or in my home

environment

so if you're new to Splunk or you're

sitting here going look up tables why

are they important what are you talking

about Travis let's go to your favorite

search engine whatever you want to use

and do a search on Splunk lookup tables

give you ways to find more information

and use our documentation I find you

know doing a search in your favorite

search engine

is the easiest way to find stuff in our

documentation so the first result is a

lookup command

um I am using that lookup command in

this search

and then if we go back here the second

one is about lookups and then there's

other you know lookup command examples

there is you know how to use lookup

table the you know Splunk Community

Splunk answers but I'm going to go into

this about lookups Splunk documentation

and show you more information about the

lookup table

here you know what is a lookup way to

enrich your data that you are collecting

you know the four types of lookup CSV

external KV store and even Geo

and then how you know more information

about each one of those four types of

lookup tables I'm going to focus on CSV

today

and here we have a link to you know how

can I create and bring a lookup table

into Splunk using the the web GUI or if

you like

you know using the configuration file

CLI you know there's a link for that but

for today we are going to go into

this link here which defines a CSV

lookup gives you more information about

that CSV file and then how to upload

that file and if you need an example of

a lookup table you know we have see look

up for an example you know this lookup

is a hyperlink

and we can drill down even further and

see examples of a lookup table

an example that we provide is a HTTP

status code I say we Splunk

and you can go ahead and download that

so you can see it or just review the

sample that Splunk has provided where it

shows the header field you know status

comma status description comma status

type

and then values that are associated with

the header field and it's all comma

separated and no spaces

so you can see like 200 okay and

successful and you know three different

header fields

and then the steps two

go ahead and

uh add those lookup tables into your

Splunk web

so let's take one step back here

you know in here you know more

information about lookup tables and how

to get that in there

so just take some time and go through

all of this

you know I could probably spend an hour

on lookup tables

but what I'm going to do is also you

know scroll down here because there's

something else I want to show this is

back to the about lookups and if I

scroll down you know more lookup table

definition automatic lookups this is

great so instead of having to invoke

that lookup command and during the

search I can go ahead and set up an

automatic lookup

that will be invoked at search time and

bring that information in that you would

need

so last thing I'm going to talk about on

this page is commands and lookups there

is three commands that are related to

lookup tables

I've already shown and

look up but there's also input lookup

and output lookup

so you can manually create your lookup

file or we can actually use the output

lookup in a Splunk search to create a

lookup table

to get that information into Splunk so

you can use it with other Splunk

searches and I will go over and we're

going to build that out today

so let's back up

here's that where I'm using the lookup

command there

with this lookup table

we have a couple different ways of be

able to look at what data is in that

lookup table at CSV file and that's that

input lookup command is one way

and then there's an app that you can

download so let me show off the input

lookup command real quick so input look

up and you can see I've already used

this command before

and before I go any further

if you like how you know I get you know

I'll click input lookup if you like how

I'm getting a lot of information over

here and if you're not getting this much

information like when I click more

you'll go up to administrator or you

know whoever you're logged in as your

user account name go to preferences

and then SPL editor and you can change

this on your account for your preference

it's where it you know Splunk by default

will have it on Compact and you can

select full

and then uh if you've ever noticed when

I hit the pipe

it drops down a new line that's this

search auto format so I select it so it

automatically drops a new line every

time and you'll probably see that here

in a minute so I'm going to go ahead and

hit cancel so I have input lookup and

what was that Hall yep

I've already got it there so I'll just

click on that and click run

so all this command does is bring the

data into a Splunk search so I can view

it

this is a CSV file that I have uploaded

I have edited and made adjustments to it

and this is a CSV file that is being

used in this search to where my

destination IP will go down here if it

makes a match it outputs me the hostname

now the other way that we can edit this

file

is an app and do I have that up nope so

we'll go here apps

and we're going to go to Splunk app for

lookup file

and this is an app that I've downloaded

off of Splunk base

if you've never I'll back up or before I

go too much further if you've never

heard of Splunk base this is you know

our

App Store

and we can either you know go to

splunkbase.splunk.com

and do a search in here for lookup

um file there it is look up file editing

or just you know back at your favorite

search engine Splunk base lookup editor

and you'll get links to the same

location

I will I will point out with the new

Splunk base we are

Splunk is you know providing a new

Splunk base over the old one

sometimes if I were to just put

look up you may not see that information

you know that app down here and even if

I run a search

you may not see it so make sure to put

in lookup file

if you go to the old Splunk base

you know if I type in look up there it's

the first entry so hopefully our product

team is working on or whoever's working

on the website is you know adjusting

that

and then the last way that we could you

know bring in that lookup app is to go

to apps

find more apps

and then the same thing here look up

and if I type in let's say edit

there it is

probably any other I just didn't feel

like scrolling down but here you know

you can just install that way if your

Splunk environment is internet

capable

I worked in an environment that that was

not the case

so now let's talk about the output

lookup command and how to use it

and I'm actually going to go back into

here

I want to show

DHCP so here you can see that lookup

this is that app for Splunk

for lookup file editing I am filtering

all of my you know there is a lot more

I'll back up there is a lot of lookup

tables that are loaded in my environment

I am using the Splunk Security

Essentials app it's a free app that you

can also download from Splunk base

you know if you are in that security

business please check it out there's one

for compliance there's one it uh

Essentials so we have a lot of good apps

out there to help you get going

but here I'm going to go

DHCP and you can see the the one CSV

that I have right now

and what we're going to do here is a

base search that has given me the IP

address but I would rather or I need the

host name off to here

luckily for me I have

another data source that I'm using open

sense in a DHCP server

and if I I will go ahead and run this

it will give me the raw logs and In The

Raw logs I have my IP address and it

also has host names in here

and I can look at my interesting Fields

because I have the open sense ta

app that I downloaded but helps me to

parse this data and you can see over

here in interesting Fields I have client

IP Mac and name

so now

I want

to create a lookup table with these

three fields

I'm going to hit the pipe I'm going to

say stats count by

what was that clients underscore name

a client

underscore IP and client underscore Mac

remember your field names are case

sensitive

not the values but the field names

himself are

and once this comes up it should give me

it gives me four columns and if I don't

want to count here in my lookup table

I'm just going to say you know easiest

way Fields negative counts

and that will clean it up and this is

the output that I would like to have

so next I'm going to invoke the output

lookup command so let's click on that

and then I already have in my command

history because I practice this before I

record a video

output lookup DHCP test and if you know

when I'm here

in my

Splunk environment it is not here yet so

let's go ahead and click on that and as

soon as I run this

and I give it a few seconds

there we go

you know I have an output

it may not be a hundred percent but it's

a start you don't have to build

everything from scratch

so I can have this here and start

editing this

um lookup table with the file lookup

editor so I 100 recommend downloading

that app to look you know edit the

lookup tables because if you don't you'd

have to be in the business of pulling

that look up table from your Splunk

index or search heads bring it down to

your computer edit it or log into the

box and edit it manually like that so

the lookup editor is definitely one of

the

first apps that I install on a fresh

Splunk install but here you can see I

have you know tab a and tab a oh which

one are there two different Mac

addresses two different IPS my kids both

have a tablet so if I wanted to know

which tablet is which you know grab the

tablet

you know look up the MAC address and

make sure I know which one it is and

update my lookup table so if we go back

here to

this lookup app the Splunk app for uh

look lookup file editing and re-run this

search

let's see here just hit refresh

and I'll have to put in DHCP again there

is that lookup table

and if I wanted to I can just click in

here

and now

I can start editing this lookup file so

I like this device here is you know

my work

underscore

you know laptop

you know this is

you know Dash child

one

and then we have

Dash child two

click save you know we can add more

columns so if I know

um like right now none of my firewall

ports are showing up so I could say

firewall

and if I have the IP address I can put

that in there and if I had the MAC

address

dot you know 1.1

Let's uh sure

just for fun because it doesn't matter

I'll just plug this in and you know call

it 99.

save

now when I come back over here

and I rerun this

um well actually if I rerun this ooh

almost messed up if I rerun this it'll

overwrite the changes well I'll show you

that let's see here bam

foreign

if I go back over here click lookups

refresh this let's see I'll do another

refresh here

and I'll type in DHCP

and click DHCP test

you can see those changes I made are

gone now so be careful with that command

with the output lookup

[Music]

um

so yeah let's I'll do this time I'll

just do this one here and I'll say you

know work

laptop

and you know I just want to show you

that you know

one

and then Dash two that it does work when

you click save lookup

and what I can do is come here and

actually I will

open a new search

and do a pipe input look up

DH

yeah

DHCP underscore test

not CSV

and you can see now instead of

um what it was before I get my work

laptop and now I have one and two

and then for this here you know I can

easily come back you know come back to

my previous search or I can type it out

here I think I've got it copied over

here

you know now I can you know quickly

oops got to get rid of the extra pipe

when I copied it

and then

actually what I'll do is

fields

and say dust underscore IP

and then stats count by dust underscore

IP host name

and voila

so you can see

where it's grabbing that information oh

I got the wrong

DHCP

underscore test dot CSV

oh

and you can see I have IP here

and what I needed to do was actually go

back to my lookup table

and say

client underscore IP

and then I believe it's the first one

here so let's just test that out

client

what did I call that field again

client name

underscore name

and there you go see there's the 133

which was the A1 and in there is my work

laptop so you got to see you got to see

me fail

with the field names but that's a good

thing because then you saw where you

know the first field is in your lookup

table to match in your search results

you know so the client IP as destination

IP and then the client name as hostname

so instead of it coming out as a client

name I have it as you can you know I

could have easily done this

and say client underscore name

if I wanted to

you know if that makes more sense for

you as well

and once you've defined that lookup

table and you've got it incorporated

into Splunk you know we can start adding

that information to dashboards you may

have built or other reports so here is a

a dashboard that I created that looks at

all the you know devices in my network

I use my information my data from the

DHCP server and compare it to the lookup

table to see if there's any changes you

know if a new device grabbed a an IP on

my network that I didn't know about you

know I could set up alerts around this

you know for example I do have one here

for uh you know what so anytime a new

device comes on here and it does not

find a match it actually outputs the

name what so that I can go hey what is

this

yeah and what is this so Nintendo 3DS

so one of my kids found you know they

must have turned on their 3DS they

haven't used in a while so I'm gonna go

edit my lookup table and here's the MAC

address so let's go see if it's already

in that look up table and not this one

so I'm going to click lookups here and

go back into uh Hall DHCP leases

and I can either do a filtered search

for nin

and I have one here for an Nintendo 3DS

but that's a different Mac address

so let's just add this one in place

because I know there should be two of

them

so I'll just you know insert a row

afterwards

and we'll call this one Nintendo we'll

say three

yeah three DS

two

and we'll give it yeah we can see there

it is the different Mac address

and then what IP address did it grab so

I'll just grab this IP address because

that's what my DHCP server has

and we will go back over here

and we'll say this I'm going to click

save lookup

all right and after clicking save look

up I should be able to go back to my

dashboard

and I'll just do a refresh

click okay didn't have to click submit

and it should not have anything in the

red column and

there we go

oh

interesting so now I need to

as a client name and a host name is

different so I'll play around with this

some more

should be the same well client name is

what my DHCP server sees it and then

this is the name I gave it so I'll have

to go now go get the kids devices and

make sure that I don't have a rogue

Nintendo 3DS on my network which I I

doubt it I know we have two of them

so hopefully this uh video was helpful

in introducing you to lookups and the

power of them if you have any questions

or comments please please leave them

below and uh Happy spelunking