cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
AlexRus227
Engaged Sweeper
find all pc where the office is installed
1 REPLY 1
RCorbeil
Honored Sweeper II
Were it me, I would start by generating a list of software that contains the word "Office".
SELECT
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName
FROM
tblSoftwareUni
WHERE
tblSoftwareUni.softwareName LIKE '%Office%'
ORDER BY
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName

Using that, I would work out the conditions I need to match for actual results, e.g. I wouldn't need to include anything with "OfficeJet". Likewise, there are things like compatibility packs, Access runtimes, document viewers, add-ins and tools that contains "Microsoft Office" in the name, so those would need to be filtered out.

Looking at my own inventory, I would probably set up conditions like this:
WHERE
-- no idea why, but there's at least one entry with no publisher recorded
(tblSoftwareUni.SoftwarePublisher = '' AND tblSoftwareUni.softwareName LIKE 'Microsoft Office Professional%')
-- I see a "Corel Home Office" and a "WordPerfect Office" version_number
OR (tblSoftwareUni.SoftwarePublisher = 'Corel' AND tblSoftwareUni.softwareName LIKE '%Office%')
OR (tblSoftwareUni.softwareName LIKE 'LibreOffice%')
OR (tblSoftwareUni.softwareName LIKE 'OpenOffice.org%')
-- deal with the mess of not-necessarily-Office titles that contain "Microsoft Office" in the name
OR (tblSoftwareUni.SoftwarePublisher LIKE 'Microsoft Corp%'
AND ( tblSoftwareUni.softwareName = 'Microsoft Office'
OR tblSoftwareUni.softwareName LIKE 'Microsoft Office%Pro%'
OR tblSoftwareUni.softwareName = 'Microsoft Office 2010'
OR tblSoftwareUni.softwareName LIKE 'Microsoft Office Starter 20%'
OR tblSoftwareUni.softwareName LIKE 'Microsoft Office Ultimate 20%'
)
-- hopefully redundant, but in case something sneaks through the positive matches above
AND tblSoftwareUni.softwareName NOT LIKE '%Viewer%'
AND tblSoftwareUni.softwareName NOT LIKE '%web components%'
AND tblSoftwareUni.softwareName NOT LIKE '%interop assemblies%'
AND tblSoftwareUni.softwareName NOT LIKE '%activation assistant%'
AND tblSoftwareUni.softwareName NOT LIKE '%compatibility%'
AND tblSoftwareUni.softwareName NOT LIKE '%database engine%'
AND tblSoftwareUni.softwareName NOT LIKE '%runtime%'
AND tblSoftwareUni.softwareName NOT LIKE '%add-in%'
AND tblSoftwareUni.softwareName NOT LIKE '%tools%'
AND tblSoftwareUni.softwareName NOT LIKE '%click-to-run%'
AND tblSoftwareUni.softwareName NOT LIKE '%validation%'
AND tblSoftwareUni.softwareName NOT LIKE '%language%'
AND tblSoftwareUni.softwareName NOT LIKE '%interface pack%'
AND tblSoftwareUni.softwareName NOT LIKE '%live meeting%'
AND tblSoftwareUni.softwareName NOT LIKE '%OneNote%'
AND tblSoftwareUni.softwareName NOT LIKE '%Outlook%'
AND tblSoftwareUni.softwareName NOT LIKE '%Project%'
AND tblSoftwareUni.softwareName NOT LIKE '%Visio %'
)

Once you've figured out the filters, create a report with the tables & fields that you want to see. Make sure to add tblSoftware and tblSoftwareUni, then add the filters you worked out to the WHERE clause of the report.

e.g.
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblAssetCustom.State = 1
AND (-- no idea why, but there's at least one entry with no publisher recorded
(tblSoftwareUni.SoftwarePublisher = '' AND tblSoftwareUni.softwareName LIKE 'Microsoft Office Professional%')
-- I see a "Corel Home Office" and a "WordPerfect Office" version_number
OR (tblSoftwareUni.SoftwarePublisher = 'Corel' AND tblSoftwareUni.softwareName LIKE '%Office%')
OR (tblSoftwareUni.softwareName LIKE 'LibreOffice%')
OR (tblSoftwareUni.softwareName LIKE 'OpenOffice.org%')
-- deal with the mess of not-necessarily-Office titles that contain "Microsoft Office" in the name
OR (tblSoftwareUni.SoftwarePublisher LIKE 'Microsoft Corp%'
AND ( tblSoftwareUni.softwareName = 'Microsoft Office'
OR tblSoftwareUni.softwareName LIKE 'Microsoft Office%Pro%'
OR tblSoftwareUni.softwareName = 'Microsoft Office 2010'
OR tblSoftwareUni.softwareName LIKE 'Microsoft Office Starter 20%'
OR tblSoftwareUni.softwareName LIKE 'Microsoft Office Ultimate 20%'
)
-- hopefully redundant, but in case something sneaks through the positive matches above
AND tblSoftwareUni.softwareName NOT LIKE '%Viewer%'
AND tblSoftwareUni.softwareName NOT LIKE '%web components%'
AND tblSoftwareUni.softwareName NOT LIKE '%interop assemblies%'
AND tblSoftwareUni.softwareName NOT LIKE '%activation assistant%'
AND tblSoftwareUni.softwareName NOT LIKE '%compatibility%'
AND tblSoftwareUni.softwareName NOT LIKE '%database engine%'
AND tblSoftwareUni.softwareName NOT LIKE '%runtime%'
AND tblSoftwareUni.softwareName NOT LIKE '%add-in%'
AND tblSoftwareUni.softwareName NOT LIKE '%tools%'
AND tblSoftwareUni.softwareName NOT LIKE '%click-to-run%'
AND tblSoftwareUni.softwareName NOT LIKE '%validation%'
AND tblSoftwareUni.softwareName NOT LIKE '%language%'
AND tblSoftwareUni.softwareName NOT LIKE '%interface pack%'
AND tblSoftwareUni.softwareName NOT LIKE '%live meeting%'
AND tblSoftwareUni.softwareName NOT LIKE '%OneNote%'
AND tblSoftwareUni.softwareName NOT LIKE '%Outlook%'
AND tblSoftwareUni.softwareName NOT LIKE '%Project%'
AND tblSoftwareUni.softwareName NOT LIKE '%Visio %'
)
)
ORDER BY
tblAssets.AssetName,
tblSoftwareUni.softwareName