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

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

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).
Answered 02/01/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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.
Answered 02/01/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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.
Answered 02/09/2010 by: afzal
Fourth Degree Green Belt

Please log in to comment
0
You may change the INTERVAL value to 1 DAY in the above SQL
Answered 02/09/2010 by: afzal
Fourth Degree Green Belt

Please log in to comment
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.
Answered 02/10/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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.
Answered 02/11/2010 by: afzal
Fourth Degree Green Belt

Please log in to comment
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.
Answered 02/19/2010 by: Jack_Robins
Yellow Belt

Please log in to comment
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
Answered 02/20/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
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.
Answered 02/09/2011 by: wsteo
Senior Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity