
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-13-2015 06:02 PM - last edited on ‎02-23-2023 01:44 PM by Mercedes_O
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
Solved! Go to Solution.
- Labels:
-
Report Center

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-27-2015 05:00 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-27-2015 05:00 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-15-2015 03:58 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-13-2015 07:46 PM
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.
