Community FAQ
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

Reports & Analytics

Ask about reports you're interested in and share reports you've created. Subscribe to receive daily updates of reports shared in the Community.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now