→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Cam
Engaged Sweeper II

Hey All,
I have the following report for Computers with Microsoft Office:


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblSerialnumber.Product,
tblSerialnumber.ProductID,
tblSerialnumber.ProductKey,
tsysOS.Image As icon
From tblAssets
Inner Join tblSerialnumber On tblAssets.AssetID = tblSerialnumber.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblSerialnumber.Product Like '%microsoft%office%' And
tblAssetCustom.State = 1
Order By tblAssets.AssetUnique,
tblSerialnumber.Product



Which results with something like the picture shown below. My issue is that I have tons of duplicate computer names in the report (due to them having multiple versions of Office installed). Is there any ways to combine the duplicate computer names and comma separate the Product column? I don't even need the ProductID or ProductKey columns (so I can remove those).



Thanks,
Cam

1 ACCEPTED SOLUTION
Cam
Engaged Sweeper II
Thank you both. I guess I need to combine the 'sql stuff xml path' as shown by RC62N with the report provided by Susan. I'll try and post the final code once I have a chance to get it working.

View solution in original post

3 REPLIES 3
Cam
Engaged Sweeper II
Thank you both. I guess I need to combine the 'sql stuff xml path' as shown by RC62N with the report provided by Susan. I'll try and post the final code once I have a chance to get it working.
Susan_A
Lansweeper Alumni
You should not be using license key information (tblSerialnumber) to report on software installations. Software installation data is stored in tblSoftware/tblSoftwareUni.

Please have a look at this knowledge base article, which explains the difference between software installations and software license keys, and build a report based on tblSoftware/tblSoftwareUni instead. A sample report can be found here. Your current report will not accurately reflect which computers have Office installed.
RCorbeil
Honored Sweeper II
Google up "sql stuff xml path".

My first experiment with it using your question as the challenge:
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
Stuff( ( SELECT
', ' + tblSerialnumber.Product
FROM
tblSerialnumber
WHERE
tblAssets.AssetID = tblSerialnumber.AssetID
AND tblSerialnumber.Product Like '%microsoft%office%'
ORDER BY
tblSerialnumber.Product
FOR Xml Path('')
), 1, 2, '') AS Products,
tsysOS.Image As icon
FROM
tblAssets
INNER JOIN tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysOS On tblAssets.OScode = tsysOS.OScode
WHERE
tblAssetCustom.State = 1
ORDER BY
tblAssets.AssetUnique

Product ID and key concatenated just because I wanted to see them there.