Summary: IP addresses in KBOX are not always straightforward to deal with. They can become a problem particularly when you want to search for a machine within a subnet or label machines with smart labels.
The main things that you need to consider are as follows:
- The IP address in inventory is not guaranteed to be the IP address the machine currently has.
- Machines can have multiple IP addresses, but they only have one IP address at shown in the machine record.
- Machines can have multiple Network adapters (NICS),but only one will reflect the current IP.
- KBOX stores all Network Adapter information separately from the current MAC and IP.
- IP Addresses cannot be compared with > and < because they are stored as strings in the database.
Multiple Addresses:
If you are in a DHCP environment then you already know that a machine can have one IP one moment and another the next. This is usually triggered by a significant change such as the machine rebooting or changing physical ports or changing network adapter.
The KBOX does keep track of IP changes BUT in the inventory record it will only show you the IP address the machine had when it last checked in. Therefore, the any KBOX features or reports that you run that rely on the current IP address in inventory could be inaccurate at times.
To find out what the current IP address according to KBOX is you would:
- Go to Computers->inventory->IP address. To verify what the current IP address of the machine actually is you would have run a real-time test on that machine or look in your network configuration (e.g. DHCP table).
- Or you could run this query : select NAME, IP from MACHINE WHERE MACHINE.NAME='pcname'
Multiple Network Adapters:
If a machine has multiple network adapters then the KBOX will track that. It stored all of the known adapters at each inventory in a table called MACHINE_NICS. When you are looking for an IP address you must ask yourself which of these two questions is relevant to you.
- Am I looking for the current IP address the machine is using?
- Am I looking for all IPs that the machine has used for all of its NICs?
By default searches in the web UI and smart labels built in the web UI assume you want to answer question #2.
Let's pretend you want to find machines with IP addresses in the 192.168.1.* subnet. To do this you go to the computer inventory advanced search and choose "IP Address begins with 192.168.1." The kbox would evaluate this against all possible NICs. Here is what that might look like under the covers:
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 ORG2.MACHINE M2 where M2.ID = MACHINE.ID and M2.IP like '192.168.1.%' union select 1 from ORG2.MACHINE_NICS where MACHINE.ID = MACHINE_NICS.ID and MACHINE_NICS.IP like '192.168.1.%')) ))
Here is a rewritten version that is exactly the same thing but might be easier to understand
select DISTINCT MACHINE.*, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from 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 LIKE '192.168.1.%'
If you really wanted to answer this with question #1 then you would want it to run:
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 IP LIKE '192.168.1.%'
There is no way to ask question #1 in the advanced search but you can modify the SQL behind filters.
Most users want to ask question #1.
Comparing IP addresses
For this section we are going to assume we want to answer the question: "Am I looking for the current IP address the machine is using?"
Let's take the above one step farther and consider how we would look for an IP address that was in a range of a subnet. Essentially one address is larger than another but less than a third. For example, what if we wanted to find all IPs between 192.168.1.80 and 192.168.1.230?
Some might write this as
example
where IP > '192.168.1.80' and IP < '192.168.1.231'
The problem with this is that the KBOX stores IP address as strings. It does this because of the periods in them. So when you use a comparison operator on strings it is comparing the sort order of the character set in the database. for example.
'1'<'2' but '2'>'11' because '2' is greater than the first character in the string '11' which is '1'.
To tackle this we can use two different techniques:
- Regular expressions (REGEX)
- INET_ATON helper functions built into the MySQL database
Use regular expressions because you can implement them in the web UI but the INET_ATON functions are easier.
The MySQL database supports the POSIX implementation of REGEX. This is not a regex primer but you can refer to this faq on that (http://itninja.com/blog/view/kace-k1000-smart-labels-creating-a-smart-label-for-an-ip-range-using-regex ). Here is how you would write the above criteria in REGEX:
where IP RLIKE '^192[[|...]]168[[|...]]1[[|...]](8[1-9]|9|1[0-9][0-9]|2([0-2][0-9]|30))$'
translation:
- The 9 IPs in 192.168.1.80's
- or the 10 IPs in 192.168.1.90's
- or the 100 IPs in 192.168.1.100's
- or the 30 IPs from 192.168.1.200 to 192.168.1.229
- or 192.168.1.230
IN the UI you would do an advanced search and use: IP address matches regex ^192[[|...]]168[[|...]]1[[|...]](8[1-9]|9|1[0-9][0-9]|2([0-2][0-9]|30))$
To use INET_ATON you have to be somewhere you can modify the SQL but it's really easy. The above becomes:
WHERE INET_ATON(MACHINE.IP) > INET_ATON('192.168.1.80')
and < INET_ATON(MACHINE.IP) < INET_ATON('192.168.1.231')
Comments