Procedure for database monitoring through Microsoft Sql Server APP

[nagadeepthi]

I have Splunk enterprise installed on one of the machine and universal forwarders installed on other machine which consists of MSSQL Server 2012 and (Windows 2008 server R2)
I want to monitor database metrics like Concurrency, Transactions, Top Executed Queries etc. from this machine.

I have even installed the Microsoft sql server app which is a plugin to support MSSQL database monitoring but I am not able to see any type of output from it and monitoring metrics with it. It is showing "No results found".
And it is throwing an error called Failed load deployment server tenant default

Can you please do the needful help on how to configure the Microsoft SQl server APP to see my database performance metrics

[Hemnaath]

Hi Nagadeepthi, hey we have a requirement to monitor SQL error log and data base monitoring using the splunk. I had downloaded the MS-SQL Add-on 1.3 and DB-Connect 3.1. We have 5 indexer instance, 5 Heavy Forwarder instances, three cluster search heads, deployment instances and Deployer instances all are running with Splunk 6.6.1 version.

We have downloaded the SQL Add-on app from splunk base to monitor the Error log file.
Default monitoring stanza provide in MS SQL Add-on

Inputs.conf
ERROR Log for SQL Server 2014
[monitor://C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Log\ERRORLOG*]
sourcetype = mssql:errorlog
disabled = 1

My Question : How to configure inputs.conf to monitor the Error Log file placed under different paths on the remote node.

Example : Test01 Test02 Test03

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

Kindly guide me on how to setup an inputs.conf to monitor Error.log file placed under different paths on different nodes from remote nodes.
thanks in advance.

[phuonguit]

Hi, When I run command line that, I also only see :
MSSQL:Database:Health
MSSQL:Database:Information
MSSQL:Host:Memory
MSSQL:Index:MissingStats
MSSQL:Index:Stats
MSSQL:Instance:Service
MSSQL:Instance:User
Powershell:ScriptExecutionErrorRecord
Powershell:ScriptExecutionSummary
I can't show
MSSQL:Instance:Information
Please help me about the issue

[Hemnaath]

Hi Phuonguit, hey we have a requirement to monitor SQL error log and data base monitoring using the splunk. I had downloaded the MS-SQL Add-on 1.3 and DB-Connect 3.1. We have 5 indexer instance, 5 Heavy Forwarder instances, three cluster search heads, deployment instances and Deployer instances all are running with Splunk 6.6.1 version.

We have downloaded the SQL Add-on app from splunk base to monitor the Error log file.
Default monitoring stanza provide in MS SQL Add-on

Inputs.conf
ERROR Log for SQL Server 2014
[monitor://C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Log\ERRORLOG*]
sourcetype = mssql:errorlog
disabled = 1

My Question : How to configure inputs.conf to monitor the Error Log file placed under different paths on the remote node.

Example : Test01 Test02 Test03

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

Kindly guide me on how to setup an inputs.conf to monitor Error.log file placed under different paths on different nodes from remote nodes.
thanks in advance.

[phuonguit]

Thanks Hemnaath,

I will try

[amiracle]

I figured this one out, finally. Here's what I did:
Windows Server 2008 R2 and Windows 2012 R2 - Open Powershell as Administrator

PS C:\>Get-Execution Policy

If it's Restricted, then do the following:

PS C:\>Set-Execution Policy Bypass

Say Yes to the Execution Policy Change.

Then run Get-ExecutionPolicy and see that it changed to Bypass:

PS C:\> Get-ExecutionPolicy
Bypass

Once you have that done, now you'll need to make one more change.

Open your SQL Server Management Studio and log in as sysadmin (sa). Go to Security ->Logins -> NT AUTHORITY\SYSTEM (Properties) and grant the user sysadmin Server Role. Apply the change and restart your Splunk service. (Thanks Adrian: http://answers.splunk.com/answers/108974/problem-with-powershell-and-splunk_for_sqlserver-app)

Once you have all these steps done, then go into the app and run the Lookup Table Rebuilder (Searches & Reports->Lookup Table Rebuilder)

Lastly, you can run the search:

index=mssql | stats count, values(sourcetype) by host 

You should see the following source types show up:

MSSQL:Database:Health
MSSQL:Host:Memory
MSSQL:Instance:Service
MSSQL:Instance:User
Powershell:ScriptExecutionSummary

[Smile172]

You can check the installed add-ons on the SQL Server (Splunk Technology Add-on for Windows, Splunk Add-on for PowerShell, Splunk Technology Add-on for SQL Server).
Check splunkd.log for Powershell related errors. Did you install the prerequisites for running Powershell on SQL Server?

[nagadeepthi]

i have installed MS SQL server App on my main splunk instance and have completed te required steps to configure it but still it is unable to fetch me the metrics of the database should i do anyhting more to get this information like configuring lookup or etc.,,,, please let me know

[piebob]

did you install the MS SQL Server App, or did you install the DB Connect app? http://apps.splunk.com/app/958/