cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

List of all servers with flag for specific softwre

malbanese1
Engaged Sweeper II
I am trying to create a report that will show all of the servers on my domain and add a Yes/No field to show the presence of a specific software package from Add/Remove list. I can get a list of only the servers with the software, but I want to see what the total percentage of installation is.

I am working on a software deployment project and I want to make sure that I cover all of the servers.

Thank you!
1 ACCEPTED SOLUTION

Andy_Sismey
Champion Sweeper III
Here is an example of a report to display all Servers, with Yes No columns for 3 types of software :

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
Case
When Software1.AssetID Is Not Null Then 'Yes'
Else 'No'
End As 'Software1',
Case
When Software2.AssetID Is Not Null Then 'Yes'
Else 'No'
End As 'Software2',
Case
When Software3.AssetID Is Not Null Then 'Yes'
Else 'No'
End As 'Software3'
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Left Join (Select tblSoftware.softwareVersion,
tblSoftwareUni.softwareName,
tblSoftware.AssetID
From lansweeperdb.dbo.tblSoftware
Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Software1%') Software1 On
Software1.AssetID = tblassets.AssetID
Left Join (Select tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftware.AssetID
From lansweeperdb.dbo.tblSoftware
Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Software3%') Software3 On
Software3.AssetID = tblassets.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From lansweeperdb.dbo.tblSoftware
Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Software2%') Software2 On
Software2.AssetID = tblassets.AssetID
Inner Join lansweeperdb.dbo.tblComputersystem On tblassets.AssetID =
tblComputersystem.AssetID
Where tsysassettypes.AssetTypename Like 'Windows' And
tblComputersystem.Domainrole > 1 And tblassetcustom.State = 1


View solution in original post

2 REPLIES 2

malbanese1
Engaged Sweeper II
Thank you, this worked perfectly

Andy_Sismey
Champion Sweeper III
Here is an example of a report to display all Servers, with Yes No columns for 3 types of software :

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
Case
When Software1.AssetID Is Not Null Then 'Yes'
Else 'No'
End As 'Software1',
Case
When Software2.AssetID Is Not Null Then 'Yes'
Else 'No'
End As 'Software2',
Case
When Software3.AssetID Is Not Null Then 'Yes'
Else 'No'
End As 'Software3'
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Left Join (Select tblSoftware.softwareVersion,
tblSoftwareUni.softwareName,
tblSoftware.AssetID
From lansweeperdb.dbo.tblSoftware
Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Software1%') Software1 On
Software1.AssetID = tblassets.AssetID
Left Join (Select tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftware.AssetID
From lansweeperdb.dbo.tblSoftware
Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Software3%') Software3 On
Software3.AssetID = tblassets.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From lansweeperdb.dbo.tblSoftware
Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Software2%') Software2 On
Software2.AssetID = tblassets.AssetID
Inner Join lansweeperdb.dbo.tblComputersystem On tblassets.AssetID =
tblComputersystem.AssetID
Where tsysassettypes.AssetTypename Like 'Windows' And
tblComputersystem.Domainrole > 1 And tblassetcustom.State = 1