/build/static/layout/Breadcrumb_cap_w.png

K1000 (Version: 6.4.120822) - Apple\Mac Inventory Report

Hello,

I would like to create a custom report for our Apple\Mac inventory (which include the KACE client) that will include the following fields but I'm unsure of how to do so. The information for each of the fields below will be entered manually. We have a relatively small Apple\Mac environment. 

1. Employee Name
2. Model
3. Serial Number
4. Purchase Date
5. Warranty Expiration Date
6. Filevault Key
7. Firmware Password

Thanks in advance!
Asked 7 years ago 1403 views
6 Comments   [ + ] Show comments
  • Where do you intend to store purchase date, warranty expiration date, filevault key and firmware password? The easiest location would be to store them in the computer asset by adding those as custom fields. If that is the case then you can build this report using the reporting wizard. - chucksteel 7 years ago
  • Hi Chuck - thanks for getting back to me. Yes, I am planning to store\create these fields under a new asset 'Apple Inventory' but when I generate the report, the respective headings do not appear as I would like. For instance, the Name field is called 'Asset Name'. If possible, I'm more than willing to continue this conversation via email so I can provide you an example of my report via an attachment but would prefer to keep my email address hidden so its remains private from others. Let me know your thoughts and thanks again! - AEM 7 years ago
    • I would recommend adding the fields to the current computer asset type, otherwise you will have to do more custom reporting in order to link the new asset type to the devices in inventory.

      Regardless, if you just want to change the column headers:
      Create the report using the wizard
      Save the report
      Open the report to edit it
      Click the Edit SQL button
      Each column of the report will appear in a comma delimited list in the first part of the statement after the word Select. Find each column that you want to rename and add an alias using the AS keyword. For your example, there will probably be something like:
      ASSET.NAME,
      that you want to modify make to read:
      ASSET.NAME as "Asset Name",

      Add the aliases to the relevant columns and save the report. You will get a notice that the report will not longer be editable using the wizard. If that makes you nervous then make a copy of the report first and make these changes to the copy. - chucksteel 7 years ago
  • Hi Chuck - below is my SQL report and this was created by using the Asset Type I created for my Apple Inventory.

    I would like the following headings\column names changed:

    1. Asset Name to Employee Name
    2. Field 10036 to Model
    3. Field 10037 to Serial #
    4. Field 10040 to Purchase Date
    5. Field 10041 to Warranty Expiration Date

    I would like to also include columns for the following (I have them included in the Asset Type I created but for some reason they're not included when I generate a report using the SQL table below):

    6. Filevault Key
    7. Firmware Password

    I would like to permanently remove the following headers\columns from the report:

    1. Asset Data Id
    2. Mapped Id
    3. Machine Name
    4. Field 10038
    5. Field 10039

    SELECT ASSET.ID, ASSET.NAME AS ASSET_NAME, COALESCE(AC.NAME, 'None') AS ASSET_SUBTYPE,
    ASSET.ASSET_DATA_ID, ASSET.MAPPED_ID, M.NAME AS MACHINE_NAME, AD.FIELD_10036, AD.FIELD_10037, AD.FIELD_10040, AD.FIELD_10041, AD.FIELD_10038, AD.FIELD_10039 FROM ASSET
    LEFT JOIN ASSET_CLASS AC ON AC.ID = ASSET.ASSET_CLASS_ID
    LEFT JOIN USER U ON U.ID = ASSET.OWNER_ID
    LEFT JOIN MACHINE M ON ASSET.MAPPED_ID = M.ID and ASSET.ASSET_TYPE_ID = 5
    LEFT JOIN ASSET_DATA_48 AD ON AD.ID = ASSET.ASSET_DATA_ID where ASSET.ASSET_TYPE_ID = 48 GROUP BY ASSET.ID order by ASSET_NAME

    I wish there was a way to attach any screenshots and my report to give you a better idea of what it looks like on my end.

    Thanks,

    Al - AEM 7 years ago
    • Sorry, didn't see your comments. Renaming a field in MySQL is easy. For each field you add " as newname". You can see that some of the fields have already been renamed or aliased. For instance:
      ASSET.NAME AS ASSET_NAME
      To have this column named Employee Name you would change that to:
      ASSET.NAME AS "Employee Name" - chucksteel 7 years ago
      • When I do that, I receive a SQL error message (A red banner) at the top of the screen. Any suggestions? - AEM 7 years ago
      • Please post your SQL. - chucksteel 7 years ago
  • I wish we could exchange email addresses without posting them for others to see. - AEM 7 years ago
  • Good Morning Chuck - have you had a chance to review the above? - AEM 7 years ago
  • Good Morning Chuck - sorry for the delay but I think I have it figured out. Thanks again for your time and assistance. - AEM 7 years ago

Answers (0)

Be the first to answer this question

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ