‎06-22-2017 02:29 PM
Solved! Go to Solution.
‎06-22-2017 04:44 PM
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
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
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
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
‎12-14-2021 09:28 PM
‎06-22-2017 05:45 PM
‎06-22-2017 04:44 PM
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
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
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
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now