Currently Powered On Detail by Name
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
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
Answers (2)
Please log in to answer
Posted by:
RichB
14 years ago
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.
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.
Posted by:
RichB
14 years ago
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.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.