script to find if SSRS / SSIS service is installed

Question

script to find if SSRS / SSIS service is installed

tan110

Hall of Fame

Points: 3855
More actions
December 30, 2016 at 6:54 am

#401294

Was wondering if anyone had/knew of a script that would check to see if SSRS/SSIS is installed on an instance. We have hundreds of SQL instances and trying to find an easier way of checking for this as opposed to doing this manually.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

with powershell, you can check the services that exist on multiple machines, as long as they are on the LAN/you can connect.

You don't want to handcuff yourself into a TSQL command, since you don't want to have to connect to SQL, and then query the registry

here's a decent example, where you would feed an array of server names

$ComputerNames = "Prod01","DEV01","Cluster01"

##SQL Services

Get-Service MSSQL* -ComputerName $ComputerNames | format-table -property MachineName, Status, Name, DisplayName -auto

##Tabular or OLAP services

Get-Service *OLAP* -ComputerName $ComputerNames | format-table -property MachineName, Status, Name, DisplayName -auto

##SSIS

Get-Service *DTS* -ComputerName $ComputerNames | format-table -property MachineName, Status, Name, DisplayName -auto

##Reporting Services

Get-Service *report* -ComputerName $ComputerNames | format-table -property MachineName, Status, Name, DisplayName -auto

##All Services for review

#Get-Service * -ComputerName $ComputerNames | format-table -property MachineName, Status, Name, DisplayName -auto

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Kevin3NF SSCarpal Tunnel Points: 4765 More actions · Answer 2

It may sound a bit-self serving, but if you have a server list in a text file, you can loop through that quite easily using Lowells commands coupled with techniques I documented as I was learning and writing my first (and only) powershell script:

Old DBA learns new (PoSH) trick[/url]

------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NF DallasDBAs.com/Blog Why is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]

tan110 Hall of Fame Points: 3855 More actions · Answer 3

Thank you, much appreciated! Exactly what i was looking for.

Lowell SSC Guru Points: 323518 More actions · Answer 4

tan110 (12/30/2016)
Thank you, much appreciated! Exactly what i was looking for.

you might run into some permissions issues, you might need a domain admin level permission;

i can query servers i KNOW i'm local admin on no problem, but for any server i never received local admin before, but know exists, i got this error:

get-service : Cannot open Service Control Manager on computer 'CN=TEMPADDC01,CN=Computers,DC=MYDOMAIN,DC=org'. This operation might require other privileges.

in my case I was querying active directory for all servers, and trying to iterate through the lsit:

import-module ActiveDirectory

[array] $AllSQLServers = Get-ADComputer -filter { (OperatingSystem -like "*Windows*") -and (OperatingSystem -like "*Server*") -and (Enabled -eq $True) } -properties Name, ServicePrincipalNames, DistinguishedName, OperatingSystem, passwordLastSet

get-service -name MSSQL* -computername $AllSQLServers | format-table -property MachineName, Status, Name, DisplayName -auto

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tan110 Hall of Fame Points: 3855 More actions · Answer 5

Would it be possible to do this at the database instance level?

Kevin3NF SSCarpal Tunnel Points: 4765 More actions · Answer 6

Since the services are what you are really looking for, you will see all of them for each Server you query.

If you search for SQL Server Service on YourServer0546, which has 5 instances installed, you will see service listed 5 times.

From my local machine, with 3 Dev instances (2014 is default, 2016 are both named):

MachineName Status Name DisplayName

----------- ------ ---- -----------

. Stopped MSOLAP$SQL2016 SQL Server Analysis Services (SQL2016)

. Running MSSQL$SQL2016 SQL Server (SQL2016)

. Stopped MSSQL$SQL2016_B SQL Server (SQL2016_B)

. Running MSSQLFDLauncher SQL Full-text Filter Daemon Launcher (MSSQLSERVER)

. Running MSSQLFDLauncher$SQL2016 SQL Full-text Filter Daemon Launcher (SQL2016)

. Stopped MSSQLLaunchpad$SQL2016 SQL Server Launchpad (SQL2016)

. Running MSSQLSERVER SQL Server (MSSQLSERVER)

. Stopped MSSQLServerOLAPService SQL Server Analysis Services (MSSQLSERVER)

. Stopped ReportServer$SQL2016 SQL Server Reporting Services (SQL2016)

. Stopped SQL Server Distributed Replay Client SQL Server Distributed Replay Client

. Stopped SQL Server Distributed Replay Controller SQL Server Distributed Replay Controller

. Running SQLAgent$SQL2016 SQL Server Agent (SQL2016)

. Stopped SQLAgent$SQL2016_B SQL Server Agent (SQL2016_B)

. Stopped SQLBrowser SQL Server Browser

. Running SQLSERVERAGENT SQL Server Agent (MSSQLSERVER)

. Stopped SQLTELEMETRY$SQL2016 SQL Server CEIP service (SQL2016)

. Running SQLTELEMETRY$SQL2016_B SQL Server CEIP service (SQL2016_B)

. Running SQLWriter SQL Server VSS Writer

. Stopped SSASTELEMETRY$SQL2016 SQL Server Analysis Services CEIP (SQL2016)

Generated by:

Get-Service *SQL* | format-table -property MachineName, Status, Name, DisplayName -auto

------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NF DallasDBAs.com/Blog Why is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 7

tan110 (12/30/2016)
Would it be possible to do this at the database instance level?

so, are you inventorying servers/instances, or databases? for me, that's two different things/levels of documentation.

or are we on a DBA documentation spree, and we are documenting everything?

if we are still using powershell, I've adapted a script from this post, which is an excellent starting point for documenting servers and databases and jobs and stuff.

http://www.sqlservercentral.com/Forums/Topic1240480-1351-1.aspx#

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tan110 Hall of Fame Points: 3855 More actions · Answer 8

Doing an inventory of where SSIS & SSRS is installed, not databases.