topic Re: SQL Server Splunk App Does Not Show any Servers in All Apps and Add-ons

SQL Server Splunk App Does Not Show any Servers

josephkandi — Fri, 30 Aug 2013 16:11:37 GMT

Hi
I have went through the documentation to install the SQL Server Splunk app but no SQL Server are being shown in the app.

I am able to view the Server Audit logs from the Windows Application log. I can see data on the Logins and Logouts to SQL Server. I am also unable to drill down. An error shows up and quickly disappears. Any hep greatly appreciated.

Re: SQL Server Splunk App Does Not Show any Servers

Lehanov — Mon, 28 Oct 2013 10:32:34 GMT

1 - First of all, ensure that you have
properly deployed Powershell and
Windows TA-s on forwarder. Powershell TA have a most
cranky installation process - double
check script execution rights and
related powershell script execution errors in splunkd.log

2 - check that you have SQL TA properly
deployed

3 - ensure you have SQL
audit events in Splunk in simple
search

4 - run lookup generating
scripts in SQL app

Re: SQL Server Splunk App Does Not Show any Servers

matt4321 — Thu, 16 Jan 2014 19:31:25 GMT

I ran into this issue as well.. My issue ended up being execution policy settings in Powershell by default it does not allow scripting from the service account. You have to run a powershell cmd to change it from restricted to something less, to test you can set it to unrestricted then step down the policy 1 at a time. Once I did this it started working right away.

This should be added to check in the documentation as it was not there and it is a Powershell default setting if you just installed it via the documentation.

Matt

Re: SQL Server Splunk App Does Not Show any Servers

Akili — Fri, 30 May 2014 11:47:33 GMT

i agree with lehanov. the ad-dons should be placed on the forwarder
if you check locally on the forwarder var/log/splunk/powershell and powershell2 , you will see any possible error.

Re: SQL Server Splunk App Does Not Show any Servers

amiracle — Mon, 28 Sep 2020 17:14:02 GMT

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