/build/static/layout/Breadcrumb_cap_w.png

Asset Change History Report using Kbox 1000

1. Logon to Kbox 1000 as Administrator

2. Select Reports

3. Under "Choose Action" dropdown select “Add New SQL Report”

4. Paste the following SQL statement under the “SQL Select Statement Box”

5. You may change the SQL statement interval of 10 days to some other values

SQL:

select ASSET_HISTORY.TIME, ASSET.NAME, ASSET_HISTORY.DESCRIPTION

from ASSET

left join ASSET_TYPE on ASSET.ASSET_TYPE_ID = ASSET_TYPE.ID

left join ASSET_HISTORY on ASSET.ID = ASSET_HISTORY.ASSET_ID

where

(ASSET_TYPE.ID = 5

and NOW() < DATE_ADD(ASSET_HISTORY.TIME, INTERVAL 10 DAY)

and ASSET_HISTORY.DESCRIPTION not like '%KB%'

and ASSET_HISTORY.DESCRIPTION not like '%reboot%' and ASSET_HISTORY.DESCRIPTION not like '%Warranty%')

and

(ASSET_HISTORY.DESCRIPTION like 'Found software item%'

or ASSET_HISTORY.DESCRIPTION like '%remove%'

or ASSET_HISTORY.DESCRIPTION like '%change%')

order by TIME DESC

Posted 12 years ago 4630 views

Comments

  • This got me about halfway to where I need to be. If I have an additional field "Asset Tag", how would I modify this to have it show on the report as well? Some of our asset types do not have unique names so I need a few more fields on the report to make this usable. - bmartin 12 years ago
  • The db is 5.5 is a bit changed. I created this example query to spot when the computers change Operative System:
    SELECT NAME,TIME,VALUE1,VALUE2 FROM ORG1.ASSET_HISTORY
    WHERE
    TYPE_NAME LIKE '%Computer%' AND
    CHANGE_TYPE LIKE '%Modification%' AND
    FIELD_NAME LIKE 'OS_NAME'
    ORDER BY TIME DESC - StockTrader 10 years ago
  • Updated to fit my needs, but here it is.

    select ASSET_HISTORY.TIME, ASSET.NAME, ASSET_HISTORY.FIELD_NAME, ASSET_HISTORY.VALUE1, ASSET_HISTORY.CHANGE_TYPE

    from ASSET

    left join ASSET_TYPE on ASSET.ASSET_TYPE_ID = ASSET_TYPE.ID

    left join ASSET_HISTORY on ASSET.ID = ASSET_HISTORY.ASSET_ID

    where (ASSET_TYPE.ID = 5
    and ASSET_HISTORY.FIELD_NAME like '%SOFTWARE%'
    #and NOW() < DATE_ADD(ASSET_HISTORY.TIME, INTERVAL 10 DAY)
    )

    and

    (ASSET_HISTORY.CHANGE_TYPE like '%removal%'
    or ASSET_HISTORY.CHANGE_TYPE like '%addition%'
    or ASSET_HISTORY.CHANGE_TYPE like '%modification%'
    or ASSET_HISTORY.CHANGE_TYPE like '%detected%')

    and

    ASSET.NAME like '%<asset_name>%'

    order by ASSET_HISTORY.TIME DESC - dteetz 9 years ago
This post is locked
 
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