Archive for category: Splunk

By: Khristian Pena | Splunk Consultant

Have you worked with structured data that is not following its structure? Maybe your JSON data has a syslog header. Maybe your field values have an extra quote, colon, or semicolon and your application team cannot remediate the issue. Today, we’re going to discuss a powerful tool for reformatting your data so automatic key-value fields are extracted at search-time. These field extractions utilize KV_MODE in props.conf to automatically extract fields for structured data formats like JSON, CSV, and from table-formatted events.

Props.conf

[<spec>]

KV_MODE = [none|auto|auto_escaped|multi|json|xml]

This article will focus on the JSON structure and walk through some ways to validate, remediate and ingest this data using the SEDCMD.  You may have used the SEDCMD to anonymize, or mask sensitive data (PHI,PCI, etc) but today we will use it to replace and append to existing strings.

JSON Structure

JSON supports two widely used (amongst programming languages) data structures.

• A collection of name/value pairs. Different programming languages support this data structure in different names. Like object, record, struct, dictionary, hash table, keyed list, or associative array.

• An ordered list of values. In various programming languages, it is called as array, vector, list, or sequence.

Syntax:

An object starts with an open curly bracket { and ends with a closed curly bracket } Between them, a number of key value pairs can reside. The key and value are separated by a colon : and if there are more than one KV pair, they are separated by a comma ,

{

  “Students“: [

                                      { “Name“:”Amit Goenka” ,

  “Major“:”Physics” },

                                      { “Name“:”Smita Pallod” ,

  “Major“:”Chemistry” },

                                      { “Name“:”Rajeev Sen” ,

  “Major“:”Mathematics” }

                                      ]

                                      }

An Array starts with an open bracket [ and ends with a closed bracket ]. Between them, a number of values can reside. If more than one values reside, they are separated by a comma , .

[

                                      {

  “name“: “Bidhan Chatterjee”,

  “email“: “bidhan@example.com”

                                      },

                                      {

  “name“: “Rameshwar Ghosh”,

  “email“: “datasoftonline@example.com”

                                      }

                                      ]

JSON Format Validation:
Now that we’re a bit more familiar with the structure Splunk expects to extract from, let’s work with a sample. The sample data is JSON wrapped in a syslog header. While this data can be ingested as is, you will have to manually extract each field if you choose to not reformat it. You can validate the structure by copying this event to https://jsonformatter.curiousconcept.com/ .

Sample Data:

May 14 13:28:51 <redacted_hostname> github_audit[22200]: { “above_lock_quota”:false, “above_warn_quota”:false, “babeld”:”eebf1bc7″, “babeld_proto”:”http”, “cloning”:false, “cmdline”:”/usr/bin/git upload-pack –strict –timeout=0 –stateless-rpc .”, “committer_date”:”1589477330 -0400″, “features”:” multi_ack_detailed no-done side-band-64k thin-pack include-tag ofs-delta agent=git/1.8.3.1″, “frontend”:”<redacted>”, “frontend_pid”:17688, “frontend_ppid”:6744, “git_dir”:”/data/user/repositories/7/nw/75/42/9d/4564/6435.git”, “gitauth_version”:”dcddc67b”, “hostname”:”<redacted>”, “pgroup”:”22182″, “pid”:22182, “ppid”:22181, “program”:”upload-pack”, “quotas_enabled”:false, “real_ip”:”10.160.194.177″, “remote_addr”:”127.0.0.1″, “remote_port”:”15820″, “repo_config”:”{\”ssh_enabled\”:\”true\”,\”ldap.debug_logging_enabled\”:\”true\”,\”auth.reactivate-suspended\”:\”true\”,\”default_repository_permission\”:\”write\”,\”allow_private_repository_forking\”:\”true\”}”, “repo_id”:6435, “repo_name”:”<redacted>”, “repo_public”:true, “request_id”:”43358116096ea9d54f31596345a0fc38″, “shallow”:false, “status”:”create_pack_file”, “uploaded_bytes”:968 }

The errors are noted and highlighted below:

As we can see, the timestamp, hostname and thread field are outside of the JSON object.

Replace strings in events with SEDCMD

You can use the SEDCMD method to replace strings or substitute characters. This must be placed on the parsing queue prior to index time. The syntax for a sed replace is:

SEDCMD-<class> = s/<regex>/\1<replacement>/flags

• <class> is the unique stanza name. This is important because these are applied in ABC order
• regexis a Perl language regular expression
• replacementis a string to replace the regular expression match.
• flagscan be either the letter g to replace all matches or a number to replace a specified match.
• \1 – use this flag to insert the string back into the replacement

How To Test in Splunk:

Copy the data sample text into a notepad file and upload using Splunk’s built in Add Data feature under Settings to test. Try out each SEDCMD and note the difference in the data structure for each attribute.

BEFORE:

AFTER:

Props.conf – Search Time Field Extraction

[<spec>]

KV_MODE = json

Want to learn more about JSON structured data & the SEDCMD in Splunk? Contact us today!

By: Jon Walthour |Team Lead, Senior Splunk Consultant

Over the years, I have found one tried and true method for getting Splunk connected to multiple Microsoft SQL Server instances spread across a corporate network—connect to Windows from Windows. That is to say, run the DB Connect application from Splunk on a Splunk Enterprise Heavy Forwarder, installed on a Windows environment. Why must Splunk be running Windows? It certainly doesn’t if you’re going to authenticate to the MSSQL instances with local database accounts. That authentication process can be handled by the database driver. However, when multiple connections to multiple MSSQL instances are required, as is often the case, a bunch of local account usernames and passwords can be a nightmare to manage for everyone involved. So, Windows AD authentication is preferred. When that becomes a requirement, you need a Windows server running Splunk. I tried getting Splunk running on Linux to connect to SQL Server using AD authentication via Kerberos for a month and never got it to work. Using a Windows server is so much simpler.

To accomplish this, the first thing you need to do is request two things from your Infrastructure teams—a service account for Splunk to use to connect to all the SQL Server instances and a server running Microsoft Windows. The service account must have “logon as a service” rights and the Windows server must meet the requirements for Splunk reference hardware with regards to CPUs, memory and storage. The best practice for Splunk generally speaking is to use General Policy Objects (GPOs) to define permissions so that they are consistent across a Windows environment. Relying on local Admin accounts can result in challenges, particularly across some of the “back-end” Splunk instances such as Splunk Search Head to Indexer permissions.

Once the server and service account have been provisioned, install Splunk Enterprise and Splunk DB Connect (from Splunkbase) on the it. Here’s the first trick: go into Settings > Control Panel > Services and configure the splunkd service to run under the service account. This is crucial. You want not just the database connections to be made using the service account, but the Splunk executables to be running under that account. This way, all of Splunk is authenticated to Active Directory and there are no odd authentication issues.

After you have Splunk running under the MSSQL service account with DB Connect installed as an app in the Splunk instance, you’ll want to install the Java Runtime Environment (JRE) software, either version 8 (https://www.oracle.com/java/technologies/javase-jre8-downloads.html) or version 11 (https://www.oracle.com/java/technologies/javase-jdk11-downloads.html), and download the appropriate MSSQL driver based on Splunk’s documentation (https://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Installdatabasedrivers), which either the Microsoft drivers for the open source jTDS drivers. Personally, I’ve had better outcomes with the Microsoft drivers in this scenario.

Once you’ve downloaded the SQL database driver archive, unzip it. In the installation media, find the library “mssql-jdbc_auth-<version>.<arch>.dll” appropriate to the version and architecture you downloaded and copy it to the C:\Windows\System32 directory. Then, find the file jar “mssql-jdbc-<version>.<jre version>.jar” appropriate to your JRE version and copy it to $SPLUNK_HOME\etc\apps\splunk_app_db_connect\drivers.

Now, log into Splunk and go the Splunk DB Connect app. It will walk you through the configuration of DB Connect. In the “General” section, fill in the path to where you installed the JRE (JAVA_HOME). This is usually something like “C:\Program Files\Java\jre<version>”. The remaining settings you can leave blank. Just click “Save”. This will restart the task server, which is the java-based processing engine of DB Connect that runs all the database interactions.

In the “Drivers” section, if the MS SQL drivers are not listed with green checkmarks under the “Installed” column, click the “Reload” button to have the task server rescan the drivers folder for driver files. If they still do not have green checkmarks, ensure the right driver files are properly placed in $SPLUNK_HOME/etc/apps/splunk_app_db_connect/drivers.

Next, navigate to Configuration > Databases > Identities and click “New Identity”. Enter the username and password of the service account you’re using for the MSSQL connections and give it an appropriate name. Check “Use Windows Authentication Domain” and enter the appropriate value for your Active Directory domain. Save the identity.

Navigate to Configuration > Databases > Connections and click “New Connection”. Pick the identity you just created and use the “MS-SQL Server using MS Generic Driver With Windows Authentication” connection type. Select the appropriate timezone the database you’re connecting to is in. This is especially important so that Splunk knows how to interpret the timestamps it will ingest in the data. For the “host” field, enter the hostname or IP address of the MSSQL server. Usually the default port of 1433 doesn’t need to be changed nor the default database of “master”. Enable SSL if you’re connection is to be encrypted and I always select “Read Only” when creating a database input to make sure there is no way to input can change any data in the connected database.

Finally, a few miscellaneous tips for you.

For the “Connection Name” of database connections, I always name them after their hostname and port from the JDBC URL Settings. This is because in a complex DB Connect environment, you can have many inputs coming from many different databases. A hostname/port number combination, however, is unique. So, naming them with a pattern of “hostname-port#” (e.g., “sql01.mycompany.com-1433”) will prevent you from establishing duplicate connections to the same MSSQL installation.

Another tip is that you can edit the connection settings for your JDBC driver directly in the configuration. This is typically only useful when your development team has come up with specific, non-standard configurations they use for JDBC drivers.

Sometimes complex database queries that call stored procedures or use complex T-SQL constructions can be more than the JDBC driver and Task Server can handle. In that case, I ask the MSSQL DBAs if they will create a view for me constructed of the contents of the query and provide me select rights on the view. That leaves all the complex query language processing with SQL server rather than taxing the driver and DB Connect.

When dealing with ingesting data from a SQL server cluster, the usual construction of the JDBC connection string created by DB Connect won’t do. With a clustered environment, you also need to specify the instance name in addition to the hostname and port of the SQL Server listener. So, after setting up the connection information where the host is the listener and the port is the listener port, click the “Edit JDBC URL” checkbox and add “;instance=<database instance name>” to the end of the JDBC URL to ensure you connect to the proper database instance in the cluster. For example, the get to the “testdb” instance in the “sql01” cluster, you’d have a JDBC URL like: “jdbc:sqlserver://sql01.mycompany.com:1433;databaseName=master;selectMethod=cursor;integratedSecurity=true;instance=testdb”

I hope these directions and tips have been helpful in making your journey into Splunk DB Connect simpler and straightforward.

Happy Splunking!

Want to learn more about setting up Splunk DB Connect to connect to multiple MSSQL databases? Contact us today!

By: Aaron Dobrzeniecki | Splunk Consultant

Have you ever wanted to pull logs from Splunk without actually being physically signed into the Splunk Search Head? With an external application, such as Postman, you can query the Splunk REST API endpoint to actually provide you with results from a search being run.

When Splunk runs a search, it creates a search ID which we can use to grab the results from the REST endpoint. We will be testing out two ways to get the results of a search. The first way is to grab the name of the Splunk search and query it against the /services/saved/searches/{search_name}/dispatch endpoint, which will provide us with the sid. We then use the sid to grab the results of the search, which will fire off the search and will poll for results as they come in. The second way to get the search results is by doing an export on the search name which will run the search and get the results without polling.

First things first, you need to make sure that the user you are authenticating to Splunk with has the “Search” capability, as well as access to search the necessary indexes. It’s that simple! If you are setting up a user for a particular person make sure they only have access to what they need. Giving further access is not necessary and can cause security issues.
In this example we are using the Postman application to query the Splunk REST API to grab search results from a couple of different reports/saved searches. Things we are going to need include:

• Splunk user account with the Search capability. We need that user to be able to search the index we are going to be grabbing our data from.
• We also need to know the Splunk URL we are going to be pulling from. In this case, I am using my localhost as an example. We will also be querying the Splunk management port of 8089 to get our results set.

The image above shows the type of request I am doing (POST), the REST API being used to query my search ID (/services/saved/searches/{name_of_search}/dispatch), and the authentication type of username and password. What the URL above is doing is it is reaching out to Splunk and grabbing the SID (search id) of the search named Index Retention Getting Close. With this search id we will be able to run a GET on the Splunk REST API and grab the results of the search.

Below I will be showing you two Splunk REST API endpoints that you can query (using POST) to get the Search ID for a specified search. The first endpoint is for searches that do not have Global permissions. As long as the user you are authenticating with has a role that has access to read the search, you can query the endpoint of /servicesNS/nobody/{app}/saved/searches/{name}/dispatch to retrive the Search ID. The second endpoint you can query if the search has Global permissions and you have read access is simply /services/saved/searches/{name}/dispatch to retrieve the Search ID. The two scenarios are below.

The image above shows the rest endpoint that can be used to grab a specific search ID that is in an app and has specific permissions. As long as my account has access to the app and search inside the app, I will be able to query it. For this example, we have changed the permissions of the search to be App only.

The image above is the results of the search in json, using the search ID we queried from the REST API.

The image above gives us the same results except they are in xml format.

The image above shows the search ID of the search with REST API I am querying. Since that search now has Global permissions, we do not need to use the ServicesNS endpoint. When you do a POST with a dispatch on the name of a search/report you will get the Search ID. As you can see the search ID is circled. We will be using this search ID to query the results of the search and show the actual search results in the Postman application. The Splunk REST API you will want to query next is the /services/search/jobs/{sid}/results?output_mode= (atom | csv | json | json_cols | json_rows | raw | xml). Any of those values will get you the results of the search in the format selected. In this example, I will be showing you json and xml.

As you can see above, the data results are shown in xml format for the search we were wanting to get results from.

This image shows the same results but in json format. With the options above for data output, you can query the Splunk REST API to get the search results and have them show in your preferred format.

Way 2: Query the REST API to show the results by using an export on the search name which will run the search and get the results without polling. Take a look at the screenshot below which queries the /services/search/jobs endpoint to stream in the results of the search as they come in.

Remember, you need to have the Search capability in Splunk, as well as you have to be able to read the results of the search. Whether that is setting Global permissions or having a role that has read access to the app and search. Below are some links referencing the Splunk REST API. If you have any questions at all regarding querying the Splunk REST API from an external application, please let me know!

https://docs.splunk.com/Documentation/Splunk/8.0.6/RESTTUT/RESTsearches

https://docs.splunk.com/Documentation/Splunk/8.0.6/RESTREF/RESTsearch#search.2Fjobs.2Fexport)

Want to learn more about using an external application to pull Splunk search results? Contact us today!