Q&A Archives - SCCMinfo

People always gets confused when they look at the options available on the properties of the any package type available in the SCCM 2012 for the distribution. Whenever they try to distribute content, up on right click they see below two options available which we will try our best to clear any doubt on the same.

After right click on Package we get,

Distribute content
Update Distribution points

Let us first see on Distribute Content:

Distribute content as name suggest is for distributing contents of the package to the distribution points which are not already available for SCCM client to use for deployment. Which means whenever you create any package and trying to put content of that package to the distribution point very first time, we should always use Distribute Content.

In this we must select the Distributions Point where package to be added.

When we select Distribute content, we get list of the distribution point to select as shown in below screen,

Now let us see Update Distribute Point:

Update Distribute point should be used to update the contents which are already available on Distribution points. Lets us say there is one package which source files have to be modified for some reason then as package is already available on the DP’s and we want updated source package contents to be updated on distribution points then use the Update Distribution Point option. Up on selecting update distribution point all the Distribution point would be updated wherever package is already distributed.

In this rather selecting distribute content, we must select update distribution point up on right click on package.

When we select Update Distribution point we get below screen, Click OK to continue to update all Distribution Points in which package is already added.

Here important to note that once you click OK on above dialog box, process of updating package would start on all Distribution points on which Package is already added.

So in simple language if we have to understand this, then whenever we create any package use distribute content for first time to add package to Distribution Point and later on for some reason when we modify the content of the source of the same package then we have to use update distribute point.

Please share feedback in the comment box.

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.

SCCMinfo

SCCM 2012 distribute content vs update distribution points

Here important to note that once you click OK on above dialog box, process of updating package would start on all Distribution points on which Package is already added.

SCCM SQL Query – Part1