Systems Management

Howdy,

Recently I found myself needing to do a good bit of cleanup in the asset management section of the SMA. I found myself wanting to find a solution to automatically update the asset fields from "In Stock" to "Active" once a computer has been online. I ended up making a custom ticket rule to do this. Figured I would share the SQL here since this site was one of the sources I used to make it.

This rule updates the devices asset status to active (493) if the agent has been online in the past 24 hours.

SELECT SQL

SELECT 

    ASSET.ID AS ASSET_ID,

    ASSET_STATUS.NAME AS ASSET_STATUS,

    SC.CONNECT_TIME

FROM

    MACHINE

        LEFT JOIN

    ASSET ON ASSET.MAPPED_ID = MACHINE.ID

        AND ASSET.ASSET_TYPE_ID = 5

        LEFT JOIN

    ASSET_STATUS ON ASSET_STATUS.ID = ASSET.ASSET_STATUS_ID

        LEFT JOIN

    KBSYS.KUID_ORGANIZATION KUID_ORG ON KUID_ORG.KUID = MACHINE.KUID

        LEFT JOIN

    KBSYS.SMMP_CONNECTION SC ON MACHINE.KUID = SC.KUID

        AND SUBSTRING(SCHEMA(), 4) = KUID_ORG.ORGANIZATION_ID

WHERE

    (((TIMESTAMP(SC.CONNECT_TIME) <= NOW()

        AND TIMESTAMP(SC.CONNECT_TIME) > DATE_SUB(NOW(), INTERVAL 24 HOUR))))

ORDER BY ASSET_ID

UPDATE SQL

UPDATE ASSET

LEFT JOIN MACHINE ON ASSET.MAPPED_ID = MACHINE.ID

    AND ASSET.ASSET_TYPE_ID = 5

LEFT JOIN KBSYS.KUID_ORGANIZATION KUID_ORG ON KUID_ORG.KUID = MACHINE.KUID

LEFT JOIN KBSYS.SMMP_CONNECTION SC ON MACHINE.KUID = SC.KUID

    AND SUBSTRING(SCHEMA(), 4) = KUID_ORG.ORGANIZATION_ID

SET ASSET.ASSET_STATUS_ID = '493'

WHERE TIMESTAMP(SC.CONNECT_TIME) <= NOW()

    AND TIMESTAMP(SC.CONNECT_TIME) > DATE_SUB(NOW(), INTERVAL 24 HOUR);