SMS: How to Query for IP Subnets in Systems Management Server 2.0 (266438)

SUMMARY

Although you may prefer to perform queries for ranges of Transmission Control Protocol/Internet Protocol (TCP/IP) addresses or subnets, the queries created tend to return unexpected data. This behavior occurs because the data is stored in the database as a variable character (VarChar) without a numeric value.

MORE INFORMATION

Sample Scenario

Set up the following query statement:

MICROSOFT|NETWORK|1.0:IP Address is greater than or equal to '148.108.120.0'
AND MICROSOFT|NETWORK|1.0:IP Address is less than or equal to '148.108.135.255'

You would expect this query to return clients on networks between 148.108.120, 121, 122 through 135. However, the results that you receive contain addresses such as 148.108.13.1, 148.108.13.2, and so forth, which are not in the expected range.

Reason for failure

Because the IP Address is stored in the database as a VarChar, as opposed to a numeric value, it is read differently. With a number, both SMS and SQL read from right to left to determine the size of the number for the query. With a character string, SMS and SQL read from left to right. Consequently, when you perform a query looking for a value between two strings, it has unexpected results. For example, in the "Sample Scenario" above, it would return everything from 12, 120 to 129, and 13, 130 to 135.

Possible Workaround

Include the following "exclusions" in the query:

AND MICROSOFT|NETWORK|1.0:IP Address is not like '148.108.12.%'
AND MICROSOFT|NETWORK|1.0:IP Address is not like '148.108.13.%'

Use the Default Gateway field for the query instead of a TCP/IP range. By doing so, you can base your query on a specific address instead of on a range of addresses.