Report on AD OU with devices of 2GB or less disk space

[yura_koresh]

Hello,
I'm kind of new to lansweeper and trying to make a clean up of C:\Windows\Temp on some specific AD OU with disk space <2GB.

Any suggestions on how to make a report on specific OU with disk space <2GB ?

After that I'm planning to set a schedule by using this report and deployment script
del /Q /S c:\Windows\Temp\*.*

Thanks in advance!

[JacobH]

Oh. you didn't mention server or workstation. Sorry - that report is for servers only.


you can remove 'tblComputersystem.Domainrole > 1' from the WHERE clause. That check means 'server only'

[JacobH]

Hm. Do you have active scanning enabled? If not, I don't think it will get the OU information from active directory. I could be wrong though - but I've always used Active Scanning.


Are you sure you have machines with <2% ? you can increase the check in the WHERE clause:


Where tblComputersystem.Domainrole > 1 And tblDiskdrives.DriveType = '3' And
Floor(tblDiskdrives.Size / Power(2, 20)) <> 0 And tblDiskdrives.Freespace /
tblDiskdrives.Size * 100 <= 2 And Floor(tblDiskdrives.Freespace / Power(2,
30)) < 25 And Floor(tblDiskdrives.Freespace / Power(10, 9)) < 25


Good point though - you can change

Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID

to

Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID


to see if you have machines on the report with no OU information...

[JacobH]

I got most of this report from the forum... you take this query and put WHERE tblDiskdrives.Caption = 'C:' and tblADComputers.OU = 'YOUR OU HERE'

if you want to include that OU and subOU's, you change it to LIKE 'YOUR OU HERE%' to match the front part and whatever comes afterwards.




Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname As OS,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Domain,
tblDiskdrives.Caption,
tblDiskdrives.Freespace,
Cast(tblDiskdrives.Freespace / tblDiskdrives.Size * 100 As Numeric(36,2)) As
[% Free],
Cast(tblDiskdrives.Freespace / Power(2, 30) As Numeric(36,2)) As [GiB Free],
Cast(tblDiskdrives.Size / Power(2, 30) As Numeric(36,2)) As [GiB Capacity],
Cast(tblDiskdrives.Freespace / Power(10, 9) As Numeric(36,2)) As [GB Free],
Cast(tblDiskdrives.Size / Power(10, 9) As Numeric(36,2)) As [GB Capacity],
tblDiskdrives.Lastchanged As [Last changed],
tblADComputers.OU
From tblDiskdrives
Inner Join tblAssets On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblComputersystem.Domainrole > 1 And tblDiskdrives.DriveType = '3' And
Floor(tblDiskdrives.Size / Power(2, 20)) <> 0 And tblDiskdrives.Freespace /
tblDiskdrives.Size * 100 <= 2 And Floor(tblDiskdrives.Freespace / Power(2,
30)) < 25 And Floor(tblDiskdrives.Freespace / Power(10, 9)) < 25
Order By [% Free],
tblDiskdrives.Freespace,
tblDiskdrives.Caption

[yura_koresh]

Thank you for this info!
Since we have two domains do I need to specify that somehow ? as i'm still getting 0 results.
Thanks.

JacobH wrote:
I got most of this report from the forum... you take this query and put WHERE tblDiskdrives.Caption = 'C:' and tblADComputers.OU = 'YOUR OU HERE'

if you want to include that OU and subOU's, you change it to LIKE 'YOUR OU HERE%' to match the front part and whatever comes afterwards.




Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname As OS,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Domain,
tblDiskdrives.Caption,
tblDiskdrives.Freespace,
Cast(tblDiskdrives.Freespace / tblDiskdrives.Size * 100 As Numeric(36,2)) As
[% Free],
Cast(tblDiskdrives.Freespace / Power(2, 30) As Numeric(36,2)) As [GiB Free],
Cast(tblDiskdrives.Size / Power(2, 30) As Numeric(36,2)) As [GiB Capacity],
Cast(tblDiskdrives.Freespace / Power(10, 9) As Numeric(36,2)) As [GB Free],
Cast(tblDiskdrives.Size / Power(10, 9) As Numeric(36,2)) As [GB Capacity],
tblDiskdrives.Lastchanged As [Last changed],
tblADComputers.OU
From tblDiskdrives
Inner Join tblAssets On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblComputersystem.Domainrole > 1 And tblDiskdrives.DriveType = '3' And
Floor(tblDiskdrives.Size / Power(2, 20)) <> 0 And tblDiskdrives.Freespace /
tblDiskdrives.Size * 100 <= 2 And Floor(tblDiskdrives.Freespace / Power(2,
30)) < 25 And Floor(tblDiskdrives.Freespace / Power(10, 9)) < 25
Order By [% Free],
tblDiskdrives.Freespace,
tblDiskdrives.Caption