:::: MENU ::::

Report to list of all users laptops

SELECT distinct dbo.v_R_System.Name0 AS [Computer Name], dbo.v_R_System.User_Name0 AS [User Name], dbo.v_R_System.User_Domain0 AS [Domain Name],
dbo.v_GS_SYSTEM_ENCLOSURE.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number], dbo.v_GS_SYSTEM.SystemRole0 AS [System OS Type],
dbo.v_GS_SYSTEM.SystemType0 AS [System Type]
dbo.v_R_System ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
dbo.v_GS_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID INNER JOIN
WHERE (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ‘8’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ‘9’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’10’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’11’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’12’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’14’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’18’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’21’)

SCCM Collections WQL Query—–Include or Exclude other collections

select distinct c.name as [Collection Name],
cdepend.SourceCollectionID as ‘Collection Dependency’,
cc.Name as ‘Collection Dependency Name’,
Case When
cdepend.relationshiptype = 1 then ‘Limited To ‘ + cc.name + ‘ (‘ + cdepend.SourceCollectionID + ‘)’
when cdepend.relationshiptype = 2 then ‘Include ‘ + cc.name + ‘ (‘ + cdepend.SourceCollectionID + ‘)’
when cdepend.relationshiptype = 3 then ‘Exclude ‘ + cc.name + ‘ (‘ + cdepend.SourceCollectionID + ‘)’
end as ‘Type of Relationship’
from v_Collection c
join vSMS_CollectionDependencies cdepend on cdepend.DependentCollectionID=c.CollectionID
join v_Collection cc on cc.CollectionID=cdepend.SourceCollectionID
where c.CollectionID = ‘XXX0077A’

All application list


select * from fn_ListLatestApplicationCIs(1033)

select DateCreated, DateLastModified, DisplayName, Manufacturer, SoftwareVersion, CreatedBy, LastModifiedBy 

from fn_ListLatestApplicationCIs(1033)
GROUP BY DateCreated,DateLastModified,DisplayName,Manufacturer,SoftwareVersion,CreatedBy, LastModifiedBy


For deployment types ::: fn_ListDeploymentTypeCIs(1033)

All “Microsoft Office%” Versions

c.Name0 AS [Machine Name], a.DisplayName0 AS [ARP Entry], a.Version0 AS Version, c.Operating_System_Name_and0, dbo.v_GS_OPERATING_SYSTEM.Caption0,
c.User_Domain0, c.User_Name0, c.AD_Site_Name0
dbo.v_R_System AS c ON a.ResourceID = c.ResourceID INNER JOIN
WHERE (a.DisplayName0 LIKE ‘Microsoft Office%’) AND (NOT (a.DisplayName0 LIKE ‘%update%’)) AND (NOT (a.DisplayName0 LIKE ‘%Microsoft Office XP Web Components’))
AND (NOT (a.DisplayName0 LIKE ‘%server%’))

All OS with Versions



dbo.v_R_System.Netbios_Name0 as [Machine Name],dbo.v_R_System.User_Name0 as [User Name],dbo.v_R_System.AD_Site_Name0 as [AD Site],
dbo.v_R_System.User_Domain0 as [Domain]
, dbo.v_GS_OPERATING_SYSTEM.Caption0 as [OS Name],
dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 as [SP Name], dbo.v_R_System.Operating_System_Name_and0 as [OS NT Version],dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 as [Build Number]
FROM dbo.v_R_System INNER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID

Use of Detection Method In App Model

Detection methods allow the administrator to check software installs to ensure that the application is not already installed. It can also prevent an install of an application if it conflicts with another application that is already installed.

Usually in our batch file we put the query method to detect the new version or the old version. Here in the App Model we have Detection Method which we can use while creating the package in App-model.



I want to install Microsoft .Net 4.5 on my machine and it should upgrade if the machine is having any old version of .Net

  • In General tab of the Deployment Type Page Select Script Installer.

Note: For Files like .exe,.vbs,.cmd we have to choose Script Installer.




  • In Program Page browse the file with the switches.



  • In Detection Page Click on Add Clause Button on the right side of the Panel.





  • For detecting the new version of the application, here we can choose Registry in Setting Type.



  • Expand the Registry and choose the Microsoft .Net 4.5 Product Code and Click on OK.






  • Select the Data Type as String and click OK

Detection Rule

  • Set the below User Experience field and click OK.Detection Rule 2
  • This way we can detect the new version of the application. It will skip to run the package if it is already present in the machine otherwise it will upgrade over the older one.

We have another two methods for detecting the installed application.


File System

  • The first method is file system. This method detects whether a file or folder is present on the system. If the file system object is not present, the application is marked as not installed.
  • You can use the “Browse” button to find the file on your computer. This will pull in all relevant information, such as the file version or modified date.


Detection Rule 3.jpg

  • One important thing to watch is the “This file or folder is associated with a 32-bit application on 64-bit systems” check box. By default, if you are on a 64-bit system, SCCM will only verify against “C:\Program Files” or “C:\Windows\system32” for files or folders and not “Program Files (x86)” or “SysWOW64”. If the application installs to “C:\ProgramFiles (x86)” or puts something in “C:\Windows\SysWOW64” and you queue off of that file, SCCM will not find it. When the user attempts to run the application, it will install again, possibly corrupting it or making it unusable.

Windows Installer

  • This method is automatically filled in when using an MSI install type. This method detects whether the MSI product code exists on the system. This method should only be used when dealing with an MSI. If you do not use the MSI install type, you can use the “Browse” button and find the MSI installer to automatically pull the product code.

Detection Rule 4


  • For products that update, but keep the same product code, you can use the “This MSI product code must exist on the target system and the following condition must be met to indicate the presence of this application” options to specify the version of the MSI.

SCCM 2012 – Glossary

Term Meaning
Collection A set of resources in the Configuration Manager. 
Deployment  An application state associated with the software deployment. 
Distribution point A site system role that contains source files for clients to download including application content, software packages, software updates, operating system images and so on.. 
Distribution point group  A set of distribution points that can be managed as a single unit. It provides a logical grouping of distribution points and collections for content distribution. 
Application  An object that contains the content files and instructions for distributing Deployment types, software updates, operating system images, and drivers to clients of the Configuration Manager. 
Global Condition Global condition is used to specify the conditions that must be met before a deployment type can be installed on a client device.
Deployment Type This program deploys software to a computer system. 
Detection method A detection method in Configuration Manager contains rules that are used to check whether an application is already installed on a device. This detection occurs before the application is installed, immediately after the application is installed, and at regular intervals afterwards. This can prevent Configuration Manager from needlessly reinstalling the application and can also detect if the application is already uninstalled by the user.

SCCM Different types of Views


SCCM Different types of Views :—(In one line )

V_*                 — > By default view(EX:-V_add_remove)

V_R_  *          — > It will fill the values with Discovery data

V_RA_  *      — > It will, fill the values Once Discovered and Assigned

V_GS_*         —-> HINV/SINV/Software Granular data- Present/Current inv data

V_HS_*         —-> Historical data-After 90 days Task mainenance data HINV/SINV/Software Historical data

V_AI_*         —-> Asset Intelligence related

V_LU_*         —–> Part of AI(Localized Unit)

V_CI_*          ——> Configuration Item 5 types

V_CH_*         —–> Client Health validation related

V_CM_RES_COLL_<SMS00012>  ——> Member Class name / Machine details of that collection


Download Reference -SCCM 2007 SQL Views SCCM 2007 List of views from Technet

Download  ConfigMgr2012R2_SQLViews SCCM 2012 List of views from Technet

Download  Configmgr Tech Preview Default Reports   SCCM 2012 List of Reports Technet

Download  ConfigMgr 2012 R2 Status Messages  SCCM 2012 List of  Status Messages from Technet


SCCM 2012 Client Self healing



Monitoring Home page in the console will help with 2 things ,Those are “monitor the health and activity of client computers”

However Overall status will be depends on below 2 things

Client Activity : configure thresholds to determine whether a client is active (like DDR,requested policy,Hinv,etc)
Client Check: Self Healing with Task Scheduler

sccm 2012 client self healing in workstation how it works:::

Self healing means auto remediation :

Once SCCM client is installed , It will create a scheduled Task in client side. To check open run and type “taskschd.msc”

Then go to Microsoft : check the Configuration Manager. That is the scheduled task

If you open the  Configuration Manager Task  in Action TAB of Task Scheduler , its calling the “C:\WINDOWS\CCM\ccmeval.exe

Once its executed it will send the report  to SCCM server(MP) “C:\Windows\CCM\start CcmEval.xml”.. To check the log open “C:\Windows\CCM\Logs\CcmEval.log”

If the workstation if it has any issues it will be auto healed . CcmEval.xml it will show up what is the issue and whether it got fixed or not. and CcmEval.log will show up what kind of regular checks it will do


EX:– If  CCMexec service is stopped or BITS is disabled ,etc,It will check at the Task Scheduler time and  will be auto re-mediated.You can do a test with stopping the WMI service and execute the C:\WINDOWS\CCM\ccmeval.exe. After some time it will be starts the WMI service

CCMevalreport CCMEval

If it is unable to resolve it will be showing in server side under Monitor-> Client Status :


CLient status of a specific machine



To Exclude/Disable automatic remediation

Open Regedit.

Go to “HKEY_LOCAL_MACHINE\Software\Microsoft\CCM\CcmEval\NotifyOnly”

Make it as “True” – computers will not automatically remediate
Make it as “False”– computers will automatically remediate problems