Kaseya Community

Patch Management SQL querys

This question has suggested answer(s)

Hi

Anyone that could help me with a query?

I need a query to sort out witch machines that are missing 1 on more applied patches in one specific organization

All Replies
  • SELECT orgName,

         [real_missing_patches]

         ,[Machine_GroupID]

         ,[ComputerName]

         ,[ReverseGroupName]

         ,pat.[GroupName]

         ,[OperatingSystem]

         ,[LastLoggedOnUser]

         ,[LastCheckinTime]

         ,[LastRebootTime]

         ,[totalPatches]

         ,[installed]

         ,[missingApproved]

         ,[missingDenied]

         ,[missingManual]

         ,[pending]

         ,[notReady]

         ,[failed]

         ,[rebootPending]

         ,[initialUpdateRunning]

         ,[testStatus]

         ,[testStatusDescription]

         ,[lastScanType]

         ,[lastScanTypeDescription]

         ,[scanStatus]

         ,[nonSupportedOS]

         ,[lastPatchScan]

         ,[nextPatchScan]

         ,[lastAutomaticUpdate]

         ,[nextAutomaticUpdate]

         ,[wuaSelfUpdateRequired]

         ,[wuaSelfUpdateRequiredDescription]

         ,[online_status]

     FROM (

     SELECT  ppol.PolicyName, ISNULL(patdet.missing_patches, 0) AS real_missing_patches, pstat.Machine_GroupID, pstat.ComputerName, pstat.ReverseGroupName, pstat.GroupName,

    pstat.OperatingSystem, pstat.LastLoggedOnUser, pstat.LastCheckinTime, pstat.LastRebootTime, pstat.totalPatches, pstat.installed, pstat.missingApproved, pstat.missingDenied, pstat.missingManual,

    pstat.pending, pstat.notReady, pstat.failed, pstat.rebootPending, pstat.initialUpdateRunning, pstat.testStatus, pstat.testStatusDescription, pstat.lastScanType, pstat.lastScanTypeDescription, pstat.scanStatus,

    pstat.nonSupportedOS, pstat.lastPatchScan, pstat.nextPatchScan, pstat.lastAutomaticUpdate, pstat.nextAutomaticUpdate, pstat.wuaSelfUpdateRequired, pstat.wuaSelfUpdateRequiredDescription,

    CASE WHEN pstat.[online] > 0 THEN 1 ELSE 0 END AS online_status

    FROM            dbo.vPatchStatusByAgent  AS pstat WITH (NOLOCK) LEFT OUTER JOIN

    (SELECT        MAX(PolicyName) AS PolicyName, agentGuid

      FROM            dbo.vPatchPolicyMember

      GROUP BY agentGuid) AS ppol ON ppol.agentGuid = pstat.agentGuid LEFT OUTER JOIN

    (SELECT        COUNT(agentGuid) AS missing_patches, agentGuid

      FROM            dbo.vPatchStatus

      WHERE        (PatchAppliedFlag = 0) AND (ApprovalStatus = 0) AND (PatchIgnoreFlag = 0)

      GROUP BY agentGuid) AS patdet ON pstat.agentGuid = patdet.agentGuid

    WHERE        (ppol.PolicyName IS NOT NULL))  pat

    INNER JOIN dbo.MachGroup mg ON pat.GroupName = mg.groupName

    INNER JOIN kasadmin.org ON mg.orgFK = kasadmin.org.id

  • Nice catch!