/build/static/layout/Breadcrumb_cap_w.png

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..
Posted by: jdornan 10 years ago
Red Belt
1

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 10 years ago
7th Degree Black Belt
0

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 10 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ