Splunk KvStore Migration

By: Christopher Winarski | Splunk Consultant and

Bruce Johnson | Director, Enterprise Security

 

Migrating your Splunk environment can be a daunting task to some. With the worry of missing valuable data. Did my users’ settings migrate properly? Did all my applications migrate properly? Did all my lookup tables survive the migration? If you find yourself performing a Splunk migration you may be asking yourself some of these questions. Well, today I try to take one of those worries off your chest by walking you through a Splunk KvStore Migration, more specifically migrating the Splunk KvStore from a Search Head Cluster to a new Search Head Cluster. E.g On-prem Shcluster to AWS Shcluster

KvStore stores data in key-value pairs known as Collections. These tables of data are located in your collections.conf files. Records contain each entry of your data, similar to a row in a database table. Using KvStore as opposed to csv files you can define the storage definition schema for your data, perform create-read-update-delete operations on individual records using Splunk REST API and lookups using the Splunk search language. KvStore excels in performance when you start getting large lookups with many data points which is especially prevalent within Enterprise Security, one of Splunk’s Premium Apps.

The normal export/migration is to use csv export which is not really practical for large KvStores due to the limitations to file sizes on most operating system’s is what drive was used for mongodb in the first place. Gemini KvStore Tools helps to circumvent the normal semi-workable, tedious migration process.

Gemini KV Store Tools comes with some custom commands built for the Splunk search bar that makes our life/migration less complicated. The commands we are interested in for this migration are:

  • | Kvstorebackup
  • | Kvstorerestore

Requirements for this process:

  • You must already be utilizing Splunk’s KvStore for your lookups.
  • Downloaded and installed “Gemini KV Store Tools” application in both the originating environment Search Head Cluster and the new environment Search Head Cluster you are migrating too. https://splunkbase.splunk.com/app/3536/
  • You must have already migrated/copied the applications from the old Search Head Cluster. We are interested in the collections.conf within these applications.
    • tar -zcf apps.tgz /opt/splunk/etc/shcluster/apps
  • The collections.conf files must be present on the new environment before proceeding

 

Step 1: Of the original Search Head Cluster, Identify the kvstore captain, and log into the GUI environment, then open the search app. The KvStore captain is the instance in the search head cluster that receives the write operations regarding the KvStore collections where the Search head captain is the instance in the search head cluster that schedules jobs, pushes knowledge bundles to search peers, and replicates any runtime changes to knowledge objects throughout the search head cluster.**note** This may be different than the Search Head captain

 

Step 2: On this instance, also log into the backend and create a directory under the /tmp directory named “kvstore_backup”. Ensure Splunk has read/write permissions to this folder.

cd /tmp

mkdir kvstore_backup

sudo chown -R splunk:splunk /tmp/kvstore_backup

 

Step 3: Creates a json file per each collection to the destination path in the kvstore_backup folder, as well as should see “Success” per each collection zipped within the original environment. In the search bar on original environment KvStore captain, run:

| kvstorebackup path=”/tmp/kvstore_backup” global_scope=”true” compression=”true”

 

Step 4: Check KvStore monitoring console to verify if collection counts are listed and save the page to refer to the results/counts to verify later. (old environment)

Monitoring Console > Search > KvStore:Instance

 

Step 5: Now that you have created your collection backups and have verified that the number of records per is correct. Go on each new search head cluster member (CLI) and edit server.conf to have:

[kvstore]

                                                                        oplogSize = 10000

Also, on each instance, you have to edit/change the search head replication factor to 1 in the new environment on each search head cluster member. (server.conf)

[shclustering]

replication_factor = 1

Once both are set, restart the instance. Do this for every search head cluster member in the new environment.

 

Step 6: Identify and get Search Head captain to be the same instance as the kvstore captain.

Ensure the kvstore captain = Search Head Cluster captain.

Useful commands:

./splunk show shcluster-status

./splunk show kvstore-status

Transfer captaincy to one node by bootstrapping the kvstore captain as the search head captain.

On the KvStore captain, we want to make it also the search head captain, run this command (CLI):

./splunk edit shcluster-config -mode captain -captain_uri <URI>:<management_port> -election false

On each other non-captain instance, run this command (CLI):

./splunk edit shcluster-config -mode member -captain_uri <URI>:<management_port> -election false

This will allow you to specify the captain as the kvstore captain and get rid of dynamic captaincy for this purpose. At the end we will want to revert our search head cluster back to a dynamic captaincy.

 

Step 7: Once you have the kvstore captain = search head captain, Log into the CLI of the other search head nodes (every search head cluster member that is not the captain/kvstore captain). Starting the instance after cleaning the local kvstore will initialize a kvstore synchronization upon startup with kvstore captain.

SHUTDOWN Splunk: ./splunk stop

run: ./splunk clean kvstore –local

Then start splunk: ./splunk start

 

Step 8: SCP kvstore_backup from Step:2 to new environment search head captain/kvstore captain. Make sure that splunk has permissions to access the file. Follow these steps for guidance.

Old instance where the backup was created from:

scp -r kvstore_backup ec2-user@IPADDRESS:/tmp

Move file to /opt folder on kvstore/search head captain:

mv kvstore_backup /opt/kvstore_backup

Change ownership of the file and internal files to splunk for permissions

sudo chown -R splunk:splunk /opt/kvstore_backup

 

Step 9: Kvstore Gemini Tools is to be installed on the new search head cluster prior to running this step, if you have not done so please insure it is installed within the new search head cluster. Once the kvstore_backup has the permissions and is in place on the backend of the kvstore captain/search head captain. Now log on to the GUI of that splunk instance, open search and run:

| kvstorerestore filename=”/opt/kvstore_backup/*.json.gz”

On big restores, can take many minutes for the restore to complete, be patient and let the search run.

 

Step 10: Verify lookups return the same results in the new environment as back in the old environment with the saved page(step 4) , run:

| inputlookup <Lookup definition>

 

Step 11: We want to revert the search head cluster back to a dynamic captaincy now (the static captaincy bootstrapping was just used for the migration) and also change our replication factor back to the original setting in the environment.

You can do this by logging on to each instance CLI, stopping splunk then on the search head cluster captain, run:

./splunk edit shcluster-config -mode captain -captain_uri <URI>:<management_port> -election true

On the other non captain search head cluster members run:

./splunk edit shcluster-config -mode member -captain_uri <URI>:<management_port> -election true

Then we want to edit the config file again to revert replication factor back to the original number that was set before the migration. (server.conf)

[shclustering]

replication_factor = 2

**The “2” is arbitrary here, as this should be set to the number that was present prior to the migration**

That’s it! Migrations can be a scary endeavor and if not prepared, one can easily lose data. If you seek further assistance don’t hesitate to reach out to us here at TekStream Solutions. We would be happy to help! No Splunk project is too small or too big.

How to Set Up Splunk DB Connect to Connect to Multiple MSSQL Databases and Some Tips & Tricks

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!