cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
bluesymbol
Engaged Sweeper II
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.
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

2 REPLIES 2
bluesymbol
Engaged Sweeper II
Great! thanks Daniel! 🙂

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

Daniel_B
Lansweeper Alumni
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