Hi,

I am really new to SQL queries and am trying to get a list of all my systems that will be three years old at December 31, 2013. I need to group it by the smart labels I created under the smart label group "Locations". Would anyone be able to offer some guidance? Thanks

SELECT M.NAME AS COMPUTER_NAME,DA.SERVICE_TAG, M.CS_MODEL AS MODEL, M.CHASSIS_TYPE AS CHASSIS_TYPE, DATE_FORMAT(DA.SHIP_DATE,'%Y/%m/%d')AS SHIP_DATE, LABEL.NAME 

FROM LABEL, DELL_ASSET DA

LEFT JOIN MACHINE M ON M.BIOS_SERIAL_NUMBER=DA.SERVICE_TAG

WHERE DA.SHIP_DATE < ('2013-12-31' - INTERVAL 3 YEAR)

ORDER BY CHASSIS_TYPE
 
Answer Summary:
Since you are selecting from multiple tables you need to create a relationship between the two. When I tried that by adding a statement to the where clause the query was very slow, so instead I used a join to the DELL_ASSET table: # Select Columns to gather information from SELECT MACHINE.NAME AS SYSTEM_NAME, BIOS_SERIAL_NUMBER, DA.SHIP_DATE, GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR ',') AS SUBNET # Gathering information from the below Tables FROM MACHINE LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden') LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 130) LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID LEFT JOIN USER ON USER.ID = ASSET.OWNER_ID LEFT JOIN DELL_ASSET DA on DA.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER WHERE LABEL_LABEL_JT.LABEL_ID = 130 # 3 years old at date entered below 2013-12-31 AND DA.SHIP_DATE < DATE('2013-12-31' - INTERVAL '3' YEAR) GROUP BY MACHINE.ID # Ordered by Subnet smart labels ORDER BY LABEL.NAME
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

Change to match your label names.

SELECT M.NAME AS COMPUTER_NAME,DA.SERVICE_TAG, M.CS_MODEL AS MODEL, M.CHASSIS_TYPE AS CHASSIS_TYPE, DATE_FORMAT(DA.SHIP_DATE,'%Y/%m/%d')AS SHIP_DATE, LABEL.NAME

FROM LABEL, DELL_ASSET DA

LEFT JOIN MACHINE M ON M.BIOS_SERIAL_NUMBER=DA.SERVICE_TAG

WHERE DA.SHIP_DATE < ('2013-12-31' - INTERVAL 3 YEAR)

AND LABEL.NAME RLIKE 'LABEL_1|LABEL_2|LABEL_3'

ORDER BY CHASSIS_TYPE
Answered 08/21/2013 by: dugullett
Red Belt

Please log in to comment
1

Here's a query that returns computers that have a label in my "Departments" group:

 SELECT IP,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR ',') AS DEPARTMENT,
MACHINE.NAME AS SYSTEM_NAME,MACHINE.USER_NAME,USER_LOGGED,USER_FULLNAME
FROM MACHINE  
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)  
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID  AND LABEL.TYPE <> 'hidden') 
LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 258)
LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 
LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID 
LEFT JOIN USER ON USER.ID = ASSET.OWNER_ID 
WHERE LABEL_LABEL_JT.LABEL_ID = 258
GROUP BY MACHINE.ID
ORDER BY SYSTEM_NAME

The important bits are the LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 258)

and WHERE LABEL_LABEL_JT.LABEL_ID = 258

In both cases you'll need to change the 258 to the ID of the label group that you're looking to filter on. You can add your statement for the ship date after the WHERE LABEL_LABEL_JT.LABEL_ID = 258 and before the GROUP BY statement.


Answered 08/21/2013 by: chucksteel
Red Belt

  • That has gotten me started, but my SHIP_DATE is not reporting properly now. Here is what I have now.

    # Select Columns to gather information from
    SELECT MACHINE.NAME AS SYSTEM_NAME, BIOS_SERIAL_NUMBER, DA.SHIP_DATE,
    GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR ',') AS SUBNET
    # Gathering information from the below Tables
    FROM DELL_ASSET AS DA, MACHINE
    LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)
    LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden')
    LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 130)
    LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5
    LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
    LEFT JOIN USER ON USER.ID = ASSET.OWNER_ID
    WHERE LABEL_LABEL_JT.LABEL_ID = 130
    # 3 years old at date entered below 2013-12-31
    AND DA.SHIP_DATE < DATE('2013-12-31' - INTERVAL '3' YEAR)
    GROUP BY MACHINE.ID
    # Ordered by Subnet smart labels
    ORDER BY LABEL.NAME

    I am getting equipment from last year in my query, not sure why.
    • Since you are selecting from multiple tables you need to create a relationship between the two. When I tried that by adding a statement to the where clause the query was very slow, so instead I used a join to the DELL_ASSET table:

      # Select Columns to gather information from
      SELECT MACHINE.NAME AS SYSTEM_NAME, BIOS_SERIAL_NUMBER, DA.SHIP_DATE,
      GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR ',') AS SUBNET
      # Gathering information from the below Tables
      FROM MACHINE
      LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)
      LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden')
      LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 130)
      LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5
      LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
      LEFT JOIN USER ON USER.ID = ASSET.OWNER_ID
      LEFT JOIN DELL_ASSET DA on DA.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER
      WHERE LABEL_LABEL_JT.LABEL_ID = 130
      # 3 years old at date entered below 2013-12-31
      AND DA.SHIP_DATE < DATE('2013-12-31' - INTERVAL '3' YEAR)
      GROUP BY MACHINE.ID
      # Ordered by Subnet smart labels
      ORDER BY LABEL.NAME
  • Wow, great. I get what you did I think. You had to LEFT JOIN it so it relates to a field in the other table so it could pull from the other columns?
    • Yes, the join allows you to pull data from other tables based on a relationship between a table you already know and the other table.
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share