/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1
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.

Answered 04/25/2018 by: chucksteel
Red Belt

All Answers

0
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
Answered 04/25/2018 by: c-team@jeron.je
Senior White Belt

Share