Generating Location-Based Device Reports in KACE

Overview

This content provides a step-by-step guide to generating location-based device reports in KACE. The goal is to count devices for each location that have been created within the last week, helping ensure accurate and up-to-date tracking of assets.

Instructions

1. Modify the SQL Query:
   • Objective: Adjust the SQL query to display a count of devices for each location, filtering for those created in the last week.
   • Action: Use the following SQL query to generate the report.

   SELECT
     ASSET_LOCATION.NAME AS LOCATION_NAME,
     COUNT(MACHINE.ID) AS DEVICE_COUNT
   FROM
     MACHINE
     LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID = 5
     LEFT JOIN ASSET ASSET_LOCATION ON ASSET_LOCATION.ID = ASSET.LOCATION_ID
   WHERE
     ASSET_LOCATION.NAME IS NOT NULL
     AND TRIM(ASSET_LOCATION.NAME) <> ''
     AND TRIM(ASSET_LOCATION.NAME) <> 'unassigned'
     AND MACHINE.CREATED > DATE_SUB(NOW(), INTERVAL 1 WEEK) -- Filters devices created in the last week
   GROUP BY
     ASSET_LOCATION.NAME
   ORDER BY
     ASSET_LOCATION.NAME ASC;
               

2. Run the Query in KACE:
   • Navigate to the reporting section in KACE.
   • Paste the SQL query into the query editor.
   • Execute the query to generate the report.
3. Review the Results:
   • Ensure that the report accurately reflects the devices created in the last week for each location.
   • Use this data to verify compliance and address any discrepancies.

Explanation

The SQL query filters devices by their creation date within the last week, grouping the results by location. This allows for a quick overview of recent device additions across different locations.

Tags:

#SQL #KACE #DeviceManagement #Reporting #AssetTracking