All Apps and Add-ons

Missing sourcetypes for Splunk App for Microsoft SQL Server

SheridanCollege
Explorer

We're running 1 search head and 2 indexers, splunk v6.1.1 on Ubuntu 12.04. MS SQL Servers run on Windows 2008 R2. We are having issues with our SQL Servers. I am trying to install Splunk App for Microsoft SQL Servers. I've got it to the point where the following sourcetypes are showing in splunk:

MSSQL:Database:Health
MSSQL:Host:Information
MSSQL:Host:Memory
MSSQL:Index:MissingStats
MSSQL:Index:Stats
MSSQL:Instance:Service
MSSQL:Instance:User

However, I am missing the sourcetypes: MSSQL:Instance:Information, MSSQL:Database:Information, which is the reason the Operations Overview Dashboard is showing no results.

Anyone have an idea on how to troubleshoot?

0 Karma
1 Solution

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

View solution in original post

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

SheridanCollege
Explorer

PS> Import-Module 'C:\Program Files\SplunkUniversalForwarder\etc\apps\SA-ModularInput-PowerShell\windows_x86_64\bin\Modules\LocalStorage\LocalStorage.psm1'
PS> .\Invoke-MonitoredScript.ps1

cmdlet Invoke-MonitoredScript.ps1 at command pipeline position 1
Supply values for the following parameters:
Command: .\dbinstances.ps1

SplunkSourceType : Powershell:ScriptExecutionSummary
Identity         : a55a383a-fb73-4851-b3fd-7372fccb03ac
InvocationLine   : .\Invoke-MonitoredScript.ps1
TerminatingError : False
ErrorCount       : 0
0 Karma

SheridanCollege
Explorer

So MSSQL:Instance:Information sourcetype is a powershell input. I am able to run this powershell successfully on the SQL Server, but I get no results on the search head for sourcetype=MSSQL:Instance:Information. Any ideas?

------ inputs.conf --------
[powershell://DBInstances]
script = & "$SplunkHome\etc\apps\TA-SQLServer\bin\Invoke-MonitoredScript.ps1" -Command ".\dbinstances.ps1"
schedule = 0 */5 * ? * *
index = mssql
sourcetype = MSSQL:Instance:Information
source = Powershell
---------------------------
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...