Creat report ALL office (ms officce,libreoffice,openoffice)

[AlexRus227]

find all pc where the office is installed

[RCorbeil]

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