→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
wolfpack
Engaged Sweeper
I am trying, without much success, to run a simple report that only has the following results generated:
1) Server list by Hostname/Computername
2) Hard drive letters (optional)
3) Total Hard drive space Free (all drives)
4) Total Hard drive space Used (all drives)
5) Total Hard drive space (all drives)

I have been through nearly every forum discussion, and have tried many of the reports that have been posted, but none of them can give me anything close to what I need. Every one I have tried gives me a whole lot of garbage that I don't need, and nothing that I do need. Any help on this would be greatly appreciated.

Thanks!
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Please use the report below for the information you are after.
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblDiskdrives.Caption,
Ceiling(tblDiskdrives.Freespace / 1024 / 1024) As FreeMB,
Ceiling((tblDiskdrives.Size / 1024 / 1024) - (tblDiskdrives.Freespace / 1024 /
1024)) As UsedMB,
Ceiling(tblDiskdrives.Size / 1024 / 1024) As TotalMB,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1 And
tblDiskdrives.DriveType = 3
Order By tblAssets.Domain,
tblAssets.AssetName,
tblDiskdrives.Caption

View solution in original post

5 REPLIES 5
wolfpack
Engaged Sweeper
Correct. That one turned out pretty cool also. Thanks!
RCorbeil
Honored Sweeper II
Oh. By "all drives" you meant "for each drive". That makes more sense.

Ah well, I'll leave the code. You never know; someday somebody might want to produce something similar. I used it as an opportunity to learn how to sort the result of a union.
RCorbeil
Honored Sweeper II
I'm not sure why you want the total of all drives' space, but try this:

Select Top 1000000
ServerDrives.icon,
ServerDrives.AssetID,
ServerDrives.AssetUnique,
ServerDrives.Domain,
ServerDrives.Description,
ServerDrives.Lastseen,
ServerDrives.Drive,
ServerDrives.FileSystem,
ServerDrives.[Capacity GB],
ServerDrives.[Used GB],
ServerDrives.[Free GB]
From
( Select Top 1000000
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tblAssets.Lastseen,
tblDiskdrives.Caption As Drive,
tblDiskdrives.Caption As SortDrive,
tblDiskdrives.FileSystem,
Ceiling(tblDiskdrives.Size / 1000 / 1000 / 1000) As [Capacity GB],
Ceiling((tblDiskdrives.Size - tblDiskdrives.Freespace) / 1000 / 1000 / 1000) As [Used GB],
Ceiling(tblDiskdrives.Freespace / 1000 / 1000 / 1000) As [Free GB]
From
tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblDiskdrives On tblDiskdrives.AssetID = tblAssets.AssetID
Where
tblAssetCustom.State = 1
And tblComputersystem.Domainrole >= 2
And tblDiskdrives.DriveType = 3

Union All

Select Top 1000000
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tblAssets.Lastseen,
'TOTAL' As Drive,
'zzz' As SortDrive,
Null,
Ceiling( ( Select Sum(AllDrives.Size)
From tblDiskdrives As AllDrives
Where AllDrives.AssetID = tblAssets.AssetID
And AllDrives.DriveType = 3) / 1000 / 1000 / 1000) As [Capacity GB],
Ceiling( ( Select Sum(AllDrives.Size - AllDrives.Freespace)
From tblDiskdrives As AllDrives
Where AllDrives.AssetID = tblAssets.AssetID
And AllDrives.DriveType = 3) / 1000 / 1000 / 1000) As [Used GB],
Ceiling ( (Select Sum(AllDrives.Freespace)
From tblDiskdrives As AllDrives
Where AllDrives.AssetID = tblAssets.AssetID
And AllDrives.DriveType = 3) / 1000 / 1000 / 1000) As [Free GB]
From
tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where
tblAssetCustom.State = 1
And tblComputersystem.Domainrole >= 2
) As ServerDrives
Order By
ServerDrives.AssetUnique,
ServerDrives.SortDrive

It'll produce a list of all the drives in drive letter-order followed by the drives' totals for each server.
wolfpack
Engaged Sweeper
That worked!!! Thank You! Thank You! Thank You!
Hemoco
Lansweeper Alumni
Please use the report below for the information you are after.
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblDiskdrives.Caption,
Ceiling(tblDiskdrives.Freespace / 1024 / 1024) As FreeMB,
Ceiling((tblDiskdrives.Size / 1024 / 1024) - (tblDiskdrives.Freespace / 1024 /
1024)) As UsedMB,
Ceiling(tblDiskdrives.Size / 1024 / 1024) As TotalMB,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1 And
tblDiskdrives.DriveType = 3
Order By tblAssets.Domain,
tblAssets.AssetName,
tblDiskdrives.Caption

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now