Reporting OS & Removing a bunch of IP range of the report.

[akhx]

I'm trying to get a Global operating system overview of our PC all around our 100 compagny. I need to remove a bunch of ip range of compagny that we're not supporting. Here what i have so far.

 Select Top 1000000 tblAssets.Domain,
  tblAssets.AssetName,
  tblOperatingsystem.Caption,
  tblAssets.Lastseen,
  tsysIPLocations.StartIP,
  tsysIPLocations.EndIP,
  tblAssets.IPNumeric
  Round((Cast((DateDiff(mm, tblBIOS.ReleaseDate, GetDate())) As float) / 12),
  0) As [PC Age],
  
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblOperatingsystem
  On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Inner Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
  And tblAssets.IPNumeric <= tsysIPLocations.EndIP

Where (tblOperatingsystem.Caption = 'Microsoft Windows 7 Professionnel' And
    tsysIPLocations.StartIP != '010134059001' And tsysIPLocations.EndIP != '010134058255'    And tblAssetCustom.State = 1)

[Hemoco · Accepted solution]

Have you configured IP locations.
Because if you have done this you can filter on the name of the ip location instead of the start and end ip.

You can then use the following report that filters on a specific ip location. Please change the red word with the name of the ip location that you want to filter on.

Select Top 1000000 tblAssets.Domain,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblAssets.Lastseen,
tsysIPLocations.StartIP,
tsysIPLocations.EndIP,
tblAssets.IPNumeric,
Round((Cast((DateDiff(mm, tblBIOS.ReleaseDate, GetDate())) As float) / 12),
0) As [PC Age]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblOperatingsystem.Caption = 'Microsoft Windows 7 Professionnel'
And tblAssetCustom.State = 1 And tsysIPLocations.IPLocation =
'name ip location'