/build/static/layout/Breadcrumb_cap_w.png

monitor change in hardware inventory

hi guys,
does anyone have the sql code this?
i want to monitor every pc at check-in time
if any change has occurred in its hardware inventory
in comparison to the previous inventory taken!
this should be similar to the asset change history report!
thanks guys

0 Comments   [ + ] Show comments

Answers (9)

Posted by: airwolf 14 years ago
Red Belt
0
I could create a report for you that shows asset history for a given period of time, but there is no way to schedule a report to run on a specific event (other than time). There is no way to tell the KBOX to email you when a change has occured in inventory.

You could probably set something like this up in Excel using ODBC. You'd still have to open the spreadsheet, but you could set it up to hide rows that have not changed (thus giving you a report of all changes in inventory since the last time you opened the spreadsheet).
Posted by: airwolf 14 years ago
Red Belt
0
I've come up with a report that shows all inventory changes for the past 7 days. It can easily be modified for different periods of time:


SELECT A.NAME AS 'Machine Name', AH.TIME AS 'Time Changed', CAST(AH.DESCRIPTION AS CHAR(8000)) AS 'Description of Change' FROM ASSET_HISTORY AH
JOIN ASSET A ON (A.ID = AH.ASSET_ID)
WHERE DATEDIFF(NOW(), AH.TIME) < 7 AND A.ASSET_TYPE_ID = 5
ORDER BY A.NAME


Again, if you're looking for something more dynamic, you'll have to use third party software and an ODBC connection to the KBOX database.
Posted by: afzal 14 years ago
Fourth Degree Green Belt
0
I have created such report just follow the procedure.
Add “Asset Change History” to Helpdesk module to send out email on regular interval.
Select any helpdesk queue. Click on “ticket rules: [customize]” at the bottom of page.
Put in some dummy values to define the ticket rule, as the proper values will be edited later. Select “next”
Again, key in some dummy values for values to change. Select “done”.
Check the “Send query results to someone” check box.
Uncheck “Run an update query, using the results from the one above” box.
Replace the “Select Query” box with the below 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 like 'Found software item%'
or ASSET_HISTORY.DESCRIPTION like '%remove%'
or ASSET_HISTORY.DESCRIPTION like '%change%')
order by TIME DESC

The helpdesk ticket rule is now replaced with a new rule to send out mail for asset history change. The interval of mail sent and interval of asset history change can also be modified according requirement.
Posted by: afzal 14 years ago
Fourth Degree Green Belt
0
You may change the INTERVAL value to 1 DAY in the above SQL
Posted by: airwolf 14 years ago
Red Belt
0
Just a few pointers, afzal:

Put in some dummy values to define the ticket rule, as the proper values will be edited later. Select “next”
Again, key in some dummy values for values to change. Select “done”.


This isn't required - you can just leave the fields blank and click 'Next'.

You may change the INTERVAL value to 1 DAY in the above SQL

You can change the interval to any valid MySQL INTERVAL.
Posted by: afzal 14 years ago
Fourth Degree Green Belt
0
You may change the INTERVAL value to 1 DAY in the above SQL

You can change the interval to any valid MySQL INTERVAL.


Thank you, i actually mean that.
Posted by: Jack_Robins 14 years ago
Yellow Belt
0
I use Network Inventory Advisor (http://www.clearapps.com/) for such a purpose.
It monitors each pc in the network and generates detailed report about hardware and software installed in it.
It takes only 10-15 minutes to organize pc network inventory and you will always have the comparison to the previous inventory taken.
Posted by: GillySpy 14 years ago
7th Degree Black Belt
0
from Airwolf: there is no way to schedule a report to run on a specific [inventory] event
This is correct, however, since machines do not necessarily check in at the moment that something changes either it is not really necessary to have that granularity. You can schedule something to look for changes as often as every 15 minutes and this should be sufficient time to identify a change. On average this would identify a change within 7.5 minutes

from shtamsec: [when] any change has occurred in its hardware inventory
The original request would like to focus on hardware changes. The asset history will reveal changes related to hardware and software inventory items. If you want to filter the list that would be difficult. If you are okay with only returning the summaries that include hardware changes and don't mind those results showing sofware changes as well (again, as long as there is at least one hardware change), then you would need to identify the text patterns for a hardware change. With a little bit of elbow grease this would be possible. Reviewing the asset history will reveal changes in the past which you can use to identify the patterns you want E.g. '%Video Controllers%'

If you have the helpdesk module then setting it up as a ticket rule is good because it will only return results when a change is made. If you use a report or email alert then a query of 0 results will still send an email. There is an enhancement for email alerts to behave the same way.

In your query you will only need to examine syncs made within the last 15 minutes since your query runs every 15 minutes. Also by looking at a narrow time range we improve the efficiency of the query.
SELECT MACHINE.NAME AS 'Machine Name',
MACHINE.IP,
A.NAME AS 'Asset Name',
AH.TIME AS 'Time Changed',
MACHINE.LAST_SYNC,
AH.DESCRIPTION as Changes,
CONCAT('http://yourkbox/adminui/machine.php?ID',MACHINE.ID) AS "Inventory URL"
FROM ASSET_HISTORY AH
JOIN ASSET A ON A.ID = AH.ASSET_ID and ASSET_TYPE_ID=5
JOIN MACHINE
WHERE
AH.TIME > DATE_SUB(NOW(), INTERVAL 16 MINUTE)
and MACHINE.LAST_SYNC > DATE_SUB(NOW(), INTERVAL 16 MINUTE)
and AH.DESCRIPTION RLIKE '(RAM Total|Manufacturer|Model|Motherboard Primary Bus|Motherboard Secondary Bus|Processors|CPU Chip Count|CPU Core Count|DVD Drives|Sound Devices|Video Controllers|BIOS Name|BIOS Version|BIOS Manufacturer|BIOS Description|BIOS Serial Number) Changed'
ORDER BY A.NAME
Posted by: wsteo 13 years ago
Senior Yellow Belt
0
http://itninja.com/question/silent-uninstall-oracle-8-cilent0547&mpage=1&key=𑎓

I have created such a SQL report, for whatever hardware changes that I can find.
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

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

Sign up! or login

View more:

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