/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:
Cancel
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Answer Chosen by the Author


Answers

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

Please log in to comment
Answer this question or Comment on this question for clarity

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

Please log in to comment
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share