SCCM中SQL查询合并多IP为单列、消除服务器名重复的优化方案
SCCM查询脚本IP聚合优化方案
问题根因
原脚本的核心问题是将v_RA_System_IPAddresses视图直接纳入主查询关联、同时把IP地址字段加入了主分组维度。由于SCCM的IP地址视图本身是单IP一行的长表结构,天然会造成同一服务器对应多条记录、机器名重复的问题。
优化实现逻辑
- 调整IP表关联方式:不对IP表做直接关联,先按服务器唯一标识
ResourceID对IP字段做预聚合,把同一服务器的所有IP拼接为单值字符串后,再和其他硬件属性表做关联,从根源避免主查询出现行膨胀。 - 移除原脚本冗余逻辑:删除主查询里的
DISTINCT、以及IP字段相关的分组规则,主查询仅保留和服务器硬件属性的关联,这些属性本身和ResourceID是一一对应的单值字段,不需要额外去重和分组。 - 匹配目标输出格式:使用分号作为IP拼接分隔符,实现示例中
192.168.1.1;192.168.1.2;fe80::xxxx的展示效果。
可直接使用的优化后脚本
适配SQL Server 2017及以上版本(当前主流SCCM环境通用)
SELECT CPU.SystemName0 AS [ServerName], IP_Grouped.IPAddress AS [IPAddress], CS.Manufacturer0 AS [Manufacturer], CS.Model0 AS [Model], PCB.SerialNumber0 AS [SerialNumber], PM.TotalPhysicalMemory0 AS [Memory] FROM [dbo].[v_GS_PROCESSOR] CPU LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON CS.ResourceID = CPU.ResourceID LEFT OUTER JOIN dbo.v_GS_PC_BIOS PCB ON CS.ResourceID = PCB.ResourceID LEFT OUTER JOIN dbo.v_GS_X86_PC_MEMORY PM ON CS.ResourceID = PM.ResourceID -- IP预聚合子查询:按服务器维度拼接所有IP LEFT OUTER JOIN ( SELECT ResourceID, STRING_AGG(IP_Addresses0, ';') AS IPAddress FROM dbo.v_RA_System_IPAddresses -- 过滤无效空值IP,可根据需要补充过滤规则 WHERE IP_Addresses0 IS NOT NULL AND IP_Addresses0 <> '' GROUP BY ResourceID ) IP_Grouped ON CS.ResourceID = IP_Grouped.ResourceID ORDER BY [ServerName] ASC
低版本SQL Server兼容写法(2016及更早)
如果环境不支持STRING_AGG函数,只需要把上面脚本里的IP预聚合子查询替换为以下FOR XML PATH实现即可:
LEFT OUTER JOIN ( SELECT IPA_Inner.ResourceID, STUFF(( SELECT ';' + IPA_Inner2.IP_Addresses0 FROM dbo.v_RA_System_IPAddresses IPA_Inner2 WHERE IPA_Inner2.ResourceID = IPA_Inner.ResourceID AND IPA_Inner2.IP_Addresses0 IS NOT NULL AND IPA_Inner2.IP_Addresses0 <> '' FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS IPAddress FROM dbo.v_RA_System_IPAddresses IPA_Inner GROUP BY IPA_Inner.ResourceID ) IP_Grouped ON CS.ResourceID = IP_Grouped.ResourceID
可选调整规则
- 可以在IP预聚合的WHERE条件中增加过滤逻辑,比如剔除不需要展示的链路本地IPv6地址(fe80开头),只要补充条件
AND IP_Addresses0 NOT LIKE 'fe80%'即可。 - 聚合后的IP顺序默认是按表中存储顺序排列,如果需要固定排序(比如IPv4在前、IPv6在后),只需要在
STRING_AGG函数里加上排序规则,例如STRING_AGG(IP_Addresses0, ';') WITHIN GROUP (ORDER BY CASE WHEN IP_Addresses0 LIKE '%:%' THEN 2 ELSE 1 END)。
内容的提问来源于stack exchange,提问作者J_PT
相关产品推荐
相关产品推荐

