Notification

Icon
Error

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

Posted: Tuesday, November 12, 2019 8:37:07 AM(UTC)
AlexRus227

AlexRus227

Member Original PosterPosts: 10
0
Like
find all pc where the office is installed
RC62N
#1RC62N Member Posts: 392  
posted: 11/15/2019 9:58:33 PM(UTC)
Were it me, I would start by generating a list of software that contains the word "Office".
Code:
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:
Code:
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.
Code:
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

Active Discussions

Lansweeper Software Missing Report
by  RC62N   Go to last post Go to first unread
Last post: 12/6/2019 6:09:28 PM(UTC)
Lansweeper Assets without Asset Location
by  JLangthaler  
Go to last post Go to first unread
Last post: 12/5/2019 12:44:19 PM(UTC)
Lansweeper Installed Memory report
by  lansend   Go to last post Go to first unread
Last post: 12/2/2019 8:15:53 PM(UTC)
Lansweeper Custom OID Report
by  bramassendorp  
Go to last post Go to first unread
Last post: 12/2/2019 4:42:48 PM(UTC)
Lansweeper Report thats showing Windows machines when AV is not like =
by  RC62N   Go to last post Go to first unread
Last post: 11/28/2019 5:56:51 PM(UTC)
Lansweeper dhcp addresses available
by  Cesco93   Go to last post Go to first unread
Last post: 11/28/2019 2:56:02 PM(UTC)
Lansweeper All Assets Report on all IP-addresses and MAC-addresses
by  Tommy75  
Go to last post Go to first unread
Last post: 11/27/2019 11:54:44 AM(UTC)