SQL Query for collection creation date, deployment name and enforcement state of endpoints


Hello HTMDians,

Help needed from all SQL Guru’s in our forum.

  1. I have been trying to write one SQL Query where i need to fetch the compliance of the collection along with the enforcement state of each servers inside the collection and maintenance window. And this is not it. I am also trying to get all the collections created on specific date along with the deployment targeted to it and the status of the deployment for each client it has been deployed to i.e Compliant, Non-Compliant, Failed to install update. The subscription should get the maintenance window from the collection and send the enforcement at the end of the maintenance window.

Open to everything like PS, SSRS, PowerBi, Teraform, Azure Runbook automation. etc.

My current setup.

1 Primary, 4 MP, 3 HTTP and 1 HTTPS MP, CMG, 43 DP’s, 41 HTTP and 2 HTTPS (CDP included). 2 SUP, 1 HTTP and another one HTTPS.

Answers ( 3 )


    I searched and couldn’t find any so had to write a new one.

    select vrs.name0,vch.Clientstatedescription, a.EnforcementDeadline,
    a.Collectionname,a.AssignmentName as DeploymentName, sn.StateName as LastEnforcementState
    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
    join v_ch_clientsummary vch on vch.resourceid=vrs.resourceid
    –DATEDIFF(DAY,a.EnforcementDeadline,getdate()) = ‘1’
    vrs.operatingSystem0 like ‘%server%’


    Ok Sir. Will check with Karthik and if this requirement is fulfilled then going to share here.

  1. I think karthic k is the best to help you here. Can you try to email him with this thread?

    Best answer
    Cancel the best answer

Leave an answer

Sorry, you do not have permission to answer to this question .