I'm looking to see if the KBOX can do this.  Alert me whenever some user installs something like logmein or something similar.  I want the KBOX the next time it scans to send me an alert that this piece of software was installed on x machine with the domain user who installed it etc.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

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

 

Answered 01/27/2013 by: GillySpy
Seventh Degree Black Belt

Please log in to comment

Answers

2

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

Answered 01/24/2013 by: jverbosk
Red Belt

  • Thanks John! I'll take a look at this.
  • My other thought would be to use a combination of this report and what nshah provided (if you are using the Assets module), as his will give you a definite timestamp of when the software was installed.

    John
    • I will definitely check these out. K2000 roll out is now top priority.
Please log in to comment
0

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,
UNIX
TIMESTAMP(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

Answered 01/23/2013 by: nshah
Red Belt

  • Thanks, I will take a look at this.
  • "that sends an email regardless of any information to actually report" are you certain about that? I'm not going to check right now but this is not how it is spec'd.
Please log in to comment
Answer this question or Comment on this question for clarity