K1000: Machine Smart Labels
When I first set up our kbox, I set up machine labels based on IP ranges that would identify the location of a computer. I'm in a school district and equipment moves around sometimes and IP gives us some definite information on location on our network. Originally i created the labels using the wizard and there's a lot of extra junk in there. Now that I know a little more SQL, I'm wondering if I can simplify these queries to make it easier to manage and change them.
Here's the query created by the wizard:
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 like '10.19%' union select 1 from ORG1.MACHINE_NICS where MACHINE.ID = MACHINE_NICS.ID and MACHINE_NICS.IP like '10.19%')) ))
and here's my much simplified version:
select *
from MACHINE
where IP like '10.19.%'
Am I missing something? Is there a reason to use the first query instead of the second?
Answers (1)
I believe it needs the format from the first one. You can always duplicate your label, and enter your new query and check the results.
Comments:
-
But WHY? That's what I want to know. I see it seems to be matching KUID between tables.
I also don't get the where clause. "1 in (select 1 from ... union" Definitely creates a lot of parentheses. It looks like it's just joining the two things. Why couldn't you just use an OR statement?
My query pulls the same machines as the first one. - lmland 10 years ago-
I agree it pulls the same info. I've had trouble adding the "LIMIT" command in the labels. It does not return any results at all when adding that. I'll have to defer to someone with Kace on that one. Now I'm curious also. - dugullett 10 years ago
-
I always leave the first part of the query and simplify the WHERE clause. I always assumed that it needed the LAST_SYNC_TIME and LAST_SYNC_SECONDS columns so I don't mess with them. - chucksteel 10 years ago
-
Yeah, I do the same for ticket rules, just leaving a big cluster of info that "im going to assume is good to have but changes nothing really" since it's most "approver" based type stuff.
anyywwaaayyyysss that WHERE statement the first format comes with seems to grab more than just IP as it's calling and unioning MACHINE_NIC as well, which could be your little missing piece. - Wildwolfay 10 years ago -
@wildofway So right now I get the same machines with a simplified WHERE statement. Do you know what could possibly be missed without the longer version including MACHINE_NIC? - lmland 10 years ago
-
MACHINE_NICS will get all IPs inventoried by the machine. MACHINE.IP only lists only one. - dugullett 10 years ago
-
That totally explains why my IP search sometimes pulls up machines incorrectly. I only want the most recent IP address so that the label is accurate based on where machines are CURRENTLY located. So MACHINE_NICS might give me an old IP if a machine moved between locations, right?
So, even though it was nice of the sql wizards to think of including this, I actually DON'T want to include that union statement. Brilliant! - lmland 10 years ago -
It will give you the current IPs of all NICs. In my case if machines either have a wired IP of 192.168.X.X or and wireless IP of 129.112.X.X then they are part of X building. I would guess they would both work, depending on what you were looking for. - dugullett 10 years ago
-
Got it! I will have to check with our networking guys to see if using both matters or not. They are redoing a lot of our wireless network and maybe this would make a difference and/or be more accurate. Thanks! - lmland 10 years ago
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID
FROM MACHINE
WHERE ((MACHINE.IP like '10.12.%') OR (MACHINE.IP like '10.212.%')) - lmland 10 years ago