SCCM Configmgr SQL views

SQL Query to get all views Select distinct table_name,COLUMN_NAME From Information_Schema.columns Order by table_name Source: https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-R2-SQL-5fefdd3b http://eskonr.com/2013/04/download-sccm-2012-sql-views-documentation/

Advertisements

Queries for Boundary,Boundary Groups and Devices info

Query Devices,IP Address and IP Subnet per Device select distinct A.Name0 as 'PC Name',c.IPAddress0 as 'IP Address',D.IP_Subnets0 as 'IP Subnet' from v_R_System A inner join v_FullCollectionMembership B on A.ResourceID=B.ResourceID Inner join v_GS_NETWORK_ADAPTER_CONFIGUR C ON A.ResourceID=C.ResourceID Inner Join v_RA_System_IPSubnets D ON A.ResourceID=D.ResourceID where CollectionID='SMS00001' and C.IPEnabled0='1' group by A.Name0,c.IPAddress0 ,D.IP_Subnets0 order by A.Name0,c.IPAddress0 ,D.IP_Subnets0 Get all... Continue Reading →

How to find quick compliance percentage of Patch MS17-010 in your environment?

select catinfo.CategoryInstanceName as Vendor0, ui.ArticleID as ArticleID, ui.BulletinID as BulletinID, ui.Title as Title, NumPresent as Present, NumMissing as Missing, NumNotApplicable as NotApplicable, NumUnknown as Unknown, NumTotal as Total, PCompliant=convert(numeric(5,2), (isnull(NumPresent, 0)+isnull(NumNotApplicable, 0))*100.0 / isnull(nullif(NumTotal, 0), 1)), PNotCompliant=convert(numeric(5,2), (isnull(NumMissing, 0))*100.0 / isnull(nullif(NumTotal, 0), 1)), PUnknown=convert(numeric(5,2), (isnull(NumTotal - (isnull(NumPresent, 0)+isnull(NumMissing, 0)+isnull(NumNotApplicable, 0)), 0))*100.0 / isnull(nullif(NumTotal, 0), 1)),... Continue Reading →

Re-install / Change path of SMS_SITE_SQL_BACKUP_SITESERVER-FQDN

Had an issues where a SCCM 2012 Primary Site did want to install/re-install the SMS_SITE_SQL_BACKUP_[SITESERVER-FQDN] component. Got errors that the Installation path did not exist.This was easy to correct. Change the string named value “Installation Directory” in the key HKEY_LOCAL_MACHINESOFTWAREMicrosoftSMSComponentsSMS_SITE_COMPONENT_MANAGERMultisite Component Servers[DB-SERVER-FQDN] with [DB-SERVER-FQDN] being the placeholder for the FQDN of the database server hosting... Continue Reading →

SQL Report to list Boundary Group with Site System Details

select sys1.Name, sys1.DefaultSiteCode, (select SUBSTRING(sys2.ServerNALPath, CHARINDEX('\\', sys2.ServerNALPath) + 2, CHARINDEX('"]', sys2.ServerNALPath) - CHARINDEX('\\', sys2.ServerNALPath) - 3 ) + CASE sys2.Flags WHEN '1' Then ' (Slow)' WHEN '0' THEN '' END + '; ' as 'data()' from vSMS_BoundaryGroupSiteSystems as sys2 where sys1.GroupID=sys2.GroupID for XML path('')) as 'Site System', (select sys4.Value + '; ' as 'data()' from... Continue Reading →

SCCM Unused Packages

Find Unused Packages in SCCM DECLARE @ObjectTypes TABLE (ObjectType INT PRIMARY KEY, TypeDescription VARCHAR(46)); INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(2,'Package'); INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(3,'Advertisement'); INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(7,'Query'); INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(8,'Report'); INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(9,'MeteredProductRule'); INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(11,'ConfigurationItem'); INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(14,'OperatingSystemInstallPackage'); INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(17,'StateMigration'); INSERT... Continue Reading →

Report to list Boundary Group with Site System Details

select sys1.Name, sys1.DefaultSiteCode, (select SUBSTRING(sys2.ServerNALPath, CHARINDEX('\\', sys2.ServerNALPath) + 2, CHARINDEX('"]', sys2.ServerNALPath) - CHARINDEX('\\', sys2.ServerNALPath) - 3 ) + CASE sys2.Flags WHEN '1' Then ' (Slow)' WHEN '0' THEN '' END + '; ' as 'data()' from vSMS_BoundaryGroupSiteSystems as sys2 where sys1.GroupID=sys2.GroupID for XML path('')) as 'Site System', (select sys4.Value + '; ' as 'data()' from... Continue Reading →

SCCM report packages status on Distribution Point

Report for packages which are Pending: select s.sitecode,s.packageid,p.name,s.sourceversion as "dpversion",p.storedpkgversion as latesteversion from v_PackageStatusDistPointsSumm s join smspackages p on s.packageid = p.pkgid where s.installstatus !='Package Installation complete' and s.servernalpath like '%DPserverName%' order by s.packageid Report for packages which are Installed : select s.sitecode,s.packageid,p.name,s.sourceversion as "dpversion",p.storedpkgversion as latestversion from v_PackageStatusDistPointsSumm s join smspackages p on s.packageid... Continue Reading →

Blog at WordPress.com.

Up ↑