This came from the Konference Power Management reports made available here:
http://www.kace.com/support/customer/faq/index.php?action=artikel&cat=5&id=983&artlang=en

This is the modified SQL with corrected time calculation of Powered On and also is sorted by name instead of by time:

select M.NAME, S.CONNECT_TIME as START_TIME,
TIME_FORMAT(sec_to_time(UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(S.CONNECT_TIME)), '%H:%i:%S') as HOURS_POWERED_ON
from MACHINE M
join KBSYS.SMMP_CONNECTION S on S.KUID = M.KUID
where S.CLIENT_CONNECTED = 1
order by M.NAME
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
Here's the same report narrowed down to a computer name that begins with "NameBegins:"

select M.NAME, S.CONNECT_TIME as START_TIME,
TIME_FORMAT(sec_to_time(UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(S.CONNECT_TIME)), '%H:%i:%S') as HOURS_POWERED_ON
from MACHINE M
join KBSYS.SMMP_CONNECTION S on S.KUID = M.KUID
where (S.CLIENT_CONNECTED = 1 and M.NAME like 'NameBegins%')
order by M.NAME


We run these reports for various sites at midnight and send the results to the technicians responsible. In the future I would prefer to use an email format similar to what is sent with the "Email Alerts" where the report is only sent if there is matching criteria and the body of the email has links back to the computer records.
Answered 01/14/2010 by: RichB
Third Degree Green Belt

Please log in to comment
0
When that SQL is used in the Email Alert Notification Query, it arrives with the computer records as links but the other columns are not adjusted to the new criteria. A table with only computer names is sent with blank columns for System Description, MAC Address and IP Address.
Answered 01/14/2010 by: RichB
Third Degree Green Belt

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