There is more than one way to get data from ConfigMgr. Some data you can get directly from the ConfigMgr Console. But if you want more detailed and customised information you usually need to go a bit deeper, for example by using the ConfigMgr PowerShell cmdlets, querying WMI with PowerShell or some other tool, or accessing the ConfigMgr database directly.
But the method you choose can make a big difference in how quickly you can return results. For example, I wanted to find out what collections a particular device is a member of. Unfortunately, this is not possible natively with the ConfigMgr Console, unless you have a custom extension. So I turned to WMI. I customised a little PowerShell script written by David O’Brien so it could run on my local machine and query the WMI on the ConfigMgr site server:
<#
This script gets the collection membership of a device using a remote WMI Query
Enter your site server name, and run on your local machine.
#>
# Get Start Time
$startDTM = (Get-Date)
$SiteServer = "mysiteserver"
write-host "Get Collection Membership for ConfigMgr Device"
$ComputerName = Read-host "Enter the computername to check"
$s = New-PSSession -ComputerName $SiteServer
Invoke-Command -Session $s -Argu $ComputerName -ScriptBlock `
{
param ($ComputerName)
Import-Module 'C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1'
$SiteCode = (Get-CMSite | Select SiteCode).SiteCode
$drive = $SiteCode + ':'
cd $drive
$ResID = (Get-CMDevice -Name $ComputerName).ResourceID
$ColRemote = @()
$Collections = (Get-WmiObject -Class sms_fullcollectionmembership -Namespace root\sms\site_$SiteCode -Filter "ResourceID = '$($ResID)'").CollectionID
foreach ($Collection in $Collections)
{
$Col = Get-CMDeviceCollection -CollectionId $Collection | select Name, CollectionID
$ColRemote += $Col
}
}
$ColLocal = Invoke-Command -Session $s -ScriptBlock { $ColRemote }
Remove-PSSession $s
$Count = $ColLocal.Count
$ColLocal = $ColLocal | Select Name,CollectionID | Sort Name
$ColLocal | Out-GridView -Title "Collection Membership for $ComputerName ($count Collections)"
# Get End Time
$endDTM = (Get-Date)
# Echo Time elapsed
"Elapsed Time: $(($endDTM-$startDTM).totalseconds) seconds"
But this is very slow to return results:
How about if I run the script on the site server itself? Well first I’ll need to change the script a bit:
<#
This script gets the collection membership of a device using a local WMI Query
Run on the site server itself
#>
# Get Start Time
$startDTM = (Get-Date)
write-host "Get Collection Membership for ConfigMgr Device"
$ComputerName = Read-host "Enter the computername to check"
$SiteCode = (Get-CMSite | Select SiteCode).SiteCode
$ResID = (Get-CMDevice -Name $ComputerName).ResourceID
$ColLocal = @()
$Collections = (Get-WmiObject -Class sms_fullcollectionmembership -Namespace root\sms\site_$SiteCode -Filter "ResourceID = '$($ResID)'").CollectionID
foreach ($Collection in $Collections)
{
$Col = Get-CMDeviceCollection -CollectionId $Collection | select Name, CollectionID
$ColLocal += $Col
}
$Count = $ColLocal.Count
$ColLocal = $ColLocal | Select Name,CollectionID | Sort Name
$ColLocal | Out-GridView -Title "Collection Membership for $ComputerName ($count Collections)"
# Get End Time
$endDTM = (Get-Date)
# Echo Time elapsed
"Elapsed Time: $(($endDTM-$startDTM).totalseconds) seconds"
That definitely quicker, but still a bit slow:
What about accessing the ConfigMgr database? Well if I run the following SQL query in the SSMS I get almost instant results:
Select col.CollectionName ,col.SiteID as 'Collection ID' from vCollectionMembers cmb inner join vCollections col on cmb.CollectionID = col.CollectionID where cmb.Name = 'PC001' ORDER BY CollectionName
But rather than log into the SQL server, I’d prefer to run a PowerShell script to get the data. So let’s use this script to query the ConfigMgr database, and get the same results as the previous scripts:
<# This script gets the collection membership of a device using a remote SQL Query Enter the SQL server name and instance, and the database name Choose Windows or SQL authentication (enter the SQL credentials in the script) #> # Get Start Time $startDTM = (Get-Date) write-host "Get Collection Membership for ConfigMgr Device" $ComputerName = read-host "Enter computername to check" # Database info $dataSource = “mysqlserver\INST_SCCM” $database = “CM_ABC” # Open a connection cls Write-host "Opening a connection to '$database' on '$dataSource'" # Using windows authentication, or.. #$connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;” # Using SQL authentication $connectionString = "Server=$dataSource;Database=$database;uid=ConfigMgrDB_Read;pwd=PaSSw0rd;Integrated Security=false" $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString $connection.Open() # Getting Software Updates Compliance Data Write-host "Running query..." $query = "Select col.CollectionName ` ,col.SiteID as 'Collection ID' ` from vCollectionMembers cmb ` inner join vCollections col on cmb.CollectionID = col.CollectionID ` where cmb.Name = '$ComputerName' ORDER BY CollectionName" $command = $connection.CreateCommand() $command.CommandText = $query $result = $command.ExecuteReader() $table = new-object “System.Data.DataTable” $table.Load($result) $Count = $table.Rows.Count $table | Out-GridView -Title "Collection Membership on $ComputerName ($count Collections)" # Close the connection $connection.Close() # Get End Time $endDTM = (Get-Date) # Echo Time elapsed "Elapsed Time: $(($endDTM-$startDTM).totalseconds) seconds"
Wow, that’s much quicker!
Given that my ConfigMgr site server and database server are both in the same remote location, that’s pretty impressive! So getting data from the SQL database directly is the quickest way to query ConfigMgr, which is good to know for scripting purposes at least.
