/build/static/layout/Breadcrumb_cap_w.png

How to get alerts on Inventory changes and the related report for the K1000 5.4

The folowing example will show how to create an alert on Inventory change for the memory (Total RAM size) and receive an email when the data gets into the database.

How to do it:

Create a “dummy” alert via the "Computer" Tab, click on "create Notification", create a "dummy" search, add a Title and an email recipient, click on "Create Notification".

The screen will change and you will be in the "Reporting" - "Email Alert" section where you can edit the new notification. You could have gone directly here as well and create a new notification form here.

Now replace the sql code with the code below!

******

select *, C.CLIENT_CONNECTED, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,

                       UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_SECONDS

     from ORG1.MACHINE PC

             LEFT JOIN ASSET_HISTORY AH ON AH.NAME=PC.NAME

             LEFT JOIN KBSYS.KUID_ORGANIZATION O ON O.KUID=PC.KUID

        LEFT JOIN KBSYS.SMMP_CONNECTION C ON C.KUID = PC.KUID

                  AND O.ORGANIZATION_ID = 1

                       where AH.CHANGE_TYPE='Modification'

                 AND AH.FIELD_NAME='RAM_TOTAL'

                 AND AH.TIME > DATE_SUB(now(), INTERVAL 1 DAY)

 ******

This query will look for any change on RAM in the last 24 hours (can be changed into 45 MINUTE, 1 HOUR, etc…). Look in the table « ASSET_HISTORY » for other field_name you want to check against if you want to get alert on other inventory fields or Modification/Removal/Creation types.

Activate the alert and choose the frequency.

                       

With this code you will get an email alert when the RAM size has changed on PC’s in the last 24 hours or the interval you have choosen.

>>> A Report <<<

Create a new SQL report and paste the SQL code below into the SQL Statement field. As before, you can change the INTERVAL if you need.

In the "Breack on Columns" section you can add "NAME", so that you will get all the changes for one PC listed together.

******

select PC.NAME, PC.USER_LOGGED AS "Actual User", CONCAT_WS(" has changed to ",AH.VALUE1, AH.VALUE2) AS "Description of Changes", AH.TIME

     from ORG1.MACHINE PC

             LEFT JOIN ASSET_HISTORY AH ON AH.NAME=PC.NAME

                       where AH.CHANGE_TYPE='Modification'

                 AND AH.FIELD_NAME='RAM_TOTAL'

                 AND AH.TIME > DATE_SUB(now(), INTERVAL 1 DAY)

****** 

You can create a similar report through the new Tab "History" under "Settings". Go to "Asset History Listing", advanced search, create your query with the wizard, test and the select to create a report from the "Choose Action" button. You are then able to add more fields in the select statement if you want.


Comments

This post is locked

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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