Report for specific IP range

[Culejoe]

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

[Culejoe]

That worked perfectly! Thanks for the help.

[phcosta]

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 · Accepted solution]

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]

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 ???