free space repot

[Reen87]

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

[RCorbeil · Accepted solution]

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.)