Custom report help needed
Good morning everyone,
I have just been tasked with an urgent management request, to produce a report from our K1200 which has to query specific fields.
We are running v6.4.120822 on our appliance. I have never created a custom SQL report before, and I can't get this information out of a wizard based report.
The data I need to produce for my ORG is simply the following;
Computer Name, System Manufacturer, System Model, Total RAM Installed, HDD Size, Last Kace Sync, Shipping Date.
Looking through other peoples requests on here, I have cobbled the following together, which is really close, I just need to replace the "Created" column with the "Date shipped" column.
This is the code I am using, can anyone help me replace "Created" with "Date Shipped" please.
SELECT DISTINCT(MACHINE.NAME) AS "Computer Name",
CS_MANUFACTURER AS "Make",
CS_MODEL AS "Model",
round(SUM(MACHINE_DISKS.DISK_SIZE),2) AS "HDD Size",
RAM_TOTAL AS "RAM Installed",
LAST_SYNC AS "Last KACE Sync",
MACHINE.CREATED FROM MACHINE LEFT JOIN MACHINE_DISKS ON (MACHINE_DISKS.ID = MACHINE.ID)
GROUP BY MACHINE.ID ORDER BY CS_MANUFACTURER
Thanks in advance.
Karl
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
6 years ago
Top Answer
The ship date is stored in the DELL_ASSET table:
SELECT DISTINCT(MACHINE.NAME) AS "Computer Name",
CS_MANUFACTURER AS "Make",
CS_MODEL AS "Model",
round(SUM(MACHINE_DISKS.DISK_SIZE),2) AS "HDD Size",
RAM_TOTAL AS "RAM Installed",
LAST_SYNC AS "Last KACE Sync",
DELL_ASSET.SHIP_DATE as "Ship Date"
FROM MACHINE
LEFT JOIN MACHINE_DISKS ON (MACHINE_DISKS.ID = MACHINE.ID)
JOIN DELL_ASSET on DELL_ASSET.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER
GROUP BY MACHINE.ID
ORDER BY CS_MANUFACTURER
As the table name implies, it is only relevant for Dell hardware. If you have systems from another manufacturer, you will need to find another way to get their shipping information into the appliance.
Posted by:
c-team@jeron.je
6 years ago
Hi Chuck,
Thank you so much for this.
I've imported your code, and its perfect, you have saved my day.
I just need to import the script into 35 ORG's and run the reports within the next hour, but I should have one happy manager. You really don't know how happy this has made me.
Many, many thanks,
Karl