cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
PROSUser
Engaged Sweeper II
I am been trying to create a single report that includes all the SQL Server (any version), MS Visio and MS Project installed in my environment, so far, haven't been able to do so, has anyone ran into this type of report before?
1 ACCEPTED SOLUTION
Tom_P
Lansweeper Employee
Lansweeper Employee
Hi,

The following report will list all active assets that have at least one of the software packages installed. Additional columns/ filters can of course still be added.

Select distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
CASE sqlServer.AssetID When sqlServer.AssetID then 'Yes' else 'No' end as [SQL Server Installed?],
CASE msVisio.AssetID When sqlServer.AssetID then 'Yes' else 'No' end as [MS Visio Installed?],
CASE msProject.AssetID When sqlServer.AssetID then 'Yes' else 'No' end as [MS Project Installed?],
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select AssetID from tblSoftware
Inner Join tblSoftwareUni on tblSoftwareUni.SoftID = tblSoftware.softID
where softwareName like '%SQL Server%') as sqlServer on sqlServer.AssetID = tblAssets.AssetID
Left Join (Select AssetID from tblSoftware
Inner Join tblSoftwareUni on tblSoftwareUni.SoftID = tblSoftware.softID
where softwareName like '%MS Visio%') as msVisio on msVisio.AssetID = tblAssets.AssetID
Left Join (Select AssetID from tblSoftware
Inner Join tblSoftwareUni on tblSoftwareUni.SoftID = tblSoftware.softID
where softwareName like '%MS Project%') as msProject on msProject.AssetID = tblAssets.AssetID
Where tblState.Statename = 'Active' and tblAssets.Assettype = -1
and ((CASE sqlServer.AssetID When sqlServer.AssetID then 'Yes' else 'No' end) = 'Yes'
Or (CASE msVisio.AssetID When sqlServer.AssetID then 'Yes' else 'No' end) = 'Yes'
Or (CASE msProject.AssetID When sqlServer.AssetID then 'Yes' else 'No' end) = 'Yes')
Order By tblAssets.AssetName, tblAssets.Domain

View solution in original post

1 REPLY 1
Tom_P
Lansweeper Employee
Lansweeper Employee
Hi,

The following report will list all active assets that have at least one of the software packages installed. Additional columns/ filters can of course still be added.

Select distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
CASE sqlServer.AssetID When sqlServer.AssetID then 'Yes' else 'No' end as [SQL Server Installed?],
CASE msVisio.AssetID When sqlServer.AssetID then 'Yes' else 'No' end as [MS Visio Installed?],
CASE msProject.AssetID When sqlServer.AssetID then 'Yes' else 'No' end as [MS Project Installed?],
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select AssetID from tblSoftware
Inner Join tblSoftwareUni on tblSoftwareUni.SoftID = tblSoftware.softID
where softwareName like '%SQL Server%') as sqlServer on sqlServer.AssetID = tblAssets.AssetID
Left Join (Select AssetID from tblSoftware
Inner Join tblSoftwareUni on tblSoftwareUni.SoftID = tblSoftware.softID
where softwareName like '%MS Visio%') as msVisio on msVisio.AssetID = tblAssets.AssetID
Left Join (Select AssetID from tblSoftware
Inner Join tblSoftwareUni on tblSoftwareUni.SoftID = tblSoftware.softID
where softwareName like '%MS Project%') as msProject on msProject.AssetID = tblAssets.AssetID
Where tblState.Statename = 'Active' and tblAssets.Assettype = -1
and ((CASE sqlServer.AssetID When sqlServer.AssetID then 'Yes' else 'No' end) = 'Yes'
Or (CASE msVisio.AssetID When sqlServer.AssetID then 'Yes' else 'No' end) = 'Yes'
Or (CASE msProject.AssetID When sqlServer.AssetID then 'Yes' else 'No' end) = 'Yes')
Order By tblAssets.AssetName, tblAssets.Domain