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?

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • 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.%'))
Please log in to comment

Answers

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.

Answered 10/10/2013 by: dugullett
Red Belt

  • 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.
    • 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.
    • 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.
      • 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.
      • @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?
      • MACHINE_NICS will get all IPs inventoried by the machine. MACHINE.IP only lists only one.
      • 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!
      • 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.
      • 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!
Please log in to comment
This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity

Share