Add Report Column showing Office version

[Jono]

Hello,

I have a report that works well. It lists all of our floater (loaner) laptops. Here it is.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
tblAssets.Domain,
tblAssetGroups.AssetGroup,
tblAssetCustom.Comments
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroupLink.AssetGroupID =
tblAssetGroups.AssetGroupID
Where tblAssetGroups.AssetGroup = N'Floaters' And tblAssetCustom.State = 1

I would like to add a column that shows the Office version. I tried adding a column like this:

Or
(tblSoftwareUni.softwareName Like 'Office%')

, but it listed all software if it had Office at all. I want to report to show exactly the same list as the above report, but adding the column to show the Office version. Is that possible?

Thanks,
Jono

[Jono]

Thanks for the extra information. The scope of the report is only those assets that are in the Floaters asset group. This is a group of laptops that we use for loaners.

Thank you, also, for the quick code on listing the asset types. I've wondered about that. I've saved that report for use in the future.

Happy Thanksgiving! (if you're in the US, and even if you're not)

Jono

[RCorbeil]

Good to hear!

Expanding on it a bit, my own inventory includes more than just office suites with "Office" in the software name, such as HP OfficeJet software, so to extend your exclusion list a bit, mine would read

  LEFT JOIN (SELECT
               tblSoftware.AssetID,
               tblSoftwareUni.softwareName
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE
               tblSoftwareUni.softwareName LIKE 'LibreOffice%'
               OR tblSoftwareUni.softwareName LIKE 'OpenOffice%'
               OR (tblSoftwareUni.softwareName LIKE '%Corel%' AND tblSoftwareUni.softwareName LIKE '%Office%')
               OR (tblSoftwareUni.softwareName LIKE 'Microsoft Office%'
                   AND ( tblSoftwareUni.softwareName NOT LIKE '%Visio%'
                         AND tblSoftwareUni.softwareName NOT LIKE '%Compatibility%'
                         AND tblSoftwareUni.softwareName NOT LIKE '%Access%'
                         AND tblSoftwareUni.softwareName NOT LIKE '%Validation%'
                         AND tblSoftwareUni.softwareName NOT LIKE '%Add-In%'
                         AND tblSoftwareUni.softwareName NOT LIKE '%Visual Studio%'
                         AND tblSoftwareUni.softwareName NOT LIKE '%Time Zone%'
                         AND tblSoftwareUni.softwareName NOT LIKE '%Viewer%'
                         AND tblSoftwareUni.softwareName NOT LIKE '%Project%'
                         AND tblSoftwareUni.softwareName NOT LIKE '%Assemblies%'
                         AND tblSoftwareUni.softwareName NOT LIKE '%Meeting%'
                         AND tblSoftwareUni.softwareName NOT LIKE '%Web Components%'
                       )
                  )
            ) AS msoffice ON msoffice.AssetID = tblAssets.AssetID

And are you intentionally listing all assets? If not, you can add another filter to the WHERE clause

  AND tblAssets.AssetType = -1

for only Windows machines.

If you want select asset types, create a report that just shows you the contents of tSysAssetTypes for a list of the type codes and associated descriptions.

SELECT Top 1000000 *
FROM tsysAssetTypes

Then expand that last condition, e.g.

  AND tblAssets.AssetType IN (-1, 22, 13)

for Windows, WinCE and Mac, for instance.

[Jono · Accepted solution]

Thanks RC62N! Copying and pasting brought in some unseen characters that gave me some errors, but I figured it out. Also, I changed the 1st LIKE to %Office% so it would include LibreOffice and OpenOffice.org, and I had to figure out how to get the column to show, but that wasn't too difficult. Here's what I ended up with.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Description,
  tblAssets.Domain,
  tblAssetGroups.AssetGroup,
  tblAssetCustom.Comments,
  msoffice.softwareName
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroupLink.AssetGroupID =
    tblAssetGroups.AssetGroupID
  Left Join (Select tblSoftware.AssetID,
    tblSoftwareUni.softwareName
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%Office%' And
    (tblSoftwareUni.softwareName Not Like '%Visio%' And
      tblSoftwareUni.softwareName Not Like '%Compatibility%' And
      tblSoftwareUni.softwareName Not Like '%Access%' And
      tblSoftwareUni.softwareName Not Like '%Validation%')) As msoffice
    On msoffice.AssetID = tblAssets.AssetID
Where tblAssetGroups.AssetGroup = N'Floaters' And tblAssetCustom.State = 1

As needed, I'll continue to add NOT LIKE statements, but for now this worked great.

[RCorbeil]

One approach:

SELECT
  Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Description,
  tblAssets.Domain,
  tblAssetGroups.AssetGroup,
  tblAssetCustom.Comments
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tblAssets.Assettype = tsysAssetTypes.AssetType
  INNER JOIN tblAssetGroupLink ON tblAssets.AssetID = tblAssetGroupLink.AssetID
  INNER JOIN tblAssetGroups ON tblAssetGroupLink.AssetGroupID = tblAssetGroups.AssetGroupID
  LEFT JOIN ( SELECT
                tblSoftware.AssetID,
                tblSoftwareUni.SoftwareName
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
              WHERE
                tblSoftwareUni.SoftwarePublisher LIKE '%microsoft%'
                AND (tblSoftwareUni.softwareName LIKE '%Office 2000%'
                     OR tblSoftwareUni.softwareName LIKE '%Office Standard%'
                     OR tblSoftwareUni.softwareName LIKE '%Office Professional%'
                     OR tblSoftwareUni.softwareName LIKE '%Office Home & Student%'
                     OR tblSoftwareUni.softwareName LIKE '%Office Home & Business%')
            ) AS msoffice ON msoffice.AssetID=tblAssets.AssetID
WHERE
  tblAssetGroups.AssetGroup = N'Floaters'
  AND tblAssetCustom.State = 1

Microsoft has a tendency to prepend "Microsoft Office" on a lot of components that aren't actually Microsoft Office, so you may need to edit the inclusion list or, alternatively, create an exclusion list (LIKE 'Microsoft Office%" AND NOT LIKE '%Viewer%' AND NOT LIKE '%Visio%" AND NOT LIKE etc.).