Report to Show Office Versions

[bluesymbol]

Hi,

I've managed to cobble together the below report, but it's only showing 2007 clients rather than both 2007 & 2010. Can anyone point out where I'm going wrong?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tSoftware.softwareVersion
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
(tblSoftwareUni.softwareName Like
'%Microsoft Office Professional Plus 2007%') Or
(tblSoftwareUni.SoftwarePublisher Like
'%Microsoft Office Professional Plus 2010%')) tSoftware
On tSoftware.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblState On tblState.State = tblAssetCustom.State
Where tSoftware.softwareName Like '%Microsoft Office Professional Plus%'
Order By tblAssets.AssetName,
tSoftware.softwareName


Cheers.

[bluesymbol]

Great! thanks Daniel! 🙂

Knew it would be something simple like that! Staring at the script too long!

[Daniel_B · Accepted solution]

There was a typo at one position. Instead of "softwareName" you had used "softwarePublisher" as filter criterion. Please find the modified report below:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tSoftware.softwareVersion
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select tblSoftware.AssetID,
    tblSoftwareUni.softwareName,
    tblSoftwareUni.SoftwarePublisher,
    tblSoftware.softwareVersion
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Where
    (tblSoftwareUni.softwareName Like
    '%Microsoft Office Professional Plus 2007%') Or
    (tblSoftwareUni.softwareName Like
    '%Microsoft Office Professional Plus 2010%')) tSoftware
    On tSoftware.AssetID = tblAssets.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tblState On tblState.State = tblAssetCustom.State
Where tSoftware.softwareName Like '%Microsoft Office Professional Plus%'
Order By tblAssets.AssetName,
  tSoftware.softwareName