→ 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: 
Culejoe
Engaged Sweeper
The short of what I want is to create a disk space report for a specific subnet range. So I want to scan 192.168.0.x and I'm using the following script. The alternative would be to add the names of the servers i want in the report somehow but its currently about 80 machines.

I'm not good yet at making these sql reports so my second question would be is there a quick solution for converting the disk space output to be GB instead of MB?

Thanks for any help!!


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
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.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


-Tyler
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Point 1: Add one more condition to the WHERE clause.
WHERE
tblAssets.IPAddress LIKE '192.168.1.%'
AND tblAssetCustom.State = 1 -- active
AND tblComputersystem.Domainrole > 1 -- server
AND tblDiskdrives.DriveType = 3 -- hard drive

% is a wildcard, so you're saying IPAddress starts with '192.168.0.'

In case you weren't intending to limit your query to servers, see tblDomainRoles for the role names.

As to the second point, just divide by another 1024.
Base units: bytes
/1024: kibibytes
/1024: mebibytes
/1024: gibibytes

View solution in original post

4 REPLIES 4
Culejoe
Engaged Sweeper
That worked perfectly! Thanks for the help.
phcosta
Engaged Sweeper II
Culejoe wrote:
That worked perfectly! Thanks for the help.


Eu queria algo parecido.
Mas não estou sabendo como fazer o report pra trazer já em cima do range : 10.111.8.1 - 10.111.10.255
E não 10.111.8.&
RCorbeil
Honored Sweeper II
Point 1: Add one more condition to the WHERE clause.
WHERE
tblAssets.IPAddress LIKE '192.168.1.%'
AND tblAssetCustom.State = 1 -- active
AND tblComputersystem.Domainrole > 1 -- server
AND tblDiskdrives.DriveType = 3 -- hard drive

% is a wildcard, so you're saying IPAddress starts with '192.168.0.'

In case you weren't intending to limit your query to servers, see tblDomainRoles for the role names.

As to the second point, just divide by another 1024.
Base units: bytes
/1024: kibibytes
/1024: mebibytes
/1024: gibibytes
phcosta
Engaged Sweeper II
Fiz algo assim :

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tsysAssetTypes.AssetTypename As [Asset Type],
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssets
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblAssetCustom.State = tblState.State
Where tblAssets.IPAddress Like '10.111.9.%' And tsysAssetTypes.AssetTypename =
'Windows' And tsysAssetTypes.AssetTypename <> 'location' And
tblState.Statename = 'active'
Order By tblAssets.AssetName Desc


Será que consigo pegar no range : 10.111.8.1 - 10.111.10.255 ???