→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Reen87
Engaged Sweeper II
Hello all,

I am whanting a report that will list all disks on my servers (only servers) show how mutch free space it has, and also show me what shares that are on that disk, is this posible to make?


I am realy not a SQL person, and don't even know where to start.

hope any of you can help me with this,


Ree
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
List of server hard drives. Capacity/free space in gigabytes (marketing size; 10^9) and gibibytes (computer geek size; 2^30). Choose whichever you prefer.
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblDiskdrives.Caption,
tblDiskdrives.Volumename,
tblDiskdrives.FileSystem,
Ceiling(tblDiskdrives.Size / Power(10, 9)) As [Capacity (GB)],
Ceiling(tblDiskdrives.Freespace / Power(10, 9)) As [Free (GB)],
Ceiling(tblDiskdrives.Size / Power(2, 30)) As [Capacity (GiB)],
Ceiling(tblDiskdrives.Freespace / Power(2, 30)) As [Free (GiB)],
tbldomainroles.Domainrolename
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblDiskdrives ON tblAssets.AssetID = tblDiskdrives.AssetID
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
INNER JOIN tbldomainroles ON tbldomainroles.Domainrole = tblComputersystem.Domainrole
WHERE
tblAssetCustom.State = 1 -- active asset
AND tblComputersystem.Domainrole > 1 -- server
AND tblDiskdrives.DriveType = 3 -- fixed disk drive
ORDER BY
tblAssets.AssetName,
tblDiskdrives.Caption

Listing shares has been covered in a variety of threads, e.g. this one. You can add the domain role filter from the above query to limit the output to servers. (Add the join to tblComputersystem and add the Domainrole > 1 filter to only list servers.)

View solution in original post

1 REPLY 1
RCorbeil
Honored Sweeper II
List of server hard drives. Capacity/free space in gigabytes (marketing size; 10^9) and gibibytes (computer geek size; 2^30). Choose whichever you prefer.
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblDiskdrives.Caption,
tblDiskdrives.Volumename,
tblDiskdrives.FileSystem,
Ceiling(tblDiskdrives.Size / Power(10, 9)) As [Capacity (GB)],
Ceiling(tblDiskdrives.Freespace / Power(10, 9)) As [Free (GB)],
Ceiling(tblDiskdrives.Size / Power(2, 30)) As [Capacity (GiB)],
Ceiling(tblDiskdrives.Freespace / Power(2, 30)) As [Free (GiB)],
tbldomainroles.Domainrolename
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblDiskdrives ON tblAssets.AssetID = tblDiskdrives.AssetID
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
INNER JOIN tbldomainroles ON tbldomainroles.Domainrole = tblComputersystem.Domainrole
WHERE
tblAssetCustom.State = 1 -- active asset
AND tblComputersystem.Domainrole > 1 -- server
AND tblDiskdrives.DriveType = 3 -- fixed disk drive
ORDER BY
tblAssets.AssetName,
tblDiskdrives.Caption

Listing shares has been covered in a variety of threads, e.g. this one. You can add the domain role filter from the above query to limit the output to servers. (Add the join to tblComputersystem and add the Domainrole > 1 filter to only list servers.)