PENDING SQL Query Assistance

[Nav4339]

I am hoping someone can assist me with SQL. I am using the query below. I am using this to get the status of Software Updates to all clients. What I need though to be able to include the Boundary Group Name that the client belongs too. I need this to identify which site the device belongs too as we dont have any other inventory details with this. The query will help with the location of the device, Is it possible to get this included?

select sn.StateName as LastEnforcementState, vrs.name0 as ComputerName, a.AssignmentName as DeploymentName, assc.StateTime , a.CollectionName from v_CIAssignment a join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0) join v_R_System vrs on vrs.ResourceID=assc.ResourceID where a.AssignmentID='xxxxxxxx' order by LastEnforcementState

 

[Garth]

All of the supported SQL views are listed here. https://learn.microsoft.com/en-us/m...lviews/sql-server-views-configuration-manager

 

[Nav4339]

Thank you Garth, so what I require is not possible then? Is there any query within SQL that would be able to give the device name and the boundary that it assigned too?

 

[kwmccann]

I am hoping someone can assist me with SQL. I am using the query below. I am using this to get the status of Software Updates to all clients. What I need though to be able to include the Boundary Group Name that the client belongs too. I need this to identify which site the device belongs too as we dont have any other inventory details with this. The query will help with the location of the device, Is it possible to get this included?

select sn.StateName as LastEnforcementState, vrs.name0 as ComputerName, a.AssignmentName as DeploymentName, assc.StateTime , a.CollectionName from v_CIAssignment a join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0) join v_R_System vrs on vrs.ResourceID=assc.ResourceID where a.AssignmentID='xxxxxxxx' order by LastEnforcementState

V_R_System has AD_Site_Name0... is this not getting populated for your clients?