Issue with deploying Splunk App for MS SQL

[eashwar]

For the past few days I am trying to deploy the sql app. I found not all the powershell scripts returns results. Below are the 4 sourcetypes in the mssql index.

MSSQL:Instance:Service

Powershell:ScriptExecutionSummary

Powershell:ScriptExecutionErrorRecord

MSSQL:Host:Memory

Host:Memory and Instance:Service tells me that that there is no problem with the execution of the PS script.

When I was doing some research about why the Lookup generators are showing no-results I found a lot of other sourcetypes missing.

Below is the result of this search eventtype=mssql sourcetype="Powershell:ScriptExecutionErrorRecord" | dedup ErrorMessage | table ErrorMessage Exception

ErrorMessage

Could not find file 'C:\Program Files\SplunkUniversalForwarder\var\lib\splunk\modinputs\powershell\DBUsers\DBUsers.xml'.

Could not find file 'C:\Program Files\SplunkUniversalForwarder\var\lib\splunk\modinputs\powershell\DBInstances\DBInstances.xml'.

Could not find file 'C:\Program Files\SplunkUniversalForwarder\var\lib\splunk\modinputs\powershell\Databases\Databases.xml'.

Exception

System.IO.FileNotFoundException: Could not find file 'C:\Program Files\SplunkUniversalForwarder\var\lib\splunk\modinputs\powershell\DBUsers\DBUsers.xml'. File name: 'C:\Program Files\SplunkUniversalForwarder\var\lib\splunk\modinputs\powershell\DBUsers\DBUsers.xml' at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share) at System.Management.Automation.PathUtils.OpenFileStream(String filePath, PSCmdlet command, Boolean isLiteralPath)

System.IO.FileNotFoundException: Could not find file 'C:\Program Files\SplunkUniversalForwarder\var\lib\splunk\modinputs\powershell\DBInstances\DBInstances.xml'. File name: 'C:\Program Files\SplunkUniversalForwarder\var\lib\splunk\modinputs\powershell\DBInstances\DBInstances.xml' at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share) at System.Management.Automation.PathUtils.OpenFileStream(String filePath, PSCmdlet command, Boolean isLiteralPath)

System.IO.FileNotFoundException: Could not find file 'C:\Program Files\SplunkUniversalForwarder\var\lib\splunk\modinputs\powershell\Databases\Databases.xml'. File name: 'C:\Program Files\SplunkUniversalForwarder\var\lib\splunk\modinputs\powershell\Databases\Databases.xml' at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share) at System.Management.Automation.PathUtils.OpenFileStream(String filePath, PSCmdlet command, Boolean isLiteralPath)

[amiracle]

Are you still having trouble with this app? Have you tried troubleshooting the modular input on the forwarder?

[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

[FunPolice]

Me too, although the source is "C:\Program Files\SplunkUniversalForwarder\etc\apps\SA-ModularInput-PowerShell\windows_x86_64\bin\Modules\LocalStorage\LocalStorage.psm1," so it looks like it's a problem with the PowerShell app and not the SQL app.

[fielding_rodney]

Im having the same issue. Anyone have a resolution?