Can anyone whos good with SQL help with a custom report please?
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.
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
dugullett
11 years ago
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
Comments:
-
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 - dugullett 11 years ago-
If you are needing one specific label name add
WHERE L.NAME = 'Location/Jobsite' - dugullett 11 years ago
-
You Rock Dugullett!!! Thanks so much my friend! - spassler 11 years ago
-
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? - spassler 11 years ago
-
I use USER_LOGGED.
SELECT M.NAME AS Computer_Name, D.SHIP_DATE, USER_LOGGED, - dugullett 11 years ago
-
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! - spassler 11 years ago-
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 - dugullett 11 years ago
-
Nice work, this could come in handy! - GeekSoldier 11 years ago
-
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. - spassler 11 years ago
-
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. - dugullett 11 years ago-
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. - dugullett 11 years ago
-
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. - GeekSoldier 11 years ago
