Systems Management

In 5.4 ASSET_HISTORY table is restructured and can provide this data much easier, faster and at a lower cost then before.  Any query (report, etc) should also be easier to understand and maintain if you need to change it in the future.

Because you're talking about a group of software, I would like to create a label to track them all, but because SOFTWARE and ASSET_HISTORY are not strongly related enough I might miss some entries.  If you did use a label and then you could modify the membership of your label and leave your report alone. 

Here is an example in 5.4 where you use the following to schedule a reporty that runs once a day. It will only return results from the last 24 hours :

select 
	M.ID,
	M.NAME "Machine",
	H.VALUE1 "Software Detected",
	H.VALUE2 "Version Detected"

 from ASSET_HISTORY H
	join ASSET A on A.ID=H.ASSET_ID
	join MACHINE M on M.ID=A.MAPPED_ID

where 
	H.TYPE_NAME = 'Computer'
 and H.FIELD_NAME like 'SOFTWARE'
 and H.CHANGE_TYPE = 'Addition'
 and H.VALUE1 rlike 'logmein|vnc'
 and H.TIME > date_sub(now(), interval 1 day) -- only results from last 24 hours
 group by A.ID, H.VALUE1,H.VALUE2
order by M.NAME

If having the PC name, software title, logged in user and date of install would suffice, you could use this and run it as a scheduled report  (I'm making no assumptions about having anything in assets).  First, set it up as a SQL Report, then go to Reporting > Schedule Reports and set it to run as often as you'd like (make sure you check the box under Email Notifications to "Only send when results are present".

The two drawbacks to this approach as I can see them are:

1) You'll keep getting the same report via email until that software has been removed.  So if you schedule it to run every hour, you'll potentially get the same report every hour (the minimum under Scheduling, at least on my K1000 - unless you can use fractional values like 0.25, which I've never tried).

2) It's possible to list who was on the machine at the time of the last machine inventory, but not necessarily when the software is installed.  These may be two different things, but you could probably correlate the two based on when the machine was inventoried and the time the machine first appeared in the report with the software listed.

SELECT M.NAME, M.LAST_INVENTORY, M.USER,
S.DISPLAY_NAME, S.DISPLAY_VERSION,
S.INSTALL_DATE, S.CATEGORY
FROM MACHINE M
LEFT JOIN MACHINE_SOFTWARE_JT MS ON (MS.MACHINE_ID = M.ID)
LEFT JOIN SOFTWARE S ON (S.ID = MS.SOFTWARE_ID)
WHERE DISPLAY_NAME rlike 'logmein'
ORDER BY M.NAME

Hope that helps!

John

You can create an alert but the shortest time interval for alerts is 15 minutes and that sends an email regardless of any information to actually report. inkpad! from KACE had an article that   had the following so if a title appeared in inventory on your K1000 for the first time in the last X days you could run a report or create an alert. 

select *, UNIXTIMESTAMP(now()) - UNIXTIMESTAMP(LASTSYNC) as LASTSYNCTIME,
UNIXTIMESTAMP(MACHINE.LASTSYNC) as LASTSYNC_SECONDS
from ORG1.MACHINE
where (DATE(NOW() - INTERVAL 90 DAY) < DATE(MACHINE.CREATED))

if you want to look for specific, you can create a report that does that as well.

SELECT CONCAT(S.DISPLAY_NAME, ' ', S.DISPLAY_VERSION) AS 'Software',

A.NAME as 'Computer', AH.TIME as 'Installed'

FROM ASSET_HISTORY AH, ASSET A, ASSET_TYPE AST, SOFTWARE S

WHERE AST.ASSET_CATEGORY = "Computer"

and A.ID = AH.ASSET_ID

and AH.DESCRIPTION LIKE CONCAT('%%Found software item ', S.DISPLAY_NAME,

' ', S.DISPLAY_VERSION, '%%')

and DATE(AH.TIME) > DATE(DATE_SUB(NOW(), INTERVAL 2 DAY))

ORDER BY Installed, Computer