→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
saumil18
Engaged Sweeper
I can see computer's hard drive partition and free space on Disk information tab.
how can i generate report shows these details for all computers in a single report?
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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 🙂

A sample 5.0 disk report can be found below. Very few database tables have been renamed. For 5.0 database changes, please refer to this forum thread.
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

View solution in original post

7 REPLIES 7
Hemoco
Lansweeper Alumni
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
blaired
Engaged Sweeper
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




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 🙂

Blaired
Hemoco
Lansweeper Alumni
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 🙂

A sample 5.0 disk report can be found below. Very few database tables have been renamed. For 5.0 database changes, please refer to this forum thread.
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
Fuzzanator
Engaged Sweeper
What lines would I add to create a column for used hard drive space? I like having the free space and total space but I am wanting one that shows how much is used

Thanks
Admin_Landert
Engaged Sweeper
Very good information for me also.

But is it possible to get the drives in one line? Cause i have to create a report where i have all the servers and workstations with OS, SP, CPU, Memory and Disks. Till now it was no problem. But i dont get it with the disk drives.

With this HD Information Report i have ServerX listed three times because he has three partitions. ServerY only once. ServerZ twice... and so on.

It would be great if i could use 1 line for a server or WS, even if they have 2 or three partitions. And if a server or WS just has one partition, this fields for 2nd and 3rd could be blank.

saumil18
Engaged Sweeper
Worked perfectly!. Thanks a lot.
Hemoco
Lansweeper Alumni
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