smart label for machines outside of our network
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%')) ))
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
[ + ] Show comments
Answers (3)
Please log in to answer
Posted by:
GillySpy
13 years ago
Rewrite it to this to get what you describe:
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 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.]])'
Posted by:
ms01ak
13 years ago
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
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
Posted by:
GillySpy
13 years ago
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.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.