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