How to monitor Microsoft SQL server using the Splunk Add-on for Microsoft SQL server ?

[Hemnaath]

Hi All, I have a requirement from data base team to monitor bunch of Microsoft SQL server in our organization. I had gone through the document provided in the splunk base for splunk Add-on and as per we can do File monitoring, windows performance monitoring and Database monitoring. In our case we are going to do the file monitoring and Database monitoring, as windows performance monitoring is already done by a customized app.

We have Deployment server which manages the overall apps centrally, so any changes to any app will be done via Deployment server under /etc/deployement-apps/our apps and it will be reloaded to the remote machines.
Requirement: Initially we are planning to set a file monitoring for all the servers which are having the SQL servers running in them.

Challenge 1: We want to manage / push this Splunk_TA_microsoft-sqlserver via deployment server. But we have different SQL versions running in different windows OS with different path locations to capture the Error.log.

Got an idea to configure/manage centrally but not sure whether this is the right approach.

1) Create a splunk add-on app for each version separately and likewise create a server class for each Splunk Add-on and add the respective hosts based on the SQL version.
Example: For Microsoft SQL Server 2016
a) Rename the Splunk_TA_microsoft-sqlserver_2016 and create a separate server class SC-microsoft-sqlserver-2016
b) Rename the Splunk_TA_microsoft-sqlserver_2014 and create a separate server class SC-microsoft-sqlserver-2014

Challenge2: How to configure the inputs.conf stanza provided in the Add-on, as we have different path/different host for the same SQL version? Based on the below data as example.

Node=test01
SQL Version: Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
File Monitoring:D:\Data\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG

Node=test02
SQL Version: Microsoft SQL Server 2014 - 12.0.4100.1 (X64)
File Monitoring:C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG
Node=test03
SQL Version: Microsoft SQL Server 2014 - 12.0.2000.8 (X64)

File Monitoring: 😧 \Program Files\Microsoft SQLServer\MSSQL12.SCOM2012RS\MSSQL\Log\ERRORLOG

Likewise we have more than 100 + systems which needs to be configured for file monitoring.

Challenge3: We have SQL Version: Microsoft SQL Server 2016 (SP1-CU3), Microsoft SQL Server 2008 (SP3) & Microsoft SQL Server 2005. But not sure whether it supported by the Splunk Add-on are supported by the splunk add-on.

Kindly guide me how to setup inputs.conf configuration to monitor the Error.log.

[Richfez]

So at least for Challenge 1 and Challenge 2 ....

A Monitor input in Splunk doesn't really do much of anything if the file it's trying to "watch" doesn't exist. I mean, maybe if we were silly and configured thousands of monitor stanzas pointing to non-existent files it could be a problem, but in this case you'll have maybe a dozen or two different monitor inputs. This is fine. So what I'd so is ignore the entire problem and just create the monitor stanzas in your inputs.conf to cover all cases and let Splunk ignore the ones where the file doesn't exist.

For Challenge 3 .... Some bad news and some possible good news.

There are a couple of issues here, at least potentially. SQL 2005 doesn't support the same sort of auditing that SQL 2008 does, and since the app doesn't even fully support all the way back to the original SQL 2008 line, I suspect 2005 is not going to work. Also, some of the auditing that most apps use was only supported in SQL Server Enterprise editions, not SQL Server Standard editions, at least up until either 2008R2 or 2012 when MS changed auditing a bit. I am having a bit of trouble finding the right MS docs, but hopefully now that I've pointed this out perhaps you can find that answer yourself.

But the good news? It's unlikely to cause any problems of significance to just try it on a few test boxes and see. Even if not everything works, a lot of it might, and maybe that's enough.

FWIW, I've got a ticket sitting in my queue to do this same sort of task. We have some SQL versions I know will not be supported, at all, but most of ours should so I'm hoping ... 🙂

Good luck, and happy splunking!
-Rich

[Hemnaath]

Hi Rich, thanks for your support on this, hey I have planned to create six different App and each app will be containing respective SQL version config details Example : MS SQL 2014 Version

APP name : Test-IA-MS SQL2014

Inputs.conf Details
ERROR Log for SQL Server 2014
[monitor:// C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG*]
[monitor:// D:\Data\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG*]
[monitor:// D:\Program Files\Microsoft SQL Server\MSSQL12.SCOM2012RS\MSSQL\Log\ERRORLOG*]
[monitor:// D:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Log\ERRORLOG*]
[monitor:// D:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Log\ERRORLOG*]
sourcetype = mssql:errorlog
index = windows
disabled = 1

This app will be pushed / managed from deployment server to remote nodes.

Questions:

1) Should I need to copy/push entire content of "Splunk_TA_microsoft-sqlserver" to the remote nodes or we can just keep the inputs.conf, props.conf, default.meta.conf , since we are going to use this app "Test-IA-MS SQL2014" only to fetch the Error log details from the SQL server machine.

2) Should I need to create an identity with user name and password for each database then we have almost 100 + nodes with different SQL version, what should be done. Or We can create a common identity with user name and password, that has complete access to the data that splunk will fetch from that database.

3) How to configure the Database inputs in DB connect, We have DB connect version 3.0.3 but when I checked the splunk Add-on for Microsoft SQL server documentation, we can choose DB Connect v3.1 inputs for the Splunk Add-on for Microsoft SQL Server or DB Connect v2 inputs for the Splunk Add-on for Microsoft SQL Server. So in this case which type of configuration inputs should be used.

4) I am planning to use the default template provide in the Splunk SQL server add on for configuring the database input. so in this how to configure it via DB connect.

5) While configuring the Database input via DB connect GUI, --> Datalab-->new inputs--> Name-->Description --> App name --> In this case which app should I need to select to store the inputs, do i need to choose DB connect app or Splunk Add-on for MS SQL, as i need to monitor the MS SQL database instances.

Kindly guide me on this.
thanks in advance.

[Richfez]

For the first thing (before number one) you'll want to add the settings (sourceytpe, index and disabled) to all of the stanzas.

Also, you can likely simplify it to three,

[monitor:// C:\Program Files\Microsoft SQL Server\*\MSSQL\Log\ERRORLOG*]
sourcetype = mssql:errorlog
index = windows 
disabled = 0
[monitor:// D:\Data\*\MSSQL\Log\ERRORLOG*]
sourcetype = mssql:errorlog
index = windows 
disabled = 0
[monitor:// D:\Program Files\Microsoft SQL Server\*\MSSQL\Log\ERRORLOG*]
sourcetype = mssql:errorlog
index = windows 
disabled = 0

(Let's see if the editor does that right or not... 🙂 )

Number 1, I don't think so. I believe from the view of the data inputs for the file side of things this is just a monitor stanza. UFs Running SQL from from where you'd like to collect the data just need the inputs.conf files, just make sure the indexers have the right props and transforms and stuff.

Number 2 - Number 5, I'd actually ask these in a new question or else this may end up too many answers in one question and make it hard for other people to use it down the road. But in short - #2 why not a domain username/password? Otherwise, one local or separate local (or same account different password) is a question for your own security people. #3 DBXv3 is better in nearly (but not quite all) ways than DBX2. I'd go with 3 unless you for some reason have to go with dbx2. But if you only have 3 installed anyway, use that. #4 Not sure. Try it on a test system? #5 the docs for db connect inputs walk through this, but in my experience it's easiest to leave the db input app context at DB Connect unless you want to have to fool with some permissions. Maybe that's fixed now, maybe not, try it either way.

You are testing on one box first, aren't you? Then adding in one more representative box of each different version, right? Then finally rolling out to everywhere once you a) prove it works b) tweak all the settings and c) confirm it won't make your license start whimpering in the corner... ???

[Hemnaath]

Hi Rich, Can you please guide me on the above questions.

thanks in advance.