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