Inventory % for App-V and ARP

A SQL query that needs to run against the Configuration Manager database and present all software that is installed for a specific collection (App-V or traditionally registered software in Programs and Features)

Will present;

Software Name
Software Version
App-V or ProductCode
# installations
% av devices that have the software installed (in comparision to collection members)
# Collection Members

 

DECLARE @collection VARCHAR(50),
@DisplayName VARCHAR(50);

SET @collection = 'SS100517';
set @DisplayName = 'Adobe Audition%'

select arp.DisplayName0 as 'Name', arp.Version0 as 'Version', arp.ProdID0 as 'GlobalorProd', COUNT(*) as Total, count(distinct arp.resourceid) * 100/
(
SELECT count(distinct ws.resourceid)
from v_ClientCollectionMembers ws
where ws.CollectionID = @collection
) as '%',
(
SELECT count(distinct ws.resourceid)
from v_ClientCollectionMembers ws
where ws.CollectionID = @collection
) as 'Collection Members'
from v_Add_Remove_Programs as arp
inner join v_ClientCollectionMembers as coll on arp.ResourceID = coll.ResourceID
where arp.DisplayName0 LIKE @DisplayName
and  CollectionID = @collection
GROUP BY arp.DisplayName0, arp.Version0, arp.ProdID0
UNION
select appv.Name00 as 'Name', appv.Version00 as 'Version', 'AppV' as 'GlobalorProd', COUNT(*) as Total,
count(distinct appv.machineid) * 100/
(
SELECT count(distinct ws.resourceid)
from v_ClientCollectionMembers ws
where ws.CollectionID = @collection
) as '%',
(
SELECT count(distinct ws.resourceid)
from v_ClientCollectionMembers ws
where ws.CollectionID = @collection
) as 'Collection Members'

from APPV_CLIENT_PACKAGE_DATA as appv
inner join v_ClientCollectionMembers as coll on appv.MachineID = coll.ResourceID
where appv.Name00 LIKE @DisplayName
and  CollectionID = @collection
GROUP BY appv.Name00, appv.Version00, appv.PackageId00

Device, primary user and model

A simple query that will present the following information based on what ConfigMgr has inventoried

Computer-name
Primary User(Domain\username)
Manufacturer
Model

SQL;

select umr.MachineResourceName as 'Device name', umr.UniqueUserName as 'Username',
CASE
when CAST(umsr.SourceID as varchar(20))  = '1' then 'Software Catalog'
when CAST(umsr.SourceID  as varchar(20)) =  '2' then 'Administrator'
when CAST(umsr.SourceID as varchar(20)) =  '3'  then 'User'
when CAST(umsr.SourceID as varchar(20)) =  '4'  then 'Usage agent'
when CAST(umsr.SourceID as varchar(20)) =  '5'  then 'Device Management'
when CAST(umsr.SourceID as varchar(20)) =  '6'  then 'OSD'
when CAST(umsr.SourceID as varchar(20)) =  '7'  then 'Fast Install'
else CAST(umsr.SourceID as varchar(20)) END as 'Source',
cs.Manufacturer0 AS 'Manufacturer',
CASE
when cs.Manufacturer0 = 'Lenovo' Then hwdata.Version0
else cs.Model0
END as Model
from v_UserMachineRelationship as umr
inner join v_UserMachineSourceRelation as umsr on umr.RelationshipResourceID = umsr.RelationshipResourceID
inner JOIN v_GS_COMPUTER_SYSTEM AS cs ON umr.MachineResourceID = cs.ResourceID
left join v_GS_COMPUTER_SYSTEM_PRODUCT as hwdata on umr.MachineResourceID = hwdata.resourceid

ConfigMgr: Match client address to IP-Range Boundaries

Despite the Microsoft recommendation, primarily due to additional workload that it causes, to not leverage IP-Ranges we have noticed a far greater significant accuracy of where clients retrieve content from based on our IP-ranges. So yes, we have our boundaries, with few exceptions, setup using IP-ranges.

We also have clients spread around the globe, new networks beeing spun-up, networks that aren’t supposed to be used for servers and clients and much more to actually be used for these type of things. The issue at hand is to understand where clients are actually connecting from, and what locations we know about.

To get some type of insight of where ConfigMgr clients are actually connecting from we started polling our database. In the end – this turned into two SQL-queries that would get all the IP-range boundaries, and a summary of how many clients we support on each network. As lazy as one can be – this ended up gathering enough information to present to other teams to present where clients are connecting from, how many there are and that we don’t previously didn’t know about this location.

To list how many clients you have per a /24-subnet. This may of course not necessarily be the exact size of a subnet, but it allows for an easy count-up of clients.

select SUBSTRING(ip.IPAddress0, 1, 
LEN(ip.IPAddress0) - CHARINDEX('.',REVERSE(ip.IPAddress0))) + ".1" As IP,
 COUNT(*) as Devices
 from v_Network_DATA_Serialized as ip 
where ip.IPAddress0 IS NOT NULL and ip.IPSubnet0 != "64"
and ip.DNSDomain0 like "%yourdomain.com"
and ip.TimeStamp > DATEADD(day, -10, GETDATE())
GROUP BY  SUBSTRING(ip.IPAddress0, 1, LEN(ip.IPAddress0) - CHARINDEX('.',REVERSE(ip.IPAddress0)))
ORDER BY Devices DESC

A list of all boundaries where we split the start and end IP-address of a specific range

select bound.DisplayName,
SUBSTRING(bound.value,1,CHARINDEX('-',bound.value) -1) AS LEFTHALF,
SUBSTRING(bound.value,CHARINDEX('-',bound.value) +1 ,100) AS RIGHTHALF
from vSMS_Boundary as bound
where bound.BoundaryType = "3"
and bound.DisplayName != "some boundary to exclude"

Information about the clients within a specific range that we do not know about

select DNSHostName0,
DNSDomain0,
IPAddress0,
IPSubnet0,
DefaultIPGateway0,
DHCPServer0
from v_Network_DATA_Serialized as ip
where ip.IPAddress0 IS NOT NULL
and ip.IPSubnet0 != '64'
and ip.DNSDomain0 like '%yourdomain.com'
and ip.TimeStamp > DATEADD(day, -10, GETDATE())
and ip.IPaddress0 like 'XXX.YYY.ZZZ.%'

 

To join all of this information together some basic, crude, logic was built in powershell to match up networks that clients are in and that we know about. The function to perform the actual IP-range lookup is from stackoverflow-reply. Sample output first:2015-11-22 16_07_41-Clipboard

 

 

 

function IsIpAddressInRange {
param(
 [string] $ipAddress,
 [string] $fromAddress,
 [string] $toAddress
 )

 $ip = [system.net.ipaddress]::Parse($ipAddress).GetAddressBytes()
 [array]::Reverse($ip)
 $ip = [system.BitConverter]::ToUInt32($ip, 0)

 $from = [system.net.ipaddress]::Parse($fromAddress).GetAddressBytes()
 [array]::Reverse($from)
 $from = [system.BitConverter]::ToUInt32($from, 0)

 $to = [system.net.ipaddress]::Parse($toAddress).GetAddressBytes()
 [array]::Reverse($to)
 $to = [system.BitConverter]::ToUInt32($to, 0)

 $from -le $ip -and $ip -le $to
}


$ErrorActionPreference = "silentlycontinue"
$database = "ConfigMgrServer"
$datasource = "ConfigMgrDB"

$netquery = "select SUBSTRING(ip.IPAddress0, 1, LEN(ip.IPAddress0) - CHARINDEX('.',REVERSE(ip.IPAddress0))) + '.1' As IP, COUNT(*) as Devices from v_Network_DATA_Serialized as ip where ip.IPAddress0 IS NOT NULL and ip.IPSubnet0 != '64' and ip.DNSDomain0 like '%yourdomain.com' and ip.TimeStamp > DATEADD(day, -10, GETDATE()) GROUP BY SUBSTRING(ip.IPAddress0, 1, LEN(ip.IPAddress0) - CHARINDEX('.',REVERSE(ip.IPAddress0))) ORDER BY Devices DESC"

$networks= Invoke-Sqlcmd -Query $netquery -server $datasource -Database $database

$query = "select bound.DisplayName, SUBSTRING(bound.value,1,CHARINDEX('-',bound.value) -1) AS LEFTHALF,SUBSTRING(bound.value,CHARINDEX('-',bound.value) +1 ,100) AS RIGHTHALF from vSMS_Boundary as bound where bound.BoundaryType = '3' and bound.DisplayName != 'exclusion boundary'"

$iprange = Invoke-Sqlcmd -Query $query -server $datasource -Database $database

foreach ($net in $networks) {
 if (!($net.ip -eq '192.168.1.1')) {
 $i = 0
 $J = $iprange.count
 $boundaryfound = $false
 do {
 if (IsIpAddressInRange $net.ip $iprange[$i].LEFTHALF $iprange[$i].RIGHTHALF)
 {

 $boundaryfound = $true
 }
 $i++
 } until ($i -gt $j)
 if ($boundaryfound -eq $false) 
 {
 write-host "Network: $($net.ip) - Devices: $($net.Devices)"

 #$($($net.ip) -replace ".$")
 $devquery = "select DNSHostName0,DNSDomain0,IPAddress0,IPSubnet0,DefaultIPGateway0,DHCPServer0 from v_Network_DATA_Serialized as ip
 where ip.IPAddress0 IS NOT NULL
 and ip.IPSubnet0 != '64'
 and ip.DNSDomain0 like '%yourdomain.com'
 and ip.TimeStamp > DATEADD(day, -10, GETDATE())
 and ip.IPaddress0 like '$($($net.ip) -replace ".$")%'"
 $devices= Invoke-Sqlcmd -Query $devquery -server $datasource -Database $database
 $devices
 

 }
 }
}