Report request for all servers and specific info

[James_W_]

This may be a tall order, I'm in need of a report that lists all servers and as much as the following info as possible. Please help.
Server name, OS and version, Physical or Virtual, Installed software, SQL databases and Version, disk space used, I.P. address, If virtual Datastore name.

I have a basic start that I found somewhere before:

Select Top 1000000 tblAssets.AssetName,
tsysOS.OSname As [Operating System],
tblComputersystem.Model,
tblAssets.IPAddress,
tsysIPLocations.IPLocation As Location
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tsysOS.OSname Like 'Win 20%'
Order By tblAssets.AssetName

[OscarLinerio]

Good day!!

Im looking for a report where its include all drives like this report and order by specific software. Is it possible to include that? I mean include "order by specific software".

I saw an examples where we can get all drives report and others examples where we can get a report by specific software but when I tried to join both report I got and error.

Thanks, have a greate day.

[James_W_]

Thank you, that gets me a lot further than I was. I can export the results into multiple sheets then merge.

[Tom_P · Accepted solution]

Although it is technically possible to return all the requested information into one report, as multiple tables (software, sql server data, ...) can return multiple rows, this would cause the report to return a lot of 'duplicate' information. In addition, this could also lower the performance and general usability of the report. In light of this, we've split the request into multiple reports, all of them based on the same base report, allowing you to combine them into one report if needed/required.

General SQL

Select 
	Top 1000000 tblAssets.AssetName,
	tblComputersystem.Model,
	tblAssets.IPAddress,
	tsysIPLocations.IPLocation As Location,
	tsysOS.OSname As OSCode,
	tblOperatingsystem.Caption As 'Operating System',
	tsysOS.OScode as 'Build Number',
	CASE
		WHEN tblAssetCustom.Model like '%virtual%' THEN 'Yes' 
		ELSE 'No'
	  END as IsVirtual
From tblAssets
	Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
	Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
	Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
	Inner Join tblDomainroles On tblDomainroles.Domainrole = tblComputersystem.Domainrole
	Inner Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
	Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblDomainroles.Domainrole > 1
Order By tblAssets.AssetName

Software information

Select 
	Top 1000000 tblAssets.AssetName,
	tblComputersystem.Model,
	tblAssets.IPAddress,
	tsysIPLocations.IPLocation As Location,
	tsysOS.OSname As OSCode,
	tblOperatingsystem.Caption As 'Operating System',
	tsysOS.OScode as 'Build Number',
	CASE
		WHEN tblAssetCustom.Model like '%virtual%' THEN 'Yes' 
		ELSE 'No'
	  END as IsVirtual,
	tblSoftwareUni.softwareName as Software,
	tblSoftware.softwareVersion as Version,
	tblSoftwareUni.SoftwarePublisher as Publisher
From tblAssets
	Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
	Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
	Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
	Inner Join tblDomainroles On tblDomainroles.Domainrole = tblComputersystem.Domainrole
	Inner Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
	Inner Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
	Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
	Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblDomainroles.Domainrole > 1
Order By tblAssets.AssetName, tblSoftwareUni.softwareName

SQL server information

Select 
	Top 1000000 tblAssets.AssetName,
	tblComputersystem.Model,
	tblAssets.IPAddress,
	tsysIPLocations.IPLocation As Location,
	tsysOS.OSname As OSCode,
	tblOperatingsystem.Caption As 'Operating System',
	tsysOS.OScode as 'Build Number',
	CASE
		WHEN tblAssetCustom.Model like '%virtual%' THEN 'Yes' 
		ELSE 'No'
	  END as IsVirtual,
	tblSqlServers.serviceName,
	tblSqlServers.displayVersion,
	tblSqlDatabases.name as DBName
From tblAssets
	Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
	Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
	Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
	Inner Join tblDomainroles On tblDomainroles.Domainrole = tblComputersystem.Domainrole
	Inner Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
	Inner Join tblSqlServers On tblSqlServers.AssetID = tblAssets.AssetID
	Inner Join tblSqlDatabases On tblSqlDatabases.sqlServerId = tblSqlServers.sqlServerId
	Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblDomainroles.Domainrole > 1
Order By tblAssets.AssetName

Disk Drive Information

Select 
	Top 1000000 tblAssets.AssetName,
	tblComputersystem.Model,
	tblAssets.IPAddress,
	tsysIPLocations.IPLocation As Location,
	tsysOS.OSname As OSCode,
	tblOperatingsystem.Caption As 'Operating System',
	tsysOS.OScode as 'Build Number',
	CASE
		WHEN tblAssetCustom.Model like '%virtual%' THEN 'Yes' 
		ELSE 'No'
	  END as IsVirtual,
	tblDiskdrives.caption, 
	tblDiskdrives.size, 
	tblDiskdrives.Freespace
From tblAssets
	Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
	Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
	Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
	Inner Join tblDomainroles On tblDomainroles.Domainrole = tblComputersystem.Domainrole
	Inner Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
	Inner Join tblDiskdrives On tblDiskdrives.AssetID = tblAssets.AssetID and tblDiskdrives.size is not null
	Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblDomainroles.Domainrole > 1
Order By tblAssets.AssetName, tblDiskdrives.caption