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,

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

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 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

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