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