topic Re: Can Splunk App for Microsoft SQL Server monitor SQL server 2014? in All Apps and Add-ons

Can Splunk App for Microsoft SQL Server monitor SQL server 2014?

managedservice1 — Mon, 28 Sep 2020 17:11:46 GMT

Hi There, Did anyone here managed to monitor SQL server 2014? The app doc suggests that it can only monitor 2008 and 2012 R2.

I did install the app and have upload the TA-SQLServer, SA-ModularInput-PowerShell and Splunk_TA_windows into my SQL server 2014. I tried to run the powershell scripts individually on the SQL server and see the following error:

I have also enabled gp - turn on script and execution (allow local scripts and remote signed scripts)

Common.psm1' was not loaded because no valid module file was found in any module directory

I get that for all the powershell script.

Any help will be highly appreciated.

Re: Can Splunk App for Microsoft SQL Server monitor SQL server 2014?

managedservice1 — Wed, 30 Jul 2014 20:45:42 GMT

I have resolved the issue with common.psm1 however now I am having the following issue

Import-LocalStorage : The term 'Import-LocalStorage' is not recognized as the name of a cmdlet - on databases, dbinstances, dbusers, indexhealth

Does any here know how to resolve this?

Thanks

Re: Can Splunk App for Microsoft SQL Server monitor SQL server 2014?

amiracle — Mon, 28 Sep 2020 17:13:51 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

Re: Can Splunk App for Microsoft SQL Server monitor SQL server 2014?

managedservice1 — Thu, 31 Jul 2014 22:17:47 GMT

Hello Thanks for that, it did work. But one question do you know how to resolve the following issue ?

PS C:\Program Files\SplunkUniversalForwarder\etc\apps\TA-SQLServer\bin> .\databases.ps1
Import-LocalStorage : The term 'Import-LocalStorage' is not recognized as the name of a cmdlet, function, script file,
or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and
try again.

As mentioned above I do have SA-ModularInput-PowerShell under C:\Program Files\SplunkUniversalForwarder\etc\apps

Do I need to specify anything else ??
Thanks

Re: Can Splunk App for Microsoft SQL Server monitor SQL server 2014?

amiracle — Thu, 31 Jul 2014 22:55:25 GMT

First, don't try to just run the databases.ps1 script at the powershell command line, it will not run. It has to be run using the Invoke-Monitor script. The SA-Modularinput-Powershell is correctly added and it will actually read the stanzas from the TA-SQLServer/local/inputs.conf that specify 'powershell.'

The way I resolved that issue was to go through the second part of my answer, which was to give the NT AUTHORITY SYSTEM account sysadmin privileges through the MSSQL Management Studio. Don't try and run the PS at a command line since it's basically calling the Common.PSM1 script which is why you're seeing the issue with the Import-LocalStorage.

Here's another tip, once you have it correctly setup, you'll see the XML files show up in your $SPLUNK_HOME/var/lib/splunk/modularinputs/powershell/Databases directory.

I hope that helps you out.

Re: Can Splunk App for Microsoft SQL Server monitor SQL server 2014?

managedservice1 — Thu, 31 Jul 2014 23:25:28 GMT

Once again thank you for fast reply. I have looked at the dir I can there are XML files under DBInstances and IndexStats but there is nothing under Databases or DBUsers. I guess that because we are running Instances rather databases....

Re: Can Splunk App for Microsoft SQL Server monitor SQL server 2014?

amiracle — Thu, 31 Jul 2014 23:30:45 GMT

I had this exact same issue and the way I resolved it was giving the authorization to the NT AUTHORITY account sysadmin privilege in the MSSQL Management Studio. This is a permission issue, so your best bet is to change the settings and then restart Splunk. Once it restarts, then run the Lookup Rebuilder and it should all work. Let me know if that works.

Re: Can Splunk App for Microsoft SQL Server monitor SQL server 2014?

hethaishibk — Sat, 09 Jun 2018 10:02:54 GMT

the XML's are created $SPLUNK_HOME/var/lib/splunk/modularinputs/powershell/Databases directory. but still i dont see the data coming to Splunk search head.

the mssql index that has to created is it metric or event table that has to be created. Please confirm