SQL

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

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

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

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: