SQL Query for IP help
I have a crazy SQL query....
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])))$')) ))
and I need it to exclude anything with IP 10.101.141.XX...
Can anyone help?
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
**EDIT** For an IP in the 10.101.141.xxx range Select all in that range with \b(10)\.(101)\.(141)\.\d+ **ORIGINAL** I ended up removing that ridiculous SQL query and setting up a smart label using the Smart Label Editor with IP begins with 10.101 AND IP does not begin with 10.101.141...Really simple once I thought of it. Hope this helps someone..
**EDIT** For an IP in the 10.101.141.xxx range Select all in that range with \b(10)\.(101)\.(141)\.\d+ **ORIGINAL** I ended up removing that ridiculous SQL query and setting up a smart label using the Smart Label Editor with IP begins with 10.101 AND IP does not begin with 10.101.141...Really simple once I thought of it. Hope this helps someone..
Please log in to answer
Posted by:
jdornan
11 years ago
Looks like you have a solution but for others that run across this for the record.
For an IP in the 10.101.141.xxx range
Select all in that range with
\b(10)\.(101)\.(141)\.\d+
Posted by:
easterdaymatt
11 years ago
I ended up removing that ridiculous SQL query and setting up a smart label using the Smart Label Editor with IP begins with 10.101 AND IP does not begin with 10.101.141...Really simple once I thought of it. Hope this helps someone..
Comments:
-
This might have changed in 5.5, but previous versions needed IP addresses in regex in order to evaluate them properly: http://www.kace.com/support/resources/kb/article/tool-to-assist-in-building-regex-inventory - jknox 11 years ago