locked
List of Clients per Management Point/Distribution Point RRS feed

  • Question

  • Hello,

    I am looking for reports or SQL Queries listing the SCCM Clients

    - per Management Points

    - per Distribution Points

    Thanks,

    Dom


    Security / System Center Configuration Manager Current Branch / SQL


    • Edited by Felyjos Monday, January 22, 2018 7:55 PM
    Monday, January 22, 2018 7:47 PM

Answers

  • You can use the following to list the clients that are "actively" connected to the MP:

    select Name0 from System_DISC sd join BGB_ResStatus br on sd.ItemKey=br.ResourceID where br.AccessMP='MP-FQDN'

    MP-FQDN is the FQDN of your MP.

    If you want to get the list of all the clients that have the MP as their assigned MP, you need to collect that using Inventory. You have to collect the class SMS_Authority and the property CurrentManagementPoint, under the root\ccm namespace.

    As for the DP, it does not keep track of what clients connect to it.

    • Proposed as answer by Yuxiang Shi Tuesday, January 23, 2018 5:57 AM
    • Marked as answer by Felyjos Wednesday, January 24, 2018 2:14 AM
    Tuesday, January 23, 2018 2:58 AM

All replies

  • Would also like to know how to see a report like this.
    Monday, January 22, 2018 8:38 PM
  • You can use the following to list the clients that are "actively" connected to the MP:

    select Name0 from System_DISC sd join BGB_ResStatus br on sd.ItemKey=br.ResourceID where br.AccessMP='MP-FQDN'

    MP-FQDN is the FQDN of your MP.

    If you want to get the list of all the clients that have the MP as their assigned MP, you need to collect that using Inventory. You have to collect the class SMS_Authority and the property CurrentManagementPoint, under the root\ccm namespace.

    As for the DP, it does not keep track of what clients connect to it.

    • Proposed as answer by Yuxiang Shi Tuesday, January 23, 2018 5:57 AM
    • Marked as answer by Felyjos Wednesday, January 24, 2018 2:14 AM
    Tuesday, January 23, 2018 2:58 AM
  • For DP side; Have made a baseline to run this script and then capture new WMI Class during HW inventory:

    $searchtext = " successfully processed download completion."
    
    $file = "c:\Windows\CCM\Logs\ContentTransferManager.log"
    if (Test-Path $file){
     if (Get-Content $file | Select-String -Pattern $searchtext -Quiet){
      $StrResult = (Get-Content $file | Select-String -Pattern $searchtext |
       Select -Last 1).ToString()
      $LastCTMid = $StrResult.SubString(1,$StrResult.IndexOf('}')) |
       %{$_.Replace($_.SubString(0,$_.IndexOf('{')),'')}
    
      $searchtext2 = "CTM job $LastCTMid switched to location "
      $StrResult2 = (Get-Content $file | Select-String -Pattern $searchtext2 -SimpleMatch |
       Select -Last 1).ToString()
      IF($StrResult2){
       $LastDP = $StrResult2.Split('/')[2]}
      ELSE{
      $searchtext3 = "CTM job $LastCTMid (corresponding DTS job {"
      $StrResult3 = (Get-Content $file | Select-String -Pattern $searchtext3 -SimpleMatch |
       Select -Last 1).ToString()
      $LastDP = $StrResult3.Split('/')[2]
    }}}
        
    IF($LastDP){
     IF(Get-WMIObject CM_Last_CTM_DP -ErrorAction SilentlyContinue){
      Remove-WMIObject CM_Last_CTM_DP}
    
     $newClass = New-Object System.Management.ManagementClass("root\cimv2", [String]::Empty, $null); 
    
        $newClass["__CLASS"] = "CM_Last_CTM_DP"; 
    
        $newClass.Qualifiers.Add("Static", $true)
        $newClass.Properties.Add("LastDP", [System.Management.CimType]::String, $false)
        $newClass.Properties["LastDP"].Qualifiers.Add("key", $true)
        $newClass.Properties.Add("TimeStamp", [System.Management.CimType]::DateTime, $false)
        $newClass.Properties["TimeStamp"].Qualifiers.Add("key", $true)
            
        [VOID]$newClass.Put()
    
        [void](Set-WmiInstance -Path \\.\root\cimv2:CM_Last_CTM_DP -Arguments @{LastDP=$LastDP;
         TimeStamp=(Get-Date).ToString("yyyyMMddHHmmss.000000+***")})
    }
    IF(Get-WMIObject CM_Last_CTM_DP -ErrorAction SilentlyContinue){
     $Compliance = "Compliant"}
    Else {$Compliance = "Non-Compliant"}
     $Compliance

    Seems to be capturing LastDP accurately.  PeerCaching not used here so don't know if that will make a diff.

    This SQL to summarise/validate:

    SELECT vrs.Name0,vrs.AD_Site_Name0,dp.LastDP00,dp.TimeStamp00

    from v_r_system vrs

    join dbo.CM_LAST_CTM_DP_DATA DP on vrs.ResourceID  = dp.MachineID

    orderby dp.timestamp00 desc


    • Edited by NikNicholas Wednesday, July 4, 2018 7:07 AM
    Wednesday, July 4, 2018 7:07 AM
  • Just a few Tips. 

    - It is not supported to query the Tables use the SQL views. 

    - Watch out for Timestamp as there is already one which is default to CM, As such, you might want to rename your timestamp to DPTimeStamp.

    - v_R_system, depending on What version of CM you are using will included Deleted computers as such, user v_R_System_Valid, to ensure that you don't list deleted computers. 


    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

    Wednesday, July 4, 2018 1:38 PM