Report for Monitoring Shared Network folder Sizes

[AronJBAR]

Hello All,

I have next to no experience with SQL and I'm in my first IT role. As I'm training one of my tasks has been to monitor the shared folders we have on the network and keep tabs on thier sizes. So far I've got this:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblSharesUni.Name,
tblShares.Lastchanged,
tblDiskdrives.Size,
tblDiskdrives.Freespace,
tblDiskdrives.Size - tblDiskdrives.Freespace As UsedSpace,
tblDiskdrives.Freespace / tblDiskdrives.Size * 100 As PercentageUsed
From tblShares
Inner Join tblAssets On tblShares.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSharesUni On tblShares.ShareUniqueID =
tblSharesUni.ShareUniqueID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblDiskdrives.Size > 0 And tblAssetCustom.State = 1 And
tblSharesUni.Type = 0
Order By tblAssets.AssetName

But it duplicates the Shared drives and I'm slightly unsure of how to reduce the percentages down to 2 decimal places, I've tried ROUND and CONVERT but couldn't quite make it work. So any advice on how I get it just to display the share once and how to reduce it so 2 decimal places would be great!

Thanks in advance

[Daniel_B · Accepted solution]

We don't scan the size of shared folders. What you could do is using two reports. One report which only lists the shares and one report which lists disk drives on your network computers. Please find a modification of your report below which won't list shares as this would cause duplicates (for every share SQL would list all of the disk drives on the same computer).

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblDiskdrives.Caption As [Disk drive],
  Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As numeric) As
  [Size (GB)],
  Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 / 1024 As
  numeric) As [Free space (GB)],
  Cast(Cast(tblDiskdrives.Size - tblDiskdrives.Freespace As bigint) / 1024 /
  1024 / 1024 As numeric) As [Used Space (GB)],
  Cast(Cast(tblDiskdrives.Freespace As float) / tblDiskdrives.Size *
  100 As Decimal(10,2)) As PercentageUsed
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblDiskdrives.Size > 0 And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  [Disk drive]

[AronJBAR]

I've solved the issue of the 2 decimal places (My SQL skills have massively improved in the last few hours).

Right now I'm looking at the report and seeing alot of duplicate values, this leads me to believe I am only seeing the Logical disk sizes rather than the share sizes.

Can anyone let me know if it is even possible to do what I'm asking in SQL?

Cheers,