So I have a report I found on here that tells me the age of a computer by telling me the ship date. I have all my machines tagged with a location (about 10 Labels) in a Group Label called "Location / Jobsite" Is there a way to have my warranty report group the resaults together by the Labels inside the group label of "Location / Jobsite"?

 

Current report:

SELECT M.NAME AS Computer_Name, D.SHIP_DATE
FROM MACHINE M
JOIN DELL_ASSET D ON D.SERVICE_TAG = M.BIOS_SERIAL_NUMBER
ORDER BY NAME

 

 

Any info would help, thanks all.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

3

Try this.

SELECT M.NAME AS Computer_Name, D.SHIP_DATE, L.NAME

FROM MACHINE M

JOIN DELL_ASSET D ON D.SERVICE_TAG = M.BIOS_SERIAL_NUMBER

LEFT JOIN MACHINE_LABEL_JT MLJT ON M.ID=MLJT.MACHINE_ID

LEFT JOIN LABEL L ON MLJT.LABEL_ID=L.ID

ORDER BY L.NAME
Answered 04/02/2013 by: dugullett
Red Belt

  • Looking at this again you might want to try this. Since some of your machines will be in more than one label.

    SELECT M.NAME AS Computer_Name, D.SHIP_DATE,
    GROUP_CONCAT(DISTINCT L.NAME ORDER BY L.NAME SEPARATOR '\n') AS LABEL
    FROM MACHINE M
    JOIN DELL_ASSET D ON D.SERVICE_TAG = M.BIOS_SERIAL_NUMBER
    LEFT JOIN MACHINE_LABEL_JT MLJT ON M.ID=MLJT.MACHINE_ID
    LEFT JOIN LABEL L ON MLJT.LABEL_ID=L.ID
    GROUP BY M.NAME
    ORDER BY L.NAME
    • If you are needing one specific label name add

      WHERE L.NAME = 'Location/Jobsite'
  • You Rock Dugullett!!! Thanks so much my friend!
  • Another quick question for you dugullett, if I wanted to add last user (I assume its the "User Name:" under the summery section under inventory tab) would I just add it behind the ship date? question is what is it called? just USER? or would that only be service desk users?
    • I use USER_LOGGED.

      SELECT M.NAME AS Computer_Name, D.SHIP_DATE, USER_LOGGED,
  • My "K1000 Management Appliance database tables" must be out dated or something I don't see USER_LOGGED in the list where are you getting these from? I just hate to keep asking if I can find it on my own... or are these SQL commands, cause as stated above I know nothing about SQL sadly :(


    And thanks by the way for your help really appreciate it!
    • These are SQL. I use USER_LOGGED because it includes the domain as well. You could also use USER, USER_NAME, or USER_FULLNAME. All get almost the same results.

      SELECT M.NAME AS Computer_Name, D.SHIP_DATE, USER_LOGGED
      GROUP_CONCAT(DISTINCT L.NAME ORDER BY L.NAME SEPARATOR '\n') AS LABEL
      FROM MACHINE M
      JOIN DELL_ASSET D ON D.SERVICE_TAG = M.BIOS_SERIAL_NUMBER
      LEFT JOIN MACHINE_LABEL_JT MLJT ON M.ID=MLJT.MACHINE_ID
      LEFT JOIN LABEL L ON MLJT.LABEL_ID=L.ID
      GROUP BY M.NAME
      ORDER BY L.NAME
  • Nice work, this could come in handy!
  • Ok, I see, Yeah I just added Service_tag on the end of mine just to see and it gives me the service tag as well so I guess ill play with it if I end up wanting to add anything else. Thanks for all your help Dugullett.
    • Download the workbench at http://www.mysql.com/downloads/workbench/ and see what all you get. There are a ton of fields that can be included.

      In this query we have already joined the machine, dell_asset, and label tables. There are additional fields that can be included in this report if needed i.e. IP, MAC, Model, OS.

      An additional step that could come in handy is joining the DELL_WARRANTY table since it looks like it would be beneficial to you since you're wanting to know ship dates.

      SELECT M.NAME AS Computer_Name, D.SHIP_DATE, DW.END_DATE AS 'Warranty End Date',
      GROUP_CONCAT(DISTINCT L.NAME ORDER BY L.NAME SEPARATOR '\n') AS LABEL
      FROM MACHINE M
      JOIN DELL_ASSET D ON D.SERVICE_TAG = M.BIOS_SERIAL_NUMBER
      LEFT JOIN MACHINE_LABEL_JT MLJT ON M.ID=MLJT.MACHINE_ID
      LEFT JOIN LABEL L ON MLJT.LABEL_ID=L.ID
      LEFT JOIN DELL_WARRANTY DW ON M.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
      GROUP BY M.NAME
      ORDER BY L.NAME

      It's worth taking the time to learn. It's a good tool to have when your boss wants to know how many machines have what.
      • Jverbosk wrote a great tutorial that helped me get started. http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example

        Just so you know the majority of KBSYS tables that existed in 5.3 have now moved in 5.4 SP1. I'm sure he'll update it here soon. He's good about that.
    • A caveat to mysql workbench. All tables except for 1 are read only. Use caution when performing queries against the helpdesk module. KACE support had some horror stories about people bombing their Help Desk by writing/deleting data in those tables. Everything else is fair game for pretty much anything.
Please log in to comment
Answer this question or Comment on this question for clarity

Share