![James_W_ James_W_](https://community.lansweeper.com/html/assets/User_Avatar.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-22-2017 02:29 PM
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
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
![Tom_P Tom_P](https://community.lansweeper.com/legacyfs/online/forums_avatars/12903.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-22-2017 04:44 PM
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
Software information
SQL server information
Disk Drive Information
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
3 REPLIES 3
![OscarLinerio OscarLinerio](https://community.lansweeper.com/html/assets/User_Avatar.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-14-2021 09:28 PM
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.
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_ James_W_](https://community.lansweeper.com/html/assets/User_Avatar.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-22-2017 05:45 PM
Thank you, that gets me a lot further than I was. I can export the results into multiple sheets then merge.
![Tom_P Tom_P](https://community.lansweeper.com/legacyfs/online/forums_avatars/12903.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-22-2017 04:44 PM
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
Software information
SQL server information
Disk Drive Information
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
![](/skins/images/99C55DEA87BDD7EE44074014A1B9F92F/responsive_peak/images/icon_anonymous_message.png)