Get SCSM 2012 Database Info PowerShell Script

•January 11, 2017 • Leave a Comment

binoculars-880788_1920

So over the last several years I have had the need to discover what the name of both the Database and Database Servers are for System Center Service Manager. The below script will pull the data from the registry. For right now you can run it both on DataWarehouse Management Server or the Management Server, but you will get an error for Reporting Database Details when ran on the SCSM Management Server. This will work with SCSM 2010, 2012, 2012 R2. (I haven’t gotten a chance to confirm 2016 registry locations yet but I will be sure to update once I do.)  When I get some time, I think I will wrap this up in a module or function and publish it to codeplex. I will be sure to update this post once I have done that.


########################################################
# Version 1.0
# Author Date: 1/11/2017
# Author: Josh Ancel
# Get-SCSMDatabasesInfo.ps1
#
########################################################

$dbInfo = Get-ItemProperty "hklm:\software\microsoft\system center\2010\common\database"
$reportingDBInfo = Get-ItemProperty "hklm:\software\microsoft\system center\2010\common\reporting"

Write-Host "Database Details" -foreground "yellow"
$dbInfo | fl -Property DatabaseServerName, DatabaseName, RepositoryDatabaseName, DataMartDatabaseName, OMDataMartDatabaseName, CMDataMartDatabaseName, StagingDatabaseName, RepositorySQLInstance, DataMartSQLInstance, OMDataMartSQLInstance, CMDataMartSQLInstance, StagingSQLInstance

Write-Host "Reporting Database Details" -foreground "yellow"
$reportingDBInfo | fl -Property Server, ServerInstance, WebSeviceURL
#EOF

Another little piece of Code you can run on your on your SCSM Management Servers to get you SCSM SQL Server Analysis Database is below. This will tell you what server and instance your DWASDataBase is running on.

$class= get-scclass –Name Microsoft.SystemCenter.ResourceAccessLayer.ASResourceStore –ComputerName DataWarehouseManagmentServerName  
$OLAPServer= get-scclassinstance –class $class –ComputerName DataWarehouseManagmentServerName
$OLAPServer.Server
$OLAPServer.DataService

citation: https://technet.microsoft.com/en-us/library/dn464298(v=sc.12).aspx 

 

I hope this helps others as much as it has helped me!

Good Luck!

 

Advertisements

Useful SCCM 2012 Queries

•January 9, 2017 • Leave a Comment

browse-42931_1280Below is a list of useful SCCM SQL queries for reporting or Collection creations.
Unfortunately, I am unable to recall where I complied all these queries from. I cannot take credit for them, however if you know where they came from please feel free to reply below and I will be happy to update citing the sources. 

All Systems with SCCM Client

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where (ClientType = 1) OR (SMS_R_System.AgentEdition0 = 5)

All systems with 64-bit OS

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_COMPUTER_SYSTEM.SystemType = “x64-based PC

All systems with 32-bit OS

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = “X86-based PC

All Systems with SCCM client version “X.xx.xxxx.xxx”

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ClientVersion = “5.00.8355.1306

Look here for client version history Click

All Systems with specific IP or IP rang

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System WHERE SMS_R_System.IPSubnets LIKE ‘192.168.11.%

All Systems with Windows 10

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where OperatingSystemNameandVersion like ‘%Workstation 10.0%

All Systems with windows 8.1

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where OperatingSystemNameandVersion like ‘%Workstation 6.3%

All Systems with windows 7

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like “%Workstation 6.1%

All Systems with Server 2012 or 2012 R2

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where OperatingSystemNameandVersion like ‘%Server 6.2%‘ or OperatingSystemNameandVersion like ‘%Server 6.3%

All Systems with Server 2008 or 2008 R2

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where OperatingSystemNameandVersion like ‘%Server 6.0%‘ or OperatingSystemNameandVersion like ‘%Server 6.1%

All Systems with server 2003 or 2003 R2

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where OperatingSystemNameandVersion like ‘%Server 5.2%

All System from specific Active Directory OU

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemOUName = “DomainName.com/ComputersLocation

All Systems with SCCM Console

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like ‘%Configuration Manager Console%

All SCCM Distribution Points

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemRoles = ‘SMS Distribution Point’

All Physical Servers

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.IsVirtualMachine = ‘True’) and SMS_R_System.OperatingSystemNameandVersion like ‘Microsoft Windows NT%Server%’

All Virtual Servers

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.IsVirtualMachine = ‘True’ and SMS_R_System.OperatingSystemNameandVersion like ‘Microsoft Windows NT%Server%’

All Systems with Windows Server OS

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where OperatingSystemNameandVersion like ‘%Server%’

Collection computers by Model

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Model like “%4200%

Collection computers by Brand

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Manufacturer like “Dell%

All systems with Office 2016

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName = “Microsoft Office Professional Plus 2016

All systems with Firefox (All versions)

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like “Mozilla Firefox%

New Year a New Look!

•January 9, 2017 • Leave a Comment

pexels-photo-27843

 

I am starting off this new year by updating the look of my Blog! It has been a while since I have posted anything. I have tons of drafts I just haven’t gotten cleaned up to be posted. This year I am going to be posting more. Starting with getting getting some of my drafts finalized and published.

 

Happy New Year to you all!

SilverSeekKB – Microsoft Knowledge Base Build search

•April 2, 2014 • Leave a Comment

Big thanks to my colleague that works over at Microsoft for sharing this with me!

So… short story a colleague of mine over at Microsoft sent over the following link. This is a fantastic tool for anyone who does patching or remediates patching for security audits. This tool allows you to search the files, build versions  and MS Products and links you to the all the KB articles associated with it.  Also, it has APIs that you can put your own hooks into it so you can integrate this into other solutions.

 

This is a great tool!

 

I hope other find this as exciting as I do, it will defiantly help reduce some of my search times.

 

Good Luck!

Congratulations Packt on 2000 eBooks!

•March 20, 2014 • Leave a Comment

Hey all!

I thought I would pass along this great deal Packt Publishing is offering right now.

Buy One, Get One Free on all of #Packt’s 2000 eBooks! bit.ly/1j26nPN #Packt2k

Packt 2000th Book Sale

Best of Luck!

SCOM 2012 – SQL Reconnect interval

•February 25, 2014 • 2 Comments

So I found out the reason why I kept getting the SQL reconnect issue with Ops Manager 2012 that I was having here – Powershell Script to restart SCOM. It turns out that by default Ops Manager 2012 does not automatically try to reconnect to its SQL server. I believe the default interval is like 60 seconds so you will probably want to change that registry key as well. Below is the fix for enabling the SQL reconnect interval.

To enable the automatic recovery feature on the management server, follow these steps:

  1. Start Registry Editor.
  2. Locate and then click the following registry subkey:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center\2010\Common\DAL
  3. Create the following two registry entries:
    • DALInitiateClearPoolType: DWORD
      Decimal value: 1
    • DALInitiateClearPoolSecondsType: DWORD
      Decimal Value: 60Note The DALInitiateClearPoolSeconds setting controls when the management server drops the current connection pool and when the management server tries to reestablish an SQL connection. We recommend that you set this setting to 60 seconds or more to avoid performance issues.
  4. Restart the System Center Management service on the management server.

Best of luck!

 
%d bloggers like this: