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 iManage Client deployment
by  CyberCitizen   Go to last post Go to first unread
Last post: Today at 3:09:47 AM(UTC)
Lansweeper Missing hyperv host details for guest asset LAN-4130
by  danielm  
Go to last post Go to first unread
Last post: 12/7/2019 7:15:56 AM(UTC)
Lansweeper Lansweeper showing MAC address instead of name
by  crashff   Go to last post Go to first unread
Last post: 12/6/2019 10:26:25 PM(UTC)
Lansweeper Dell Warranty Lookup - Incorrect Information
by  Trenton Knew  
Go to last post Go to first unread
Last post: 12/6/2019 10:17:15 PM(UTC)
Lansweeper Incoming Email Parse
by  UEFT   Go to last post Go to first unread
Last post: 12/6/2019 5:46:22 PM(UTC)
Lansweeper SSH - Keyboard Interactive Authentication
by  KevinA-REJIS  
Go to last post Go to first unread
Last post: 12/6/2019 5:22:21 PM(UTC)
Lansweeper Custom OID Scanning / multible OIDs with wildcards
by  Esben.D  
Go to last post Go to first unread
Last post: 12/6/2019 11:57:18 AM(UTC)