Missing sourcetypes for Splunk App for Microsoft SQL Server

[SheridanCollege]

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?

[amiracle · Accepted solution]

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]

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

[SheridanCollege]

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
---------------------------