SQL Query for collection creation date, deployment name and enforcement state of endpoints
Question
Hello HTMDians,
Help needed from all SQL Guru’s in our forum.
- 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
where
–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.
I think @Karthick is the best to help you here. Can you try to email him with this thread?