Knowledge Document: Database Query to report the Device Last Boot Time for ZENworks Agent devices

0 Likes

Environment

ZENworks 2020 Update 2 and higher 
 

Situation

PostgreSQL Database queries to report the devices with their last boot time. 
Sometimes reporting option may not show this data for an older Agent or ZCM version and if admin does not have ZENworks Reporting Server (ZRS) to report this information across all the managed devices in the zone. 

Resolution

For ZENworks using PostgreSQL as Database type: 

Query 1:  The Last Boot Time is based on the Device sync or refresh against the Primary Server and applicable to ZENworks 20.3 and higher Agent version (Uses zDeviceEx table)
 

select "Workstation1"."machinename" as "Machine Name", z_bo_timestamp("zdeviceex1"."lastboottime") as "Device Last BootTime", z_bo_timestamp("zDevice1"."lastcontact") 
as "Device Last Contact", "zzenobject1"."path" as "Device Path" ,  "zDevice1"."agentversion" as "Device Agent Version", "zDevice1"."osname" as "Device OS Name", 
"zzenobject1"."subtype" as "Device Type", "zzenobject1"."zuid" as "Device GUID"
from "nc_workstation" "Workstation1"
right outer join "zdevice" "zDevice1" on (("Workstation1"."zenworksagentid" = "zDevice1"."zuid"))
left outer join "zzenobject" "zzenobject1" on (("zzenobject1"."zuid" = "zDevice1"."zuid" ))
left outer join "zdeviceex" "zdeviceex1" on (("zdeviceex1"."zuid" = "zDevice1"."zuid"))
order by "Device Last BootTime" desc  nulls last, "Device Path" desc

 

Query 2: The Last Boot Time is based on the Agent inventory scan results reported to the Primary Server
(Uses NC_Operatingsystem table)

 

select "Workstation1"."machinename" as "Workstation1_MACHINENAME", z_bo_timestamp("OperatingSystem1"."boottime") as "OperatingSystem_BOOTTIME" , 
"Workstation1"."lastsuccessfulscandate" as "Workstation_LastInventoryScanDate" ,  
 "Workstation1"."ipaddress" as "Workstation_IPAddress",
 "Workstation1"."loginname" as "Workstation_UserLoginName",
 "Workstation1"."zenworksagentid"  as "Workstation_AgentGuid" 
from "nc_workstation" "Workstation1"
left outer join "nc_component_sw_hw" "Component1" on (("Component1"."workstationoid" = "Workstation1"."workstationoid" ))
left outer join "nc_operatingsystem" "OperatingSystem1" on (("Component1"."componentoid" = "OperatingSystem1"."componentoid"))
where ("Component1"."isdeleted" is null) and ("OperatingSystem1"."boottime" is not null ) order by "OperatingSystem1"."boottime" desc 


Note: If you are on ZENworks 20.3 or higher version, it is recommended to use Query 1 above since the device sync interval is more frequent than the inventory scan schedule. 

Access article on support portal
    

Labels:

Asset Management
Configuration Management
Knowledge Docs
Patch Management
Comment List
Related
Recommended