Kace Report to show assigned assets and associated assets under each user
Is there any way to generate a report in Kace with the users info, then the assigned assets and associated assets underneath with the values from their asset types.
For example, under my user in Kace I have a primary machine under assigned computer assets and a mobile phone under associated assets.
Can I generate a report like the following:
Curtis Lewis PrimaryEmail Manager Location
ServiceTag Vendor Model
MobileNo Make Model
I can generate a report showing the first 2 lines, but the mobile number just shows up in the same row as the computer asset.
Thanks,
Curtis
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
JasonEgg
7 years ago
This will repeat the user's name, rather than having a nice table for each user, but it works:
SELECT U.FULL_NAME,
A.ID AS ASSET_ID,
A.NAME,
A.ASSET_DATA_ID,
A.MODIFIED,
A.CREATED,
A.LOCATION_ID,
M.*
FROM USER U
JOIN ASSET A ON U.ID = A.OWNER_ID
LEFT JOIN MACHINE M ON M.BIOS_SERIAL_NUMBER = A.MAPPED_ID
ORDER BY U.FULL_NAME ASC
Comments:
-
Thanks for looking at this JasonEgg, appriciate it.
However when I setup a SQL report using the above and attempt to run it, it throws an error:
Error Running Report
Internal error (3). Please contact KACE Support.
I'm guessing I may be doing something wrong or perhaps referencing a table / field that doesn't exsit?
Curtis - Transfixed 7 years ago-
How big is your asset table? If it's huge then maybe that's where the error is coming from? I have 5,200 rows in the asset table and the query took about 20 seconds when I ran it through the web UI. - JasonEgg 7 years ago
-
FYI I would suggest using a program like Toad or MySQL workbench to test queries. - JasonEgg 7 years ago
