The following sample queries demonstrate how to join client health views with status views and inventory views. Client health views will most often use the MachineID column, which is the same as the ResourceID column in other views, and the NetBiosName column when joining to other views.
Joining Client Health and Status Views
The following query retrieves the resource ID and NetBIOS name for client computers that have provided client health status, the last status message, last policy request date, last successful ping date, and the last client health state name. The results are sorted by NetBIOS name. The query joins the v_CH_ClientSummary client health view with the v_ClientHealthState status view by using the NetbiosName column.
SELECT v_CH_ClientSummary.MachineID,
v_CH_ClientSummary.NetBiosName,
v_CH_ClientSummary.LastStatusMessage,
v_CH_ClientSummary.LastPolicyRequest,
v_CH_ClientSummary.LastSuccessfulPing,
v_ClientHealthState.HealthStateName
FROM v_CH_ClientSummary INNER JOIN
v_ClientHealthState ON
v_CH_ClientSummary.NetBiosName =
v_ClientHealthState.NetBiosName
ORDER BY v_CH_ClientSummary.NetBiosName
Joining Client Health and Inventory Views
The following query retrieves the NetBIOS name for the client computer, domain, user name, whether the client has been deployed, whether the client is assigned, and the site code. The results are sorted by NetBIOS name. The query joins the v_CH_ClientSummary client health view with the v_GS_Computer_System hardware inventory view by using the MachineID and ResourceID columns, respectively.
SELECT v_CH_ClientSummary.NetBiosName,
v_GS_COMPUTER_SYSTEM.Domain0,
v_GS_COMPUTER_SYSTEM.UserName0,
v_CH_ClientSummary.ClientDeployed,
v_CH_ClientSummary.ClientAssigned,
v_CH_ClientSummary.SiteCode
FROM v_GS_COMPUTER_SYSTEM INNER JOIN
v_CH_ClientSummary ON
v_GS_COMPUTER_SYSTEM.ResourceID =
v_CH_ClientSummary.MachineID
ORDER BY v_CH_ClientSummary.NetBiosName