/build/static/layout/Breadcrumb_cap_w.png

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?


1 Comment   [ + ] Show comment
  • Tried creating a label based on IP with the new wizard in 5.5 and got this query which is vastly simplified:

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

Answers (1)

Answer Summary:
Posted by: dugullett 11 years ago
Red Belt
0

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 11 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 11 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 11 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 11 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 11 years ago
      • MACHINE_NICS will get all IPs inventoried by the machine. MACHINE.IP only lists only one. - dugullett 11 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 11 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 11 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 11 years ago
 
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