Summary: How to to build a Smart label by building a Regex expression for an IP range
How To:
Example: To find all systems with IPs in the range 192.168.2.2 - 192.168.2.100.
1. Use this URL and fill in the ip address and build the expression automatically in the expression builder: http://www.analyticsmarket.com/freetools/ipregex
REGEX Expression: ^192\.168\.2\.([2-9]|[1-9][0-9]|100)$
2. Create the smart label with IP address Matches Regex Test and then save the label.
3. Then go the SQL of the label and replace with the REGEX expression from the tool above.
I have an IP set that I want labeled (10.101.XX.XX) so I needed a SQL query to get me there. Here is the craziness that I ended with...
select MACHINE.*, C.CLIENT_CONNECTED, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_SECONDS
from ORG3.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION O ON O.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION C ON C.KUID = MACHINE.KUID AND O.ORGANIZATION_ID = 3
where (( (1 in (select 1 from ORG3.MACHINE M2 where M2.ID = MACHINE.ID and M2.IP rlike '^10\\.101\\.(1\\.([1-9]|[1-9][0-9]|1([0-9][0-9])|2([0-4][0-9]|5[0-5]))|(([2-9]|[1-9][0-9]|1([0-9][0-9])|2([0-4][0-9]|5[0-3]))\\.([0-9]|[1-9][0-9]|1([0-9][0-9])|2([0-4][0-9]|5[0-5])))|254\\.([0-9]|[1-9][0-9]|1([0-9][0-9])|2([0-4][0-9]|5[0-4])))$' union select 1 from ORG3.MACHINE_NICS where MACHINE.ID = MACHINE_NICS.ID and MACHINE_NICS.IP rlike '^10\\.101\\.(1\\.([1-9]|[1-9][0-9]|1([0-9][0-9])|2([0-4][0-9]|5[0-5]))|(([2-9]|[1-9][0-9]|1([0-9][0-9])|2([0-4][0-9]|5[0-3]))\\.([0-9]|[1-9][0-9]|1([0-9][0-9])|2([0-4][0-9]|5[0-5])))|254\\.([0-9]|[1-9][0-9]|1([0-9][0-9])|2([0-4][0-9]|5[0-4])))$')) )) - easterdaymatt 11 years ago