All Apps and Add-ons

Splunk App for Microsoft SQL Server: Why am I not getting any SQL server data?

halkelley
Path Finder

I've done the install and set the powershell execution policy to bypass, rebuilt the lookups, and I'm still not getting any SQL server data in Splunk

1 Solution

ahall_splunk
Splunk Employee
Splunk Employee

Follow-up answer.

The Exception mentioned in your comment indicates that you have not enabled script execution properly. Microsoft limits the scripts that can be run via PowerShell for security reasons. The default setting is AllSigned, indicating that the scripts must have a digital signature. We do not ship the scripts signed. As a result, you need to ensure the proper execution policy is implemented. In the short term, you can set the proper execution policy by running the following command from an ELEVATED PowerShell console:

Set-ExecutionPolicy RemoteSigned

However, a group policy may over-ride this setting, so ensure your group policy from Active Directory does not reset it for you. If it does, then get the change made in Active Directory.

In addition, the system will not execute "blocked" scripts. When you download a file from the internet, Windows blocks the execution of the file. If you unpack the file without unblocking it, then all the unpacked files are similarly blocked. You may need to go into the path mentioned in the log, right-click on the file, select Properties and unblock the file.

View solution in original post

ahall_splunk
Splunk Employee
Splunk Employee

Follow-up answer.

The Exception mentioned in your comment indicates that you have not enabled script execution properly. Microsoft limits the scripts that can be run via PowerShell for security reasons. The default setting is AllSigned, indicating that the scripts must have a digital signature. We do not ship the scripts signed. As a result, you need to ensure the proper execution policy is implemented. In the short term, you can set the proper execution policy by running the following command from an ELEVATED PowerShell console:

Set-ExecutionPolicy RemoteSigned

However, a group policy may over-ride this setting, so ensure your group policy from Active Directory does not reset it for you. If it does, then get the change made in Active Directory.

In addition, the system will not execute "blocked" scripts. When you download a file from the internet, Windows blocks the execution of the file. If you unpack the file without unblocking it, then all the unpacked files are similarly blocked. You may need to go into the path mentioned in the log, right-click on the file, select Properties and unblock the file.

jeremyfer
Explorer

That is so annoying, and super annoying that it isn't listed in the requirements section of the Monitor Windows data with PowerShell scripts documentation. We aren't going to be changing our hundreds of servers security settings to enable one Splunk input. Unfortunately I only found this issue after wasting time making my powershell script and testing it locally before trying to run in a deployment-app. Many other programs which run powershell remotely (e.g. Octopus Deploy) can run the scripts remotely with the default Windows security settings for Powershell.

0 Karma

halkelley
Path Finder

I figured out I wasn't running the powershell console in ELEVATED mode (run as Administrator) - thanks so much for your help!

halkelley
Path Finder

in following the directions, I previously set the execution policy to "bypass"...when I open a powershell window from ssms and "get-executionPolicy" it is "bypass"

when does the powershell script attempt to execute?...can I repeat it?...the error I have in the powershell log is from 2 days ago, so it is possible I fixed it with the "bypass" setting?

0 Karma

ahall_splunk
Splunk Employee
Splunk Employee

Take a look through the TA-sqlserver inputs.conf - each stanza has an index and a sourcetype. A good search is:

index=mssql | chart count by host,sourcetype

This will tell you which hosts are producing which sourcetypes. Correlate that visually with the list of sourcetypes from your inspection of the inputs.conf file and you will know which pieces are not running. Once you have that, the next step is to look for possible errors. A search that will help there is:

index=_internal source=*powershell*.log

Look for any obvious errors. Anything leap out at you? If nothing does, then take a look at the splunkd.log which you can use a similar search as above.

Let me know what you find out.

halkelley
Path Finder

in the powershell log I see some maybe problematic events:

...sourcetype = powershell-too_small...

...Inner Exception PSSecurityException: File C:\Program Files\Splunk\etc\apps\SA-ModularInput-PowerShell\windows_x86_64\bin\Modules\LocalStorage\LocalStorage.psm1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170...

in the splunkd logs I see a bunch of "GET"s and "POST"s, but nothing seems to be erroring

thanks for your help!
any ideas where to go from here?

halkelley
Path Finder

I get 0 results for anything with index=mssql --- that's what I don't understand

0 Karma
Get Updates on the Splunk Community!

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...

Announcing the public preview of Splunk Data Management Ingest Processor

We are pleased to announce the public preview of Splunk’s latest data management capability - Ingest Processor ...

Splunk Lantern is up for three awards! Can we get your vote?

It’s been a big news month for Splunk Lantern. Along with celebrating the publication of our 1000th article, ...