To convert SerialNumberID and other ASCII-Code Array into String, you need a quite complex recursive Query (WITH-Clause) in your report:
[ WITH <common_table_expression> [ ,...n ] ]
<common_table_expression>::=
expression_name [ (column_name [ ,...n ] ) ]
AS
(CTE_query_definition)
You don't need to deploy any package on clients.
SQL-Query:
;WITH T (myManNameString, myManNameStrings, myManSerialString, myManSerialStrings, myManUFNString, myManUFNStrings, InstanceName, ResourceID, RevisionID, WeekOfManufacture, YearOfManufacture)
AS
(
SELECT CAST(CHAR(CASE
WHEN CHARINDEX(', ', Monitor1.ManufacturerName0) > 0 THEN LTRIM(SUBSTRING(Monitor1.ManufacturerName0, 1, CHARINDEX(', ', Monitor1.ManufacturerName0) - 1))
ELSE Monitor1.ManufacturerName0
END) AS Varchar(max)) AS oneManName,
LTRIM(SUBSTRING(Monitor1.ManufacturerName0, CHARINDEX(', ', Monitor1.ManufacturerName0) + 1, LEN(Monitor1.ManufacturerName0) - CHARINDEX(', ', Monitor1.ManufacturerName0))) AS myManNameStrings,
CAST(CHAR(CASE
WHEN CHARINDEX(', ', Monitor1.SerialNumberID0) > 0 THEN LTRIM(SUBSTRING(Monitor1.SerialNumberID0, 1, CHARINDEX(', ', Monitor1.SerialNumberID0) - 1))
ELSE Monitor1.SerialNumberID0
END) AS Varchar(max)) AS oneManSerial,
LTRIM(SUBSTRING(Monitor1.SerialNumberID0, CHARINDEX(', ', Monitor1.SerialNumberID0) + 1, LEN(Monitor1.SerialNumberID0) - CHARINDEX(', ', Monitor1.SerialNumberID0))) AS myManSerialStrings,
CAST(CHAR(CASE
WHEN CHARINDEX(', ', Monitor1.UserFriendlyName0) > 0 THEN LTRIM(SUBSTRING(Monitor1.UserFriendlyName0, 1, CHARINDEX(', ', Monitor1.UserFriendlyName0) - 1))
ELSE Monitor1.UserFriendlyName0
END) AS Varchar(max)) AS oneManUFN,
LTRIM(SUBSTRING(Monitor1.UserFriendlyName0, CHARINDEX(', ', Monitor1.UserFriendlyName0) + 1, LEN(Monitor1.UserFriendlyName0) - CHARINDEX(', ', Monitor1.UserFriendlyName0))) AS myManUFNStrings,
InstanceName0, ResourceID, RevisionID, WeekOfManufacture0, YearOfManufacture0
FROM v_GS_WmiMonitorIDView0 AS Monitor1
UNION ALL
SELECT myManNameString + CHAR(CASE
WHEN CHARINDEX(', ', myManNameStrings) > 0 THEN LTRIM(SUBSTRING(myManNameStrings, 1, CHARINDEX(', ', myManNameStrings) - 1))
ELSE myManNameStrings
END),
CASE
WHEN CHARINDEX(', ', myManNameStrings) > 0 THEN LTRIM(SUBSTRING(myManNameStrings, CHARINDEX(', ', myManNameStrings) + 1,
LEN(myManNameStrings) - CHARINDEX(', ', myManNameStrings)))
ELSE NULL
END,
myManSerialString + CHAR(CASE
WHEN CHARINDEX(', ', myManSerialStrings) > 0 THEN LTRIM(SUBSTRING(myManSerialStrings, 1, CHARINDEX(', ', myManSerialStrings) - 1))
ELSE myManSerialStrings
END),
CASE
WHEN CHARINDEX(', ', myManSerialStrings) > 0 THEN LTRIM(SUBSTRING(myManSerialStrings, CHARINDEX(', ', myManSerialStrings) + 1,
LEN(myManSerialStrings) - CHARINDEX(', ', myManSerialStrings)))
ELSE NULL
END,
myManUFNString + CHAR(CASE
WHEN CHARINDEX(', ', myManUFNStrings) > 0 THEN LTRIM(SUBSTRING(myManUFNStrings, 1, CHARINDEX(', ', myManUFNStrings) - 1))
ELSE myManUFNStrings
END),
CASE
WHEN CHARINDEX(', ', myManUFNStrings) > 0 THEN LTRIM(SUBSTRING(myManUFNStrings, CHARINDEX(', ', myManUFNStrings) + 1,
LEN(myManUFNStrings) - CHARINDEX(', ', myManUFNStrings)))
ELSE NULL
END,
Monitor2.InstanceName0, Monitor2.ResourceID, Monitor2.RevisionID, Monitor2.WeekOfManufacture0, Monitor2.YearOfManufacture0
FROM v_GS_WmiMonitorIDView0 AS Monitor2
INNER JOIN T
ON T.InstanceName = Monitor2.InstanceName0
AND T.RevisionID = Monitor2.RevisionID
AND T.ResourceID = Monitor2.ResourceID
WHERE myManNameStrings IS NOT NULL
)
SELECT SYS.Netbios_Name0, v_gs_system_enclosure.serialnumber0 AS "Computer Serial Number", MAX(myManNameString) AS 'Monitor Manufacturer Name', MAX(myManSerialString) AS 'Monitor Serial Number',
CASE WHEN MAX(myManUFNString) = '0' THEN 'Integrated Monitor'
WHEN MAX(myManUFNString) is null THEN 'Integrated Monitor'
ELSE MAX(myManUFNString) END AS 'Monitor User Friendly Name', MAX(T.WeekOfManufacture) AS 'Monitor Week Of Manufacture', MAX(T.YearOfManufacture) AS 'Monitor Year Of Manufacture'
FROM T INNER JOIN v_R_System as SYS
ON T.ResourceID = SYS.ResourceID
INNER JOIN v_gs_system_enclosure ON v_gs_system_enclosure.ResourceID = SYS.ResourceID
GROUP BY T.InstanceName, T.RevisionID, SYS.Netbios_Name0, v_gs_system_enclosure.serialnumber0
ORDER BY SYS.Netbios_Name0
