locked
SCCM Report - Client count with Boundary and Distribution point RRS feed

  • Question

  • Does anybody have a report or could help with a report for the following.

    Count of Clients, what boundary they use (AD Site, Range, or Subnet) what DP they go to?

    SCCM 2012 R2 SP1

    Friday, May 20, 2016 6:10 PM

Answers

  • ok, I looked and I have chopped up my code but I did use my query to search the internet to see if there was something you can start with:

    https://www.reddit.com/r/SCCM/comments/4dn8vb/sccm_2012_sql_query_boundaries_and_respective_dp/

    Here is a breakout from a report I had created to give number of machines per boundary group.

    This is based on Heartbeat data, if I recall correctly.  I did have one that had the break down of boundary group per DP, with number of clients, but that SQL doesn't run os I must have stopped working on it.

    First report: number of machines imaged in the last 30 days per ip boundary.

    SELECT GroupName.Name, count(ip_subnets0) as 'Image Count'
      FROM v_RA_System_IPSubnets
      left join vSMS_Boundary AS bondary on v_RA_System_IPSubnets.ip_subnets0 = bondary.Value
      left join vSMS_BoundaryGroupMembers AS GroupMembers on bondary.BoundaryID=GroupMembers.BoundaryID
      left join vSMS_BoundaryGroup AS GroupName on GroupMembers.GroupID=GroupName.GroupID
      JOIN v_GS_OPERATING_SYSTEM as OS ON v_RA_System_IPSubnets.ResourceID = OS.ResourceID
      Where Caption0 like '%Enterprise' and  DATEDIFF(day,InstallDate0,getdate()) between 0 and 30
      and Name not like ''
     Group by GroupName.Name
         order by 'Image count' DESC

    number of machine per boundary group.

    SELECT GroupName.Name, count(ip_subnets0) as 'Machine Count'
      FROM v_RA_System_IPSubnets
      left join vSMS_Boundary AS bondary on v_RA_System_IPSubnets.ip_subnets0 = bondary.Value
      left join vSMS_BoundaryGroupMembers AS GroupMembers on bondary.BoundaryID=GroupMembers.BoundaryID
      left join vSMS_BoundaryGroup AS GroupName on GroupMembers.GroupID=GroupName.GroupID
         Group by GroupName.Name
                    /* order by 'Machine Count'*/
                    order by GroupName.Name

    ---sorry I couldn't find the specific query anymore.


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    Friday, May 20, 2016 6:54 PM

All replies

  • Yes, I created such a sql query because of a powershell script I was working on...Not sure I even still have it..I will look for it..There are several reports in SCCM currently that will help you glean the data, it is all about putting them together.  I will say my report was for the Secondary, we don't have any DPs but the data should still be the same.


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    Friday, May 20, 2016 6:44 PM
  • I will also say that there was something similar that I also found on a blog somewhere.

    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    Friday, May 20, 2016 6:46 PM
  • I have been looking but can't find it... Basically I am trying to prepare a slide deck on purchasing a content distribution software and minimize or DP's along with network traffic and such and I need this information to help quantify.
    Friday, May 20, 2016 6:48 PM
  • ok, I looked and I have chopped up my code but I did use my query to search the internet to see if there was something you can start with:

    https://www.reddit.com/r/SCCM/comments/4dn8vb/sccm_2012_sql_query_boundaries_and_respective_dp/

    Here is a breakout from a report I had created to give number of machines per boundary group.

    This is based on Heartbeat data, if I recall correctly.  I did have one that had the break down of boundary group per DP, with number of clients, but that SQL doesn't run os I must have stopped working on it.

    First report: number of machines imaged in the last 30 days per ip boundary.

    SELECT GroupName.Name, count(ip_subnets0) as 'Image Count'
      FROM v_RA_System_IPSubnets
      left join vSMS_Boundary AS bondary on v_RA_System_IPSubnets.ip_subnets0 = bondary.Value
      left join vSMS_BoundaryGroupMembers AS GroupMembers on bondary.BoundaryID=GroupMembers.BoundaryID
      left join vSMS_BoundaryGroup AS GroupName on GroupMembers.GroupID=GroupName.GroupID
      JOIN v_GS_OPERATING_SYSTEM as OS ON v_RA_System_IPSubnets.ResourceID = OS.ResourceID
      Where Caption0 like '%Enterprise' and  DATEDIFF(day,InstallDate0,getdate()) between 0 and 30
      and Name not like ''
     Group by GroupName.Name
         order by 'Image count' DESC

    number of machine per boundary group.

    SELECT GroupName.Name, count(ip_subnets0) as 'Machine Count'
      FROM v_RA_System_IPSubnets
      left join vSMS_Boundary AS bondary on v_RA_System_IPSubnets.ip_subnets0 = bondary.Value
      left join vSMS_BoundaryGroupMembers AS GroupMembers on bondary.BoundaryID=GroupMembers.BoundaryID
      left join vSMS_BoundaryGroup AS GroupName on GroupMembers.GroupID=GroupName.GroupID
         Group by GroupName.Name
                    /* order by 'Machine Count'*/
                    order by GroupName.Name

    ---sorry I couldn't find the specific query anymore.


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    Friday, May 20, 2016 6:54 PM
  • Have made a baseline to run this script and then capture new WMI Class during HW inventory:

    $searchtext = " successfully processed download completion." $Logpath = Get-ItemProperty -path HKLM:\Software\Microsoft\CCM\Logging\@Global
     $Log = $logpath.LogDirectory
    $file = "$Log\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("EvalTime", [System.Management.CimType]::DateTime, $false)
        $newClass.Properties["EvalTime"].Qualifiers.Add("key", $true)
           
        [VOID]$newClass.Put()

        [void](Set-WmiInstance -Path \\.\root\cimv2:CM_Last_CTM_DP -Arguments @{LastDP=$LastDP;
         EvalTime=(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.  Works for PeerCaching also.

    This SQL to get ad/sub, mp, sup and dp:

    select distinct vrs.name0, vrs.AD_Site_Name0 as ADorSUB, cs.LastMPServerName as MP,
    uss
    .LastScanPackageLocation as SUP, dp.LastDP0 as DP
    from v_R_System vrs
    left join v_UpdateScanStatus uss on vrs.ResourceID = uss.ResourceID
    left join v_CH_ClientSummary cs on vrs.ResourceID = cs.ResourceID
    left join v_GS_CM_LAST_CTM_DP DP on vrs.ResourceID  = dp.ResourceID
    Where AD_Site_Name0 IS NOT NULL

    union all

    select distinct vrs.name0, sub.IP_Subnets0 as ADorSUB, cs.LastMPServerName as MP,
    uss
    .LastScanPackageLocation as SUP, dp.LastDP0 as DP
    from v_R_System vrs
    left join v_UpdateScanStatus uss on vrs.ResourceID = uss.ResourceID
    left join v_CH_ClientSummary cs on vrs.ResourceID = cs.ResourceID
    left join v_GS_CM_LAST_CTM_DP DP on vrs.ResourceID  = dp.ResourceID
    left join v_RA_System_IPSubnets sub on vrs.ResourceID = sub.ResourceID
    Where AD_Site_Name0 IS NULL and sub.IP_Subnets0 like '%.%.%.%'

    order by ADorSub




    • Edited by NikNicholas Tuesday, July 17, 2018 11:32 PM
    Wednesday, July 4, 2018 3:29 AM