All Apps and Add-ons

Procedure for database monitoring through Microsoft Sql Server APP

nagadeepthi
Explorer

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
Motivator

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.

0 Karma

phuonguit
New Member

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

0 Karma

Hemnaath
Motivator

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.

0 Karma

phuonguit
New Member

Thanks Hemnaath,

I will try

0 Karma

amiracle
Splunk Employee
Splunk Employee

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
Explorer

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?

0 Karma

nagadeepthi
Explorer

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

0 Karma

piebob
Motivator

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

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...