SCCMinfo

SCCMinfo

configuration manager

SCCM SQL Query – Part1

by

 

Here we are giving some SQL queries which can help to get common management required reports for ongoing jobs to be done, also it helps to get quick information on the report which is been looking for. These queries should work straight away by copying and pasting in SQL management studio and give output as mentioned for each query. There could be chances it may not work because changes in either SQL table or SQL Views. These queries also can be used as reference queries to build other queries for desired data reports.

 

SCCM SQL query Add Remove Programs? Or SCCM query Add Remove Programs display name and version? Or SCCM query for Add Remove Programs? Or SCCM query all software installed on all computers?

 

This query is going to give detailed information on software installed on client computers from Add Remove Program List. It would show Computer Name, Software Name, Username, Software Publisher, Software Name, Software Version.

 

 select sys.Netbios_Name0 , sys.User_Name0, arp.publisher0, arp.displayname0, arp.version0
 from v_R_System_Valid sys
 join v_gs_Add_Remove_programs arp on arp.ResourceID=sys.ResourceID

 

SCCM SQL query for specific software installed?

 

This query is going to give detailed information on specific software’s installed on client computers from Add Remove Program List. It would show Computer Name, Software Name, Username, Software Publisher, Software Name, Software Version.

 

 select sys.Netbios_Name0 , sys.User_Name0, arp.publisher0, arp.displayname0, arp.version0
 from v_R_System_Valid sys
 join v_gs_Add_Remove_programs arp on arp.ResourceID=sys.ResourceID
 where (ARP.DisplayName0 like '%(App Name A)%' and ARP.DisplayName0 like '% App Name B%')

 

SCCM SQL query software not installed?

 

This query is going to give detailed information on specific software not installed on client computers from Add Remove Program List. It would show Computer Name, Username, Software Publisher, Software Name, Software Version.

 

 select sys.Netbios_Name0 , sys.User_Name0, arp.publisher0, arp.displayname0, arp.version0
 from v_R_System_Valid sys
 join v_gs_Add_Remove_programs arp on arp.ResourceID=sys.ResourceID
 where (ARP.DisplayName0 Not like '%(App Name A)%' and ARP.DisplayName0 Not like '% App Name B%')

 

SQL query to get serial number SCCM?

 

This query is going to get the serial number of computers with column Computer Name, Username, Serial Number.

 

 select sys.Netbios_Name0 , sys.User_Name0, bios.SerialNumber0
 from v_R_System_Valid sys
 join v_GS_PC_BIOS bios on bios.ResourceID=sys.ResourceID

 

SCCM SQL query to find OS version?

 

This query is going to get the OS version of computers with column Computer Name, OS Name, OS Version, OS Install Date.

 

 select sys.Netbios_Name0, os.Caption0 as OS_Name, os.Version0 as OS_Version ,os.InstallDate0
 from v_GS_OPERATING_SYSTEM os
 join v_R_System sys on sys.ResourceID=os.ResourceID
 join v_R_System_Valid sysv on sysv.ResourceID=sys.ResourceID

 

SCCM SQL query to find Chassis Type?

 

This query is going to get the Chassis Type of computers with column Computer Name, Chassis Type.

 

 select distinct sys.Netbios_Name0, CASE en.ChassisTypes0 WHEN 8  THEN'Laptop' WHEN 9  THEN'Laptop'  
 WHEN 10 THEN'Laptop' WhEN 11 THEN'Laptop' WHEN 12 THEN 'Laptop' WHEN 14 THEN 'Laptop' WHEN 
 18 THEN 'Laptop' WHEN 21 THEN 'Laptop' WHEN 3 THEN 'Desktop' WHEN 4 THEN 'Desktop' WHEN 5 
 THEN 'Desktop' WHEN 6 THEN 'Desktop' WHEN 7 THEN 'Desktop' WHEN 15 THEN 'Desktop' end as  
 'Types' 
 from v_R_System_Valid as sys
 join v_GS_SYSTEM_ENCLOSURE as en on en.ResourceID=sys.ResourceID

 

SCCM SQL query collection members?

 

This query is going to get all members from specified collection ID with column Collection Name, Resource ID.

 

 select fscol.name, fscol.resourceid
 from v_cm_res_coll_(Collection ID) fscol

 

Note: Replace “(Collection ID)” with collection ID

 

SCCM SQL query installed patches?

 

This query is going to get MS Update status with column Computer Name, Deployment Name, Collection ID, Assignment Name, Collection Name, Reboot Status, Username.

 

 select distinct sys.netbios_name0, cl.name, cm.collectionid, cs.AssignmentName, cs.CollectionName,  
 cst.LastEnforcementIsRebootSupressed, cst.UserID
 From v_r_system_valid sys
 join v_fullcollectionmembership cm on cm.resourceid=sys.resourceid
 join v_collection cl on cl.collectionid=cm.collectionid
 join v_CIAssignment cs on cs.CollectionID=cm.CollectionID
 join v_CIAssignmentStatus cst on cst.ResourceID=sys.ResourceID
 where cs.AssignmentName like '%Microsoft_Updates_%'

 

SCCM SQL query for installed updates? And SCCM SQL query to find KB installed?

 

Here is another query to get the software installed details with column Computer Name, Bulletin ID, Article ID, Title Name, Patch Month, Status, Assignment Name.

 

 SELECT distinct SYS.Netbios_name0 , UI.BulletinID, UI.ArticleID, UI.Title,datename(year, UI.datecreated)+'-'+ datename (MONTH, UI.datecreated) as 'PatchMonth'  ,CASE when UCS.Status=2 then 'Required' WHEN UCS.Status=3 then 'Installed' when UCS.Status=0 then 'Unknown'  end as 'Status' ,UDS.AssignmentName
 FROM v_R_System_valid AS SYS
 LEFT OUTER JOIN v_Update_ComplianceStatusAll UCS ON SYS.ResourceID = UCS.ResourceID 
 Left outer JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
 left outer join v_FullCollectionMembership CM on cm.ResourceID=sys.ResourceID 
 left outer join v_UpdateDeploymentSummary UDS on uds.CI_ID=ui.CI_ID
 left outer join v_Collection cl on cl.CollectionID=cm.CollectionID

 

SCCM SQL query to find active clients

 

This query help to get information of client is active or Obsolete with column name Name, Obsolete, Active, Domain

 

 select name , IsObsolete, IsActive, Domain 
 from v_CM_RES_COLL_(Collection ID)

Note: Replace “(Collection ID)” with collection ID

 

 

SCCM query all systems with specific software product name?

 

This query is going to give detailed information on particular software’s installed on client computers from Add Remove Program List. It would show Computer Name, Software Name, Username, Software Publisher, Software Name, Software Version.

 

 select sys.Netbios_Name0 , sys.User_Name0, arp.publisher0, arp.displayname0, arp.version0
 from v_R_System_Valid sys
 join v_gs_Add_Remove_programs arp on arp.ResourceID=sys.ResourceID
 where arp.displayname0 like ‘%Inte%’

 

Note: Replace “Inte” with wild characters of the product name looking for.

 

Find here the link for the SQL table and views.

 

Please share your inputs or if any help required in comment box.

SCCM Server Setup Wizard – List installation requirements

by

To save time, confusion and to have clarity, please consider below inputs and be ready with the information in handy for installing Configuration Manager Central Administration Site or Primary Site.

 

  1. Decide on Site to be selected, Central Administration Site or Primary Site
  2. Be ready with the license product key to be entered
  3. Be ready with setup downloaded file with Setup Downloader. With this you do not require to download at the time of Site installation, you can select previously downloaded files by giving path of those files.
  4. Server Language selection would be by default as English which cannot be changed.
  5. Client Language selection would be by default as English which cannot be changed.
  6. Be ready with the Site Code to be used. Each Site code must be unique.
  7. Be ready with the friendly and easily identifiable Site Name accordingly to the Site you would want to be known.
  8. Decide on folder path for the installation as it cannot be changed later.
  9. Be ready with SQL server FQDN (Fully Qualified Domain Name), Instance Name, Database Name, and SSB (by default it uses 4022) port.
  10. Be ready with the path for SQL server data file and the SQL server log files.
  11. In case if you decide to install SMS Provider to be remote server then be ready with FQDN of the same else by default it takes site server’s name. Additional SMS provider server can be configured later.
  12. Be ready with input to give for client communication to site server on HTTP or HTTPS, in case to go for HTTPS for secure communication client’s computer must have valid PKI certificate.
  13. Decide on which server to be act as Management Point and Distribution Point Site System, and then be ready with FQDN for the servers.
  14. Service Connection Point to be selected in case of installing CAS or stand-alone Primary Site, at the time of installing child Primary Site this can be skipped.

 

Note: This information is not for installing Secondary Site as it does not support installation using Setup Wizard or by command lines. Installation of Secondary Site happens with in Configuration Manager Console.

 

Please share feedback in comment box.

 

SCCM Prerequisites Checker to install Configuration Manager Site and Site systems

by

 

There is small utility available in the installation media named prereqchk.exe which help us to find out the readiness details on the site server or remote site server. Make sure to use this utility from the same version of configuration manager which would be used to install site. It identifies and fix the issues which may cause site installation to fail.

 

It is not mandatory to run this utility before site installation, as at the time of installation it runs by default, but it is best practice to run this before site installation to fix any issues avoid any roadblock.

 

Up on starting this utility it first checks for any site is already exist and if yes, then it checks for upgrade reediness. And if it does not find any site then it performs all required checks.

 

It records all the information in the log file name ConfigMgrPrereq.log under root drive.

 

There are some commands line option which can be used to perform the readiness according to the site or site systems role we are going to install.

 

Here we see some command line options with their purpose in brief:

 

/CAS

 

This verifies the local computer meets all requirement for installing Central Administration Site server.

 

/MP

 

This verifies the local computer meets all requirement installing site systems role of Management point.

 

/DP (FQDN of Computer)

 

This verifies the local computer meets all requirement installing site systems role of Distribution point.

 

/SQL (FQDN of Computer)

 

This verifies the local computer meets all requirement to install SQL to host site database.

 

/PRI

 

This verifies the local computer meets all requirement for installing Primary Site server.

 

/INSTALLSQLEXPRESS

 

This verifies the local computer meets all requirement for installing SQL Server Express.

 

Note: This application or utility can be found in Configuration Manager Installation Media\SMSSETUP\BIN\X64 or Configuration Manager Installation Path\BIN\X64  

 

Please share feedback on this topic in comment section.

List SCCM installation requirements

by

 

Find here the top information in list manner to have an idea on before going doing the installation of SCCM site in production environment

  1. Before starting to install site please make sure you have decided on the topology.
  2. Choosing installation method would be depend on the type of site you may want to opt.
  3. The first site you install always going to be either Stand alone Primary Site or Central Administration Site.
  4. Whenever installing first site always use baseline version.
  5. After installing baseline version, it can be updated to latest one from in-console update.
  6. As installation method we can either use Configuration Manager Setup Wizard or scripted command line tool.
  7. After installation of first site, one or more site can be added any time.
  8. To install secondary site use configuration manager console as installation method of CAS or Primary are not supported to install secondary site
  9. Make sure the basic task like updating computer with latest patches, install and configure SQL server for database, hardening of server OS, preparing network environment are completed before to go for site installation.
  10. Make sure to decide on site names and codes.
  11. Be aware of limits and restriction post installation of sites like, you can not change site code, site description and installation directory. Also, you can not move primary site from hierarchy.
  12. Setup Downloader can be used to pre-download the content before installation of site.
  13. Run prerequisites checker to identify and fix the issues before start of site installations.
  14. Optional port can be identified to use to secure communication between configuration manager sites and clients.

Please share feedback in comment section

Windows Server Roles and Features for SCCM Site Server and Site System

by

 

Before to start installation of any site server or site system’s role of Configuration Manager it is better to have below Windows server features and roles installed and enabled as it requires system restart.

Here are the information on basic feature and roles listed out in tabular format to understand better.

 

Require Windows Server features to be installed and enabled:

 

FeaturesSite Systems
.Net Framework
ASP.Net
HTTP Activation
Non-HTTP Activation
Windows Communication Foundation
Background Intelligent Transfer Services (BITS)Management Point
BranchCacheDistribution Point
Data DeduplicationDistribution Point
Remote Differnerials Compressions (RDC)Site Server or Distribution Point

 

Require Windows Server roles to be installed and enabled:

 

RolesSite Systems
Windows Deployment Services (WDS)PXE Point (For OS deployment)
Windows Server Update Services (WSUS)Software Update Point (For Software Update deployment)
Web Server IIS
Common HTTP Features
Application Development
Management Tools
Security		Distribution Point
Management Point
State Migration Point
Fallback Status Point
Software Update point

Please share feedback in comment section

Everything about extending Active Directory Schema to publish SCCM Site

by

Extending active directory schema creates new container in Active Directory Database with several attributes which configuration manage can use to publish information which later SCCM client can use them for several purpose.

 

Points to be considered for extending Active Directory Schema,

 

  1. Benefit of extending Active Directory schema for publishing SCCM Sites
  2. Prerequisites for extending Active Directory schema
  3. Steps to extend Active Directory Schema
  4. Devices and clients which do not use the Active Directory Schema
  5. Active Directory classes and attributes for SCCM

Will see them in brief,

 

Benefit of extending Active Directory schema for publishing SCCM Sites :

 

  • Configuration manager clients can easily find out the information of SCCM sites using different attributed like site code, Software update server information or any other published information in Active Directory
  • It helps clients to locate content servers
  • Client could find the Management Point from Active Directory if schema extended
  • Port information for clients to be used which are stored in Active directory
  • Site public key is store in Active Directory if schema extended which help to communicate between two different primary sites

 Prerequisites for extending Active Directory schema :

 

  • Account which is going to be user for schema extension should be part of Schema Admins and Domain Admins

 

Steps to extend Active Directory Schema :

 

Below two steps to be followed to extend the schema,

 

Step 1 – Extend Schema

 

  • Using extadsch.exe tool
    • Log in with Schema Admin / Domain Admin and open CMD with high elevation
    • Copy extadsch.exe tool from Configuration Manger installation media. This would be available under SMSsetup\bin\x64. (this exe can be directly called from media as well)
    • Run the exe from copied location
    • Verify extadsh.log for details
  • Using LDIF file
    • Log in with Schema Admin / Domain Admin and open CMD with high elevation
    • Copy “configmgr_ad_schema.ldf” to local drive from SMSsetup\bin\x64 on Configuration Manager media
    • Edit file to replace instance of DC=x with “DC=test,DC=SCCMinfo,DC=com” ( considering here the FQDN is “test.sccminfo.com”
    • Then run below command to import the content of this ldf file to Active Directory
      • ldifde -i -f configmgr_ad_schema.ldf -v -j “%temp%”
    • Verify the log to check if schema is successfully extended

Step 2 – Create System Management Container in Active Directory

 

  • Under system account create container name “System Management” with account having permission to create object.
  • Under Properties of container “System Management” go to Security and give full control to computer account of all Site Server
  • Select option to this Object and all child Object

Devices and clients which do not use the Active Directory Schema :

 

  • MacOS Client computers
  • Mobile devices which are managed by Exchange Server connector
  • Mobile devices enrolled by Configuration Manager
  • Mobile devices enrolled by Microsoft Intune
  • Mobile device legacy clients
  • Windows clients which are configuring for internet only client management
  • Windows client which are detected by configuration Manager to be on the internet

Active Directory classes and attributes for SCCM:

 

  • Classes
    • cn=MS-SMS-Management-Point
    • cn=MS-SMS-Roaming-Boundary-Range
    • cn=MS-SMS-Server-Locator-Point
    • cn=MS-SMS-Site
  • Attributes
    • cn=mS-SMS-Assignment-Site-Code
    • cn=mS-SMS-Capabilities
    • cn=MS-SMS-Default-MP
    • cn=mS-SMS-Device-Management-Point
    • cn=mS-SMS-Health-State
    • cn=MS-SMS-MP-Address
    • cn=MS-SMS-MP-Name
    • cn=MS-SMS-Ranged-IP-High
    • cn=MS-SMS-Ranged-IP-Low
    • cn=MS-SMS-Roaming-Boundaries on
    • cn=MS-SMS-Site-Boundaries
    • cn=MS-SMS-Site-Code
    • cn=mS-SMS-Source-Forest
    • cn=mS-SMS-Version

Key information to note :

  • Active Directory schema extension is one-time activity and once done cannot be reversed.
  • It is not required to extend Active Directory schema but if extended Config Manager clients can be benefited from it.

Please share feedback in comment box

Securing SCCM IIS Configuration and SCCM Management Point Configuration

by

Key points on securing IIS

 

There are roles in SCCM which require IIS. And configuring IIS is one of the important ask for any SCCM implementor as configuring IIS component which are not at all require might put the SCCM infrastructure in risk for attacks.

 

Here are listing out key point to be considered while configuring IIS for roles in SCCM,

 

  • Install and enable only require component of IIS.
  • Enable HTTPS for sits system roles for the communication.
  • Setup CTL (Certificate Trust List) in IIS.
  • Add only CA (Certificate Authority) to the CTL which are use by Configuration Manager for accepting the client’s communications.
  • Do not select to put IIS on computer running site server as site servers computer account is having local admin rights on all computers having site systems roles installed.
  • Do not put any web-based application on IIS server which is being used for Configuration Manager as poorly configured application open the path for attackers to gain access to configuration manager environment.
  • Use custom website if at all there is need to run other web application with set-wide setting.
  • In case of using custom website delete default virtual directory.
  • Configure custom header to disable MIME sniffing.

Key point on securing Management Point

 

Securing Management point is very important as this is site system which is used to have communication between clients and site servers.

 

  • Best practice to assign client to the management point for same site other than management point of another site.
  • In case of migration from earlier site to current branch, migrate the clients on the management point to new site as soon as possible.

Please leave comment for any suggestions or corrections.

Securing SCCM Site Server and SQL Server

by

 

Points on Securing Site server installation

 

  • It is not required to install any of the Configuration Manager sites directly on domain controller. Install site on member server as Configuration Manager maintain the local account in local SAM (Security Account Management) Database. This help to prevent direct attack on Domain Controller.
  • Do not install Secondary Site over the network, instead run the Secondary Site installation by using option User source file at the following location on secondary site computer (most Secure). This way of installation prevents the data or source installation files getting tamper over the network before start of installation.  
  • Make sure to have correct permission set on root drive where site server installation is going to take place. This way you will secure the normal users modifying or accessing contents of configuration manager. By default, site installation inherit permission from root drive.

Points on securing SQL installation

 

It is very much important to secure SQL database as all the contents of configuration manager get stores in SQL DB in backend. This help prevent attacker gaining access to configuration manager.

 

  1. Make sure not to use SQL DB for any other application as increasing access to the DB can put the Configuration Manager Database in risk for attacks.
  2. Always use windows authentication mode for login to the DB instead mixed mode as using mix mode would always have some risk for attack surface.
  3. For Secondary server make sure to have latest version of SQL express as when installing Secondary Site from Primary Site it installs SQL express with previously downloaded version.

General requirement for SQL server installation:

 

  • Computer account of Database Site server should be part of local administrator group.
  • If to install SQL server using Domain user account, make sure site server’s computer account is configured as SPN (Service Principal Name) which is published in active directory.

Please share comment to improve us in comment section

Supported Windows Features and Network in SCCM (Configuration Manager)

by

Here we are listing out supported windows and network features which SCCM can take advantage of,

 

BranchCache

 

Upon enabling BranchCache, it helps clients in remote locations to get the required contents from the nearest client on the same subnet on which the required latest cache of the contents is available.

 

Whichever first BranchCache enable clients request the content from a distribution point that gets set as BranchCache server and then next time other clients in same subnet receive the content from this BranchCache Server. And these clients also then cache the contents so other clients does not required to go to distribution point and download the contents. Contents gets distributed across multiple clients for later use.

 

Supports for Workgroup Computers

 

Configuration manager now support for clients which are part of Workgroup but still all Site systems should be part of Active Directory Domain.

 

Supports use of data deduplication

 

Data deduplication is new feature starting from Windows Server 2012 or later, it helps to store more data in less disk space. This find and removes duplicated data within file without comprising its integrity. This comes handy in case of redistribution of WIM file ( Operating System Image file) as only changes in the WIM files gets replicated rather than entire WIM file which are in big size.

 

Find more here on deduplication

 

Supports DirectAccess

 

Direct Access enables configuration manager on the internet to talk to their assigned site as they were on the local intranet.

 

For actions initiated from server like remote sessions, client push installation need IPv6 configured.

 

IPv6

 

Configuration manager now support for IPv6 with exception mentioned here

 

Please share your valuable comments on this topic in comment section.

 

Run Powershell Script

by

Microsoft has added the capability to run and manage PowerShell script from the configuration manager console. With this feature we can create and customize PowerShell script according to requirement of the routine or ongoing job operations to be done quickly and more steadily. This comes handy when any jobs to be done on large numbers of device with limited IT resources available.

 

With this,

 

  • You can create and edit the scripts in configuration manager console.
  • Manage these scripts with the help of roles and security scopes.
  • These scripts can be run on individual device or on collections.
  • Get instant result on output from client devices.
  • Monitor and see output results.