/bundles/itninjaweb/img/Breadcrumb_cap_w.png
Hi Guys,

I'm trying to create a smart label for machines that aren't in our network (aka at home) I've tried setting up the label but it doesn't seem to quite work
I'm using the the doesn;'t begin with to ignore our ip address subnets


Here's the sql code:

select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (((( (1 in (select 1 from ORG1.MACHINE M2 where M2.ID = MACHINE.ID and M2.IP not like '192.168%' union select 1 from ORG1.MACHINE_NICS where MACHINE.ID = MACHINE_NICS.ID and MACHINE_NICS.IP not like '192.168%')) ) AND (1 in (select 1 from ORG1.MACHINE M2 where M2.ID = MACHINE.ID and M2.IP not like '100.1%' union select 1 from ORG1.MACHINE_NICS where MACHINE.ID = MACHINE_NICS.ID and MACHINE_NICS.IP not like '10.1%')) ) AND (1 in (select 1 from ORG1.MACHINE M2 where M2.ID = MACHINE.ID and M2.IP not like '50.50%' union select 1 from ORG1.MACHINE_NICS where MACHINE.ID = MACHINE_NICS.ID and MACHINE_NICS.IP not like '50.50%')) ))
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
Answer this question or Comment on this question for clarity

Answers

0
Rewrite it to this to get what you describe:

select DISTINCT MACHINE.ID, MN.IP
from ORG1.MACHINE
JOIN MACHINE_NICS MN ON MN.ID=MACHINE.ID
LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1

WHERE
MN.IP NOT RLIKE '^(192[[.period.]]168|100[[.period.]]1[[.period.]]|50[[.period.]]50[[.period.]])'



However, I have a further question:

Are these machines permanently at home or occassionally? Let me rephrase: are you labelling these machines for the purpose of differentiating machines that are currently on the fast LAN vs a slower WAN link OR are you trying to identify designated location?

If it's a case of fast v slow then you should rewrite your label to only target the machine's current IP. Namely MACHINE.IP instead of including MACHINE_NICS.IP. That way when the machines do visit the corporate LAN they can get all the benefits of that but are restricted when remote.

If it's a case of designated location then basing it on IP is probably not consistent enough since those machines might visit the office so i would recommend finding some other way.

If you want the fast v slow thing then right it like this:
select MACHINE.ID
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1

WHERE
MACHINE.IP NOT RLIKE '^(192[[.period.]]168|100[[.period.]]1[[.period.]]|50[[.period.]]50[[.period.]])'
Answered 06/24/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Hi,

I'm trying to target machines that leave the company (Client takes laptop home and never brings it back sort of thing). Basically, if a machine moves to someone's house we'd like to know and be able to provide a list to management.

Thanks,

Mike
Answered 06/24/2011 by: ms01ak
Tenth Degree Black Belt

Please log in to comment
0
It sounds like you want the latter then. However, this depends upon it still connecting. If a machine leaves the company will it connect to the kbox? Smart labels on machines are only evaluated when a machine checks in.
Answered 06/24/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment