T-SQL Query SCCM Users and their Primary Device (Including Multiple Devices)

Summary
In your environment you may have Users that have multiple machines E.G. some Users with Both a Laptop and a Desktop.

This SQL Query will display Users and their Primary computers (1 Row per User, which is achieved using the SQL Function Stuff).

The 3 Defined Columns are:
UserName CollectionName ComputerNames
However CollectionName is only useful if a Collection is being defined in your Where Clause.
Check Lines 17 & 28.

/*     .NOTES

===========================================================================

        Created on:         20^th October 2017

        Created by:         Matthew Swanston

        Contact             http://thebeardysccmguy.blogspot.com

        Filename:           DetectionMethods

===========================================================================

       .DESCRIPTION

              Provides a User and Machine information including Multiple Primary Devices

*/

if object_id('tempdb..#consoleuser') is not null

       drop table #consoleuser;

WITH CTE_ConsoleUser as (

select TopConsoleUser00

,Name0

from SYSTEM_CONSOLE_USAGE_DATA

join v_R_System on v_R_System.ResourceID = SYSTEM_CONSOLE_USAGE_DATA.MachineID)

 SELECT T1.TopConsoleUser00,     

 STUFF((SELECT Distinct ', ' + T2.Name0

 FROM   CTE_ConsoleUser as T2             

 WHERE  T1.TopConsoleUser00 = T2.TopConsoleUser00           

 FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'') AS ComputerNames

 INTO #consoleuser

 FROM CTE_ConsoleUser as T1

 where TopConsoleUser00 like 'PROD\%'

 --Comment out Line 17 if you only have 1 Domain, or you don't need to filter the Domains e.g. DEV, Prod etc.

 GROUP BY TopConsoleUser00

select SMSID AS UserName

,CollectionName

,ComputerNames

from CollectionMembers

JOIN Collections on Collections.SiteID = CollectionMembers.SiteID

JOIN v_R_User on v_R_User.Unique_User_Name0 = CollectionMembers.SMSID

LEFT JOIN #consoleuser on #consoleuser.TopConsoleUser00 = SMSID

--WHERE (CollectionMembers.SiteID = 'LAB001D7' and Distinguished_Name0 like '%OU=Europe Middle East Africa%' and computernames like '%,%')

-- Comment out Line 28 If you don't wish to limit on a Collection or Distinguished_Name0 or Multiple Computers Only (Filtered using a Comma)

ORDER BY CollectionName

Create SCCM Collections For All Active Directory OUs

https://github.com/TheBeardySCCMGuy/CreateSCCMCollectionsByOU

Imagine you have 100s of Offices across the globe, and you need to create a SCCM collection for every office you have.

I have created a PowerShell Script to Read a CSV file and then create SCCM Collections, with a WQL Query based on the OU String.

Prepare your CSV file in this format and Save to C:\temp\Collections.csv

Its important to follow this Structure as this is the expected format by the PowerShell Script.

If your enviroment operates in one country with Multiple Offices and Region isn't required please fill it out anyway.

The Region field is used for the Limiting Collection, so the Collections will created with the Region.

If this isn't required un-comment Line 21 #$LimitingCollection = "All Systems"

Change line 36 from -LimitingCollectionName $item1.region 

-LimitingCollectionName $LimitingCollection

OU,Office,Region

E.g.

Production.MyDomain.COM/EUROPE MIDDLE EAST AFRICA/UK IRELAND NORWAY/UK IRELAND/LONDON/COMPUTERS,LONDON,EMEA

You can manually populate your CSV, or use T-SQL Query on SCCM DB,

Or PowerShell Get-ADOrganizationalUnit

Or any other method you see fit.

select distinct System_OU_Name0

,right(System_OU_Name0,CHARINDEX('/',REVERSE(System_OU_Name0))-1) as Office

,case when  System_OU_Name0 like ('%Europe Middle East Africa%') then 'EMEA'

         when System_OU_Name0 like ('%americas%') then 'Americas'

         when System_OU_Name0 like ('%Asia Pacific%') then 'APAC'

         else 'Unknown Region'

         End Region

from System_System_OU_Name_ARR

where System_OU_Name0 like '%computers%'

and System_OU_Name0 like 'PRODUCTION.MYDOMAIN.COM%'

order by Office

My work environment has the following AD OU Structure

With the OU holding, all the computers called Computers, and the parent OU called “Office”

Production.MyDomain.COM/REGION/Division/Business Unit/Office/Computers

E.G.

Production.MyDomain.COM/EUROPE MIDDLE EAST AFRICA/UK IRELAND NORWAY/UK IRELAND/LONDON/COMPUTERS

So this SQL line below pulls out the office name by replacing '/computers' with ‘’ Nothing

And then using the Right command and CharIndex.

,right(replace(System_OU_Name0,'/computers',''),CHARINDEX('/',REVERSE(replace(System_OU_Name0,'/computers','')))-1) as Office

The Script places the Collections into the Root of Device Collections.

You can move in Bulk manually or using this line (If required)

Move-CMObject -FolderPath $FolderPath -InputObject (Get-CMDeviceCollection -Name $Collection.Name)

https://github.com/TheBeardySCCMGuy/CreateSCCMCollectionsByOU

SCCM Application Detection Method Advanced Query (Search SDMPackageDigest.XML)

SCCM Application Detection Method Advanced Query 

(Search SDMPackageDigest.XML)

I recently needed to re-sign all PowerShell Scripts in our environment because our Code Signing Certificate was about to expire.

Applications and Packages Content re-signing was straight forward by using PowerShell to Look for PS1 files, re-sign, and re-distribute to SCCM DPs.

(Using Start-CMContentDistribution)

However I needed to look at the Detection Methods being used and Global Conditions.

The creation of this script saved me hours, and manually without a doubt I would of missed some Applications.

Change this line to like '%.VBS%'  or your criteria.

and CONVERT(varchar(max), DeploymentTypes.SDMPackageDigest) like '%.ps1%'

Un-comment the line below to search for MSI Installs or Script, App-v Etc.

--and Technology = 'script'

/*     .NOTES

===========================================================================

        Created on:         28th August 2017

        Created by:         Matthew Swanston

        Contact             http://thebeardysccmguy.blogspot.com

        Filename:           DetectionMethods

===========================================================================

       .DESCRIPTION

              Provides a search ability for SCCM Application Detection methods

              .e.g Searching for a Powershell DetectionMethod or VBS etc.

              Or Searching for Deployment Technology type E.g. Script or MSI

              DisplayName   DateLastModified       CreatedBy     Description   Technology    SDMPackageVersion       SDMPackageDigest

*/

SELECT        Apps.DisplayName

                      ,Apps.DateLastModified

                      ,Apps.CreatedBy

                      ,Apps.Description

                      ,Technology

                      ,Apps.SDMPackageVersion

                     ,DeploymentTypes.SDMPackageDigest

FROM         dbo.fn_ListDeploymentTypeCIs(1033) AS DeploymentTypes

INNER JOIN dbo.fn_ListLatestApplicationCIs(1033) AS Apps ON DeploymentTypes.AppModelName = Apps.ModelName

WHERE     (DeploymentTypes.IsLatest = 1)

and CONVERT(varchar(max), DeploymentTypes.SDMPackageDigest) like '%.ps1%'

--and Technology = 'script'

order by Apps.DisplayName

Deleting a computer from SCCM With Powershell Script

Summary

Do you have IT staff who need delete resource access to SCCM for re-imaging purposes (e.g. Task Sequences only available to Unknown computers), but don’t wish to grant any access to the SCCM Console Itself.

Prerequisites

• Latest Console installed (That matches your current SCCM Infrastructure)
• AdminConsole.msi
• ConfigMgr2012PowerShellCmdlets.MSI Installed
• Powershell 3.0 or newer
• Must have a user profile for elevated account e.g. under       c:\users\SCCM.Admin.Matt.Swanston\
• Permissions must be setup by our SCCM Administrator
• SCCM RBAC access requirements:
• Read, Modify Resource, Delete Resource
• Import Reg Setting for Admin account
• https://github.com/TheBeardySCCMGuy/Deleting-a-computer-from-SCCM-With-Powershell

RBAC control requirements

Installing Prerequisites (SCCM Console and PSShellCmdlets)

Download Prerequisites installers 

Copy and paste command lines from RemoveComputerFromSCCM\Installers\Install.txt      Into CMD.exe (with Admin Account)

AdminConsole.MSI TARGETDIR="%programfiles(x86)%\Microsoft Configuration Console" ENABLESQM=0 DEFAULTSITESERVERNAME=FQDN.DOMAIN.COM /q /l*v c:\TEMP\SCCM1610Console.log

ConfigMgr2012PowerShellCmdlets.MSI /qn /l*v c:\TEMP\ConfigMgr2012PowerShellCmdlets.log

Check Install logs for 

c:\temp\SCCM1610Console.log

c:\temp\ConfigMgr2012PowerShellCmdlets.log

Look for exit code 0 at the end of the log file.

Edit the Reg File 

Import ImportSCCM_Settings.REG Settings (With Admin rights)

Run Deletion PowerShell Script

Troubleshooting steps

T-SQL Query Collection Path SCCM Current Branch

T-SQL Query Collection Path SCCM Current Branch

Struggling to find a T-SQL query display the Location Path of a SCCM Collection?

There are PowerShell scripts to perform the same results, however I wrote the below query in SQL so that it can be published to SSRS.

/*     .NOTES

      ===========================================================================

        Created on:        4th January 2017

        Created by:        Matthew Swanston

        Contact            http://thebeardysccmguy.blogspot.com

        Filename:          CollectionInformation

      ===========================================================================

       .DESCRIPTION

             Provides basic Collection Information including Collection Location Path.

             CollectionName      LocationPath  CollectionID       LimitToCollectionName      AssignedCount

*/

with cte_CollectionInfo as (select CollectionName, SiteID, LimitToCollectionName

,CONCAT ('\' , F4.Name , '\' , F3.Name , '\' , f2.name , '\' , f.name) as LPath

,C.CollectionID

,CMC.AssignedCount

from Collections C

join CollectionMemberCounts CMC on CMC.CollectionID = C.CollectionID

join FolderMembers FM on FM.InstanceKey = C.SiteID

join Folders F on F.ContainerNodeID = FM.ContainerNodeID

left join Folders F2 on F2.ContainerNodeID = F.ParentContainerNodeID

left join Folders F3 on F3.ContainerNodeID = F2.ParentContainerNodeID

left join Folders F4 on F4.ContainerNodeID = F3.ParentContainerNodeID

left join Folders F5 on F5.ContainerNodeID = F4.ParentContainerNodeID

where  f.ObjectType = 5000

--and CollectionName = 'Test 040117'

)

select CollectionName

,REPLACE(REPLACE(LPath, '\\\', '\') , '\\', '\') AS LocationPath

,siteid as CollectionID

,LimitToCollectionName

,AssignedCount

from cte_CollectionInfo

order by CollectionName