How to Use the Splunk Join Command

When searching across your data, you may find it necessary to pull fields and values from two different data sources. But is it possible to do that?

The answer is yes! In these cases, we can use the join command to achieve the results we’re looking for.

What is the Join Command in Splunk?

The join command brings together two matching fields from two different indexes. To use the join command, the field name must be the same in both searches and it must correlate to two data sets. To minimize the resource consumption within Splunk, the join command is primarily used when the results of the subsearch are relatively small — 50,000 rows or fewer.

Let’s say you’re trying to match an IP address information from one index to another index with CIDR’s. Or you’re trying to compare values from a lookup because you need to find values that match or don’t match.

WARNING: The join command should not be used lightly. While on the surface it seems like a solution that could be applied to everything, it can consume too much time and Splunk resources if it’s used irresponsibly. Read on to learn how to use the join command responsibly.

Join Command Syntax

Now that we know what to prepare with join, let’s take a look at the syntax:

|join type= left|inner <matching field> [subsearch]

Types of Join Commands

There are two types of joins: left and inner.

  • A left join produces ALL of the results from the main search joined with matching results from the subsearch
  • An inner join produces only results where the main search and subsearch match

How to Use the Join Command in Splunk (+Example)

Let’s look at a sample search that draws a simple picture of what you can do to join.

Index=test
| dedup ip
| eval temp_value=0
| table ip temp_value
| join type=left ip
[|inputlookup blacklist.csv | rename ip_address as ip | eval temp_value=1 | table ip temp_value]
| table ip temp_value
| where temp_value=0

In this search, we are looking for ip addresses that are not found on our ip blacklist

Step 1: Start by creating a temporary value that applies a zero to every ip address in the data. 

Step 2: Use the join command to add in the IP addresses from the blacklist, including every IP address that matches between the two changes from a 0 to a 1. 

Step 3: Filter the search using “where temp_value =0” and filter out all the results of the match between the two.

The Pros and Cons of the Splunk Join Command

  • The join command requires a subsearch. This means that a second search inside the main search will retrieve results first and then apply those results to the results of the main search.
  • The subsearch is limited to returning the first 50,000 results.
  • Search times are not reduced. If you build a complicated subsearch that takes a long time to complete, it will always a long time to complete, even when using the join command. You will still have to wait for the main search to finish.

Join can be a very powerful tool for building coherent tables of data from multiple sources. However, we want to use it responsibly, so we don’t accidentally clog up our environment. Whenever possible, try to find alternative solutions before using the join command.

If you found this helpful… 

You don’t have to master Splunk by yourself in order to get the most value out of it. Small, day-to-day optimizations of your environment can make all the difference in how you understand and use the data in your Splunk environment to manage all the work on your plate.

Cue Atlas Assessment: a customized report to show you where your Splunk environment is excelling and opportunities for improvement. Once you download the app, you’ll get your report in just 30 minutes.

New call-to-action

Splunk Search Command Series: mvzip

 

 

Need some help zipping up your data in Splunk? This week’s Search Command should do the trick. The Splunk Search Command, mvzip, takes multivalue fields, X and Y, and combines them by stitching together.

Today, we are going to discuss one of the many functions of the eval command called mvzip. This function can also be used with the where command and the fieldformat command, however, I will only be showing some examples of this function using the eval command.

If you have been following our eval series, I am sure by now you know that the eval command is very versatile. Now let’s dive into another tool in the eval command’s tool belt! Let’s also use another command that we just learned called makemv to help facilitate this lesson. First, let’s make some data that has multiple field values.

Figure 1 - Data with multiple fields in Splunk
Figure 1 – Data with multiple fields in Splunk

 

New call-to-action

 

I’ve created three new fields called name, grade, and subject. Within each of these fields, we have multiple values. Let’s say we want to create a new field with these values “zipped” together. For example, I want to know what subjects Mike is taking all in one field. This is where mvzip comes in.

Figure 2 - mvzip example in Splunk
Figure 2 – mvzip example in Splunk

 

Here, I have created a new field called “zipped” with the values from the name and subject fields. Now we can see that Mike is taking Math, Science, History, and English. Next, I want to know what grades Mike has in those subjects (a.k.a. report card time!).

Figure 3 - Using mvzip in Splunk
Figure 3 – Using mvzip in Splunk

 

Using mvzip, we can see what grades Mike has in each subject. As you can see from the SPL above, I have mvzip the third field “grade” to the other two by adding another mvzip function. Splunk only allows you to zip three fields together, so this is our limit here! Also, if you noticed I added a different delimiter to our final results. I have a pipe separating my values instead of a comma in my first example. You can use whatever delimiter you want when using the mvzip function by putting quotes around the delimiter.

That is it for now, I hope you enjoyed this lesson and I hope you try this out in your own environment, happy Splunking! P.S. I think Mike could use some tutoring in History and English??

 

Ask the Experts

Our Splunk Search Command Series is created by our Expertise on Demand (EOD) experts. Every day, our team of Splunk certified professionals works with customers through Splunk troubleshooting support, including Splunk search command best practice. If you’re interested in learning more about our EOD service or chat with our team of experts, fill out the form below!

New call-to-action

Splunk Search Command Series: Halloween Edition

 

 

Halloween is hands down my favorite time of the year. Candy, costumes, scary movies, cold weather, haunted houses (or hayrides), what’s not to love. Every time Halloween rolls around, I am always looking for a good fright. While this year has been a disappointment for going out and experiencing all the scares, Splunk has been there to provide a terrifyingly good time. 

Today, let’s look at a couple of search commands that are so good…it’s SCARY.

1. Rex command

2. Fullnull

3. Rename

(t)rex

In the land before time, one creature ruled the earth…  

Nah, just kidding, we’re not talking about dinosaurs, we’re looking at the rex command 

Field extractions don’t pull out all the values that we absolutely need for our search. It might be due to irregular data patterns, low visibility, or maybe just not necessary to have as an extracted field. Regardless of the reason, we always come back to the data and extract the values through our search. Rex allows us to use regular expression in our search to extract values and create a new field. 

 

|rex field=<field> “<regular_expression>”

 

Instead of breaking down each section, it might be easier to show an example, here are a few sample events

10:41:35 PM – I saw Casper walking down the hallway 

08:31:36 PM – I saw Zuul running after me 

06:33:12 PM – I saw Jason coming out of the lake 

04:05:01 PM – I saw Jigsaw setting something up in the basement 

02:36:52 PM – I saw Hannibal making dinner 

Apparently, we need to get out of the house we’re staying at…or call the cops, right? (We all know the phone lines have already been cut?).

Before we do anything, we need to assess all the “things” we saw. In my panic, I forgot to set up proper field extractions and didn’t write a line in props.conf for monsters. Luckily, I can use rex to quickly grab these values.  

 

|rex field=_raw saw\s+(?<scary_things>\w+) 

From there we will get a list of our monsters:

 

Casper 

Zuul 

Jason 

Jigsaw 

Hannibal 

 

New call-to-action

 

Fillnull

You ever look at the results and notice the empty fields? Is that data missing, or was it never really there? (x-files music plays in the background) These are null values in your data, usually caused by a field not being in some events. In a results set this would look like empty cells and all those empty cells might drive you to insanity. To help ease your mind, we can use fillnull to complete our tables  

 

|fillnull value=<value> 

 

By entering a value, fillnull will fill the empty cells with your chose value. This could be a number like 0 or a string like “null” or “empty” 

Rename

Field names don’t always play nicely. In terms of compliance or formatting, field names can really jump out and scare you. In order to blend, we may need to resort to putting a mask over them. Rename search command will let us do just that. 

 

|rename <field> as <new_name> 

 

Here are some examples of rename command in action:  

|rename monsters as users 

|rename insane_asylums as dest 

That’s it for this scary edition of our Search Command Series. I hope these search commands help eliminate the fear behind slow search performance and the ghouls lurking in our data.

Don’t Be Scared of Splunk

Splunk can be pretty frightening, especially when you’re hiding from your searches. That’s where our EOD team comes in. Think the Ghost Busters… but for Splunk.

Our Splunk Search Command Series is created by our Expertise on Demand (EOD) experts. Every day, our team of Splunk certified professionals works with customers through Splunk troubleshooting support, including Splunk search command best practice. If you’re interested in learning more about our EOD service or chat with our team of experts, fill out the form below!

New call-to-action

The Beginner’s Guide to the makemv Command in Splunk

Have you ever been stuck with a single field that needed to provide you with a little more… value? The makemv command adds that value. 

Keep reading to learn exactly what the makemv command is, its benefits, and how to use it to bring more value to your searches.

Try Atlas Free for 30 Days

What is the makemv command in Splunk?

Makemv is a Splunk search command that splits a single field into a multivalue field. This command is useful when a single field has multiple pieces of data within it that can be better analyzed separately.

An example of a situation where you’d want to use the makemv command is when analyzing email recipients. “Recipient” is a single field that holds multiple values, but if you want to find a single value, it would require a lot of resources (and time) to search for it. Instead, you can use makemv to display multiple values of a single field as its own field.

Benefits of the makemv Command in Splunk

  • Analyze multiple values within a single field
  • Speed up your searches
  • You don’t have to create a new field to see the multiple values
  • You’re able to search using the search head without exhausting your search cores

How to Use makemv

To demonstrate how to use the makemv command, we’re going to use the following scenario:

Three people on your team received an email. These people are: Elmer, Bugs, and Yosemite. However, the email they received wasn’t from your internal team. Instead, it was a phishing email that has the potential to jeopardize the entire company. You know that when Bugs received the email, he opened it, and you suspect that the bad actors took over his account to send more spam emails to the entire company. It’s critical that you as the Splunk administrator get ahead of this issue immediately. To get started on your investigation, you need to find not only the email that was sent to these three individuals, but you need to find the emails that were sent directly to Bugs.

Let’s use the makemv command to solve this problem in this scenario.

Step 1: Start your search.

Use the search string below to start your initial search. Here, we’re telling Splunk to return to us all the recipients of the phishing email. 

| makeresults | eval recipients=”elmer@acme.com, bugs@acme.com, yosemite@acme.com

How to use the makemv command in Splunk: Step 1

Step 2: Use the makemv command along with the delim argument to separate the values in the recipients field.

How to use the makemv command in splunk: step 2Now that we have all the recipients of the email, we’re ready to look at the individual recipients as part of our investigation. Along with using the makemv command to find our specific recipient, Bugs, we’re using the delim argument to separate the email addresses into their own lines within the field. This makes the data easier to work with and puts it in the traditional makemv format in our table. The delimiter here is a comma since our email data is separated by commas.

| makeresults 

| eval recipients=”elmer@acme.com, bugs@acme.com, yosimite@acme.com” 

| makemv delim=”,” recipient

Step 3: Find the emails where Bugs is a recipient.

The great part about the makemv command is that you can find the emails where Bugs is a recipient rather than finding all the emails sent to the company and sorting for Bugs that way. This step in the makemv process is what makes it so efficient and valuable to have in your Splunk search repertoire.

| makeresults | eval recipients=”elmer@acme.com, bugs@acme.com, yosimite@acme.com” 

| makemv delim=”,” recipient 

| search recipient=”bugs@acme.com”
how to use the makemv command: step 3

Extract field values with regex

The makemv command can also use the regex command to extract the field values. In this example, we’re using the regular expression or “tokenizer” to match the string against the word characters in the email address.

| makeresults | eval recipients=”elmer@acme.com, bugs@acme.com, yosimite@acme.com” 
| makemv tokenizer=”(/we+)0” recipient 
| search recipient=”bugs”


how to extract field values using the makemv command and regex command in splunk
split Command vs. makemv Command

The split command in Java or Python and the makemv command in Splunk are similar in that they both separate values by a delimiter, but the primary difference is that the split function only separates the data into separate strings, it does not separate it into separate fields. When searching for data in Splunk, speed and ease are the name of the game, so makemv is the better choice between the two in this scenario.split command vs makemv command in splunk

Now that you have some basic understanding of the makemv command, try it out in your environment. Happy Splunking!

If you found this helpful…

You don’t have to master Splunk by yourself in order to get the most value out of it. Small, day-to-day optimizations of your environment can make all the difference in how you understand and use the data in your Splunk environment to manage all the work on your plate.

Cue Atlas Assessment 30-day free trial: a customized report to show you where your Splunk environment is excelling and opportunities for improvement. You’ll get your report in just 30 minutes.Try the Atlas Assessment

Splunk Search Command Series: dbinspect

 

 

The power of Splunk comes from the insights we pull from our data. And to emphasize… I mean searchable data. Now, Splunk isn’t perfect and neither is your data. Data can be corrupt, go missing, or frankly, live in the dark. Pull that data back into the light and ensure your data is intact by using dbinspect.

What is dbinspect? The Splunk search command, dbinspect, allows us to look at the information of buckets that make up a specified index.  If you’re using Splunk Enterprise, this search command shows you where your data lives so you can optimize your disk space.

How to Use dbinspect

Let’s break down the command:  

|dbinspect index=<index_nametimeformat=<time format> 

Check out what this looks like in Splunk:  

Figure 1 - dbinspect in Splunk
Figure 1 – dbinspect in Splunk

 

The above screenshot may look small as it doesn’t capture all of the fields, but, the fields we DO see provide us with a wealth of information. When you use the command, you’ll have access to view all of the fields we can’t see in the screenshot.

New call-to-action

 

Here’s what we can see with dbinspect: 

How many events are in a bucket 

The file path of the bucket 

Which index the bucket belongs too 

 

dbinspect also tells us: 

The state of the bucket (hot/warm/cold) 

When the bucket was created 

The size of the bucket in mb 

And tsidx states (full, fulling, etc) 

 

And that’s it. Use dbinspect to get insights into your data buckets. We’ve got plenty of searches to come this month, stay tuned!

Ask the Experts

Our Splunk Search Command Series is created by our Expertise on Demand (EOD) experts. Every day, our team of Splunk certified professionals works with customers through Splunk troubleshooting support, including Splunk search command best practice. If you’re interested in learning more about our EOD service or chat with our team of experts, fill out the form below!

New call-to-action

Splunk Search Command Series: inputlookup and outputlookup

 

What is outputlookup in Splunk?

The outputlookup command is a way to save any search you’ve made as a lookup table. This command works by turning search results into lookup tables so that the data can be retrieved later using an inputlookup command.

What is inputlookup in Splunk?

The Inputlookup command is used to retrieve data from a Splunk lookup. Rather than searching for the .csv file, or even creating an output lookup every time you need the .csv in a lookup table, you can create an output lookup once to retrieve it, almost instantaneously, as many times as you need it with an inputlookup.

Splunk Tip: The downside to output and input lookup commands is that your .csv file is static, so the data will only be current as of the last time you updated that file.

How To Use outputlookup

Whenever you find yourself with a results table that you’d like to hold onto, use outputlookup. When you throw outputlookup at the end of the search, it will turn the results into a lookup that you can use independently.

Here’s the syntax for outputlookup:

Syntax: |outputlookup <lookup_name>.csv

Figure 2 - Using outputlookup in Splunk

Figure 1 – Using outputlookup in Splunk

There are a few extra lines that can be added if need be. Lines like append=true and overwrite=true will change based on how the lookup is created.

Outputlookup really shines when it comes to building out a list of suspicious values in Splunk (such as a watchlist, blacklist, or whitelist).  All it takes is to build out a results table in Splunk that contains the information you need.

How To Use inputlookup

Where the lookup search command [ADD LINK] allows you to join fields from a lookup to the data from search, inputlookup will allow you to just view or start with the lookup. This can be used at the beginning of a search, halfway through (using append or join), or where you see fit to bring in a lookup. Starting a search with an inputlookup can drastically increase search speeds, so keep an eye out if it applies to any of your use cases.

Here’s the syntax for inputlookup:

|intputlookup <lookup_name>.csv

Figure 1 - Using inputlookup in Splunk

Figure 2 – Using outputlookup in Splunk

New call-to-action

How To Find a List of All Lookups in Splunk

Step 1: Go to Settings

Step 2: Click Tables

Step 3: Search for your .csv file

How To Adjust Permissions for Lookups in Splunk

Step 1: Search for the lookup table you want to adjust permissions for.

Step 2: Hover over to Sharing and select Permissions.

Step 3: Choose who can have Read or Write Permissions

How To Reference a Lookup Table From Excel

If you want to upload a lookup table to reference from Excel, follow these steps.

Step 1: Select New Lookup Table File

Step 2: Choose a file that ends with .csv

Step 3: Save your file

If you found this helpful… 

You don’t have to master Splunk by yourself in order to get the most value out of it. Small, day-to-day optimizations of your environment can make all the difference in how you understand and use the data in your Splunk environment to manage all the work on your plate.

Cue Atlas Assessment 30-day free trial: a customized report to show you where your Splunk environment is excelling and opportunities for improvement. You’ll get your report in just 30 minutes.

New call-to-action

Splunk Search Command Series: Rare

 

Remember we talked about the TOP command? Well turns out there is a command that works exactly the same way but you get results for the fewest occurrences in your data. 

It is called RARE. Where TOP provides you with the most common values in your data, rare shows you the values that occur the fewest. 

More About Rare

Something we can accomplish with the search below:  

index=main| stats count as count by user | sort count | head 10 

Again, an easy search, but we can make it easier 

Index=main| rare limit=10 user 

Wango Bango! Same results, less…search.

New call-to-action

 

How to Use Rare

Let’s explore the syntax: 

|rare <options> field <by-clause> 

Options –  

  • Limit = limit the number of results 
  • Showperc =  show the activity percent field of the value 

Field = filed you want to find the top values of 

By-clause = a field you want to filter by 

And there you have it. Rare command is an easier search… but is important to utilize.

 

Ask the Experts

Our Splunk Search Command Series is created by our Expertise on Demand (EOD) experts. Every day, our team of Splunk certified professionals works with customers through Splunk troubleshooting support, including Splunk search command best practice. If you’re interested in learning more about our EOD service or chat with our team of experts, fill out the form below!

New call-to-action

How To Use Splunk Table and Fields Commands

 

Are you working with the same set of data on a regular basis? Are your searches taking more than a couple of seconds to load? Splunk’s table command and fields command can make this process faster for you. 

These two commands are similar, but they have different functions. In this guide, I’ll walk you through what table and field commands are and how to use them.

 

What is the fields command in Splunk?

The fields command is a Splunk search command that allows you to retrieve specific fields within your data. You can retrieve these fields without conducting a search for all the fields in the data. The benefit of using this command is that it reduces the time it takes for Splunk to retrieve the events associated with those fields.

 

How to Use the Fields Command

Step 1: Start a base search.

In this example, we’re using this search:

index=”splunk_test” sourcetype=”access_combined_wcookie”

Using job inspector, we can see it took about 7.3 seconds to run this search. This search includes all the events associated with each field in this set of data. You can see this on the right-hand side. 

Before Using Splunk Fields Command Search Speed Using the Job Inspector Tool

Step 2: Add the fields command.

index=”splunk_test” sourcetype=”access_combined_wcookie”
|fields JSESSIONID req_time referrer_domain

This fields command is retrieving the raw data we found in step one, but only the data within the fields JSESSIONID, req_time, and referrer_domain.

It took only three seconds to run this search — a four-second difference!After Using the Splunk Fields Command Search Speed Using the Job Inspector Tool

Running the Fields Command and Stats Command Together

You can use the fields and stats commands together for even faster searches. In this example, we’re running a stats command, but excluding a field from the search after we’ve run it.

Let’s start with the base search and the stats command:

index=”splunk_test” sourcetype=”access_combined_wcookie”
|stats count by action, status, JSESSIONID

How to Run the Fields Command and Stats Commands TogetherNext, we’ll include the fields command. We’ll be excluding the count field.

index=”splunk_test” sourcetype=”access_combined_wcookie”
|stats count by action, status, JSESSIONID
|fields - count

Now our search displays all of the same data it displayed before, but without the column dedicated to the count field.How to Use the Fields Command to Exclude a Count Field in Splunk Data

Splunk Tip: The fields command automatically includes the plus (+) so you don’t have to manually type it in when using this search command. This is also why using a minus (-) returns all the fields except those you’ve specified in the search.

 

New call-to-action

 

What is the table command in Splunk?

The table command does the exact same thing as the fields command where it pulls the raw data from a search quickly, using the fields you specify. The difference is that it pulls this data into a tabular format.

 

How to Use the Table Command

Step 1: Start a base search.

In this example, we’re using this search:

index=”splunk_test” sourcetype=”access_combined_wcookie”

Using job inspector, we can see it took about 7.3 seconds to run this search. This search includes all the events associated with each field in this set of data. You can see this on the right-hand side.

Step 2: Add the table command.

index=”splunk_test” sourcetype=”access_combined_wcookie”

|table JSESSIONID req_time referrer_domain

This table command is retrieving the raw data we found in step one, but only the data within the fields JSESSIONID, req_time, and referrer_domain. It’s placing that data in a tabular input.

How to Use the Table Command: Add the Table Command to the Search Bar

Splunk Tip:  The table command can also pull in fields that were not originally in your data — even fields that have been created after your data has been ingested.

Running the Table and Eval Commands Together

You can use the table and eval commands together for even faster searches. In this example, we’re looking for the error check field — this field doesn’t appear in our data until we run the eval command.

Let’s start with the base search and the eval command:

index=”splunk_test” sourcetype=”access_combined_wcookie”

|eval errorcheck=if(status>=400, “error”. “Non-error”}

Remember, the error check field won’t appear unless we search for this data using the eval command.How to Run the Table and Eval Commands Together Using Error Check as an Example Field

Now let’s add the table command so we can see the data in tabular format.

index=”splunk_test” sourcetype=”access_combined_wcookie”

|eval errorcheck=if(status>=400, “error”. “Non-error”} 
|table action errorcheck itemId
How to Use the Table Command in Tabular Format

Running the Stats and Table Commands Together

It’s important to note that the stats and table commands can be used together, but your table command results will be limited because the stats command is a transforming command. Put simply, that means any fields you’ve specified for the stats command will be the only fields that appear in your table, even if there is additional data in the base search.

Here’s what that looks like in practice:

index=”splunk_test” sourcetype=”access_combined_wcookie”

|stats count by action, status, JSESSIONID

|table action status req_timeHow to Run the Stats and Table Commands Together

Here, we can see that req_time has no values because running a transforming command like the stats command, our data is limited to the three fields we’ve specified (action, status, and JSESSIONID). Therefore, our table command can’t pull additional fields outside of the stats command.

 

Table and Fields Commands Made Easy In Splunk

There you have it! The fields command and the table command: two very useful and powerful commands that you should definitely add to your arsenal of search commands. Enjoy! 

Ask the Experts

You don’t have to master Splunk by yourself in order to get the most value out of it. Small, day-to-day optimizations of your environment can make all the difference in how you understand and use the data in your Splunk environment to manage all the work on your plate.

Cue Atlas Assessment 30-day free trial: a customized report to show you where your Splunk environment is excelling and opportunities for improvement. You’ll get your report in just 30 minutes.

New call-to-action