→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
07-08-2010 10:06 PM
Solved! Go to Solution.
03-20-2013 05:38 PM
blaired wrote:
Is there a version of this for Lansweeper 5? As I've noticed that a lot of the table names have changed. I've attempted myself but SQL skills aren't up to much 🙂
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblOperatingsystem.Caption As FullOSName,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblDiskdrives.Caption As [Disk],
Ceiling(tblDiskdrives.Freespace / 1024 / 1024) As FreeMB,
Ceiling(tblDiskdrives.Size / 1024 / 1024) As TotalMB
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Where tblAssetCustom.State = 1 And
tblDiskdrives.DriveType = 3
Order By tblAssets.Domain,
tblAssets.AssetName,
tblDiskdrives.Caption
06-26-2012 05:05 PM
Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
Web40OSName.OSname, tblOperatingsystem.Description, tblDiskdrives.Caption,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
Free, Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) -
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
Used, Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
[Total size], Web40OSName.Compimage As icon
From tblComputers Inner Join
tblDiskdrives On tblComputers.Computername = tblDiskdrives.Computername
Inner Join
tblOperatingsystem On tblComputers.Computername =
tblOperatingsystem.Computername Inner Join
web40ActiveComputers On tblComputers.Computername =
web40ActiveComputers.Computername Inner Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join
tblComputersystem On tblComputers.Computername =
tblComputersystem.Computername
Where Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) <> 0 And
tblDiskdrives.DriveType = '3'
Order By tblComputers.Computer, Cast(Cast(tblDiskdrives.Freespace As bigint) /
1024 / 1024 As numeric), tblDiskdrives.Caption
03-19-2013 06:59 PM
Lansweeper wrote:
Please try the report below.Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
Web40OSName.OSname, tblOperatingsystem.Description, tblDiskdrives.Caption,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
Free, Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) -
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
Used, Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
[Total size], Web40OSName.Compimage As icon
From tblComputers Inner Join
tblDiskdrives On tblComputers.Computername = tblDiskdrives.Computername
Inner Join
tblOperatingsystem On tblComputers.Computername =
tblOperatingsystem.Computername Inner Join
web40ActiveComputers On tblComputers.Computername =
web40ActiveComputers.Computername Inner Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join
tblComputersystem On tblComputers.Computername =
tblComputersystem.Computername
Where Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) <> 0 And
tblDiskdrives.DriveType = '3'
Order By tblComputers.Computer, Cast(Cast(tblDiskdrives.Freespace As bigint) /
1024 / 1024 As numeric), tblDiskdrives.Caption
03-20-2013 05:38 PM
blaired wrote:
Is there a version of this for Lansweeper 5? As I've noticed that a lot of the table names have changed. I've attempted myself but SQL skills aren't up to much 🙂
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblOperatingsystem.Caption As FullOSName,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblDiskdrives.Caption As [Disk],
Ceiling(tblDiskdrives.Freespace / 1024 / 1024) As FreeMB,
Ceiling(tblDiskdrives.Size / 1024 / 1024) As TotalMB
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Where tblAssetCustom.State = 1 And
tblDiskdrives.DriveType = 3
Order By tblAssets.Domain,
tblAssets.AssetName,
tblDiskdrives.Caption
06-26-2012 04:43 PM
08-25-2010 02:28 PM
07-13-2010 08:43 PM
07-09-2010 02:21 PM
Select Top 1000000 dbo.tblComputers.Computername,
dbo.tblComputers.ComputerUnique, Web40OSName.OSname,
dbo.tblOperatingsystem.Description, dbo.tblDiskdrives.Caption,
Cast(Cast(dbo.tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
Free, Cast(Cast(dbo.tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
[Total size], Web40OSName.Compimage As icon
From dbo.tblComputers Inner Join
dbo.tblDiskdrives On dbo.tblComputers.Computername =
dbo.tblDiskdrives.Computername Inner Join
dbo.tblOperatingsystem On dbo.tblComputers.Computername =
dbo.tblOperatingsystem.Computername Inner Join
dbo.web40ActiveComputers On dbo.tblComputers.Computername =
dbo.web40ActiveComputers.Computername Inner Join
Web40OSName On Web40OSName.Computername = dbo.tblComputers.Computername
Inner Join
tblComputersystem On dbo.tblComputers.Computername =
tblComputersystem.Computername
Where Cast(Cast(dbo.tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) <> 0
And dbo.tblDiskdrives.DriveType = '3'
Order By dbo.tblComputers.Computer, Cast(Cast(dbo.tblDiskdrives.Freespace As
bigint) / 1024 / 1024 As numeric), dbo.tblDiskdrives.Caption
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now