K1000 Reporting: Custom Machine Report (4277089)

This is a custom report that will pull some of the more often requested computer information. 

NOTE: This script is provided by KACE support without gaurantee or warranty as to usage and portability between versions.
Custom Scripting is not supported under Support Guidelines and is presented for demonstration and instructional purpose.

select DISTINCT M.NAAME as NODE,
       DATE(M.CREATED) as ADDED,
       CONCAT('[', M.MAC, ']') as MAC,
       M.IP,
       M.ASSET_TAG,
       M.LAST_INVENTORY,
       CONCAT(CAST(TIMESTAMPDIFF(day, M.LAST_SYNC, NOW()) as DECIMAL), ' days, ', CAST(TIMESTAMPDIFF(HOUR, M.LAST_SYNC, NOW()) mod 24 as DECIMAL), ' hrs ', CAST(TIMESTAMPDIFF(minute, M.LAST_SYNC, NOW()) mod 60 as DECIMAL), ' mins.') as LAST_SYNC,
       DATE(M.LAST_REBOOT) as LAST_REBOOT,

  (select CONCAT(NIC, ' dhcp?:', DHCP_ENABLED)
   from MACHINE_NICS
   where (MACHINE_NICS.ID) in
       (select MACHINE.ID
        from MACHINE
        where MACHINE_NICS.IP = M.IP
          and MACHINE_NICS.MAC = M.MAC)
     and LEFT(NIC, 7) != 'unknown'
   order by NIC desc limit 1) as NIC_INFO,
       CONCAT(M.CS_MANUFACTURER, ' ', M.CS_MODEL) as MODEL,
       M.OS_NAME as OS,
       IF(ISNULL(DATE(M.OS_INSTALLED_DATE)), 'UNKNOWN', DATE(M.OS_INSTALLED_DATE)) as OS_INSTALL_DATE,
       CONCAT('Using ', FORMAT(M.RAM_USED * 100 / M.RAM_TOTAL, 0), '% of ', M.RAM_TOTAL, ' upgardeable to ', M.RAM_MAX, '') as MEM_USAGE,
       CONCAT(M.BIOS_MANUFACTURER, ' ', M.BIOS_NAME, ' -+- VER:', M.BIOS_VERSION, ' -+- DATE:', DATE(M.BIOS_RELEASE_DATE)) as BIOS_INFO,
       M.PROCESSORS,
       M.CS_DOMAIN,
       M.LAST_USER,
       M.VIRTUAL
from MACHINE M,
     MACHINE_DISKS D
where M.ID = D.ID

This report contains an aggregate of information from each machine to give an in depth picture.

Information fileds include.
Node-Name, Date Added, MAC, IP, Asset Tag, Last Inventory, Last Sync, Last Reboot. NIC Info, Model, OS, OS Install, Mem Usage and upgrade info, BIOS Info, Processors, Domain, User, Virtual or Physical.