2 Replies Latest reply on May 11, 2016 2:23 AM by karenpeacock

    Best place for customized SQL queries




      I would like to share some slightly customized SQL queries which improve slightly on the views that can be found in the Protect Console, albeit in a CSV format. Would it be best to share these in a discussion or other area of the forum? Is anyone interested in these SQL queries?

      Thank you.

        • 1. Re: Best place for customized SQL queries

          I am going to go ahead and share my scripts in case anyone find them useful. I have added them to Pastebin, but I will include them at the bottom of this post.


          [T-SQL] Patches in Patch Group - Pastebin.com


          -- ====================================================================================================================

          -- Purpose: Query Shavlik database to get detailed listing of patches in a patch group.


          -- Dependencies: None


          -- Notes: 


          -- Version Date Author Comments

          -- -------- ----------- ------------------- ---------------------------------------------------------------------------

          -- v1.00 2016-05-10 Andrew Kaiser Initial Release.

          -- ====================================================================================================================



          USE [Protect]


          SELECT p.patchBulletinID AS 'Bulletin ID'

          , CONVERT(VARCHAR(10),p.patchBulletinDate,101) AS 'Bulletin Release Date'

          , p.patchBulletinTitle AS 'Bulletin title'

          , rpt.Value AS 'Patch Type Description'

          , vs.Value AS 'Vendor Severity'

          FROM PatchGroup AS pg

          INNER JOIN PatchGroupPatch AS pgp

          ON pg.pgrpKey = pgp.patchGroupId

          INNER JOIN Patches AS p

          ON p.patchID = pgp.patchId

          INNER JOIN Reporting.PatchType AS rpt

          ON rpt.Id = p.patchType

          INNER JOIN LinkPatchProduct AS lpp

          ON lpp.pspplID = p.patchID

          INNER JOIN Reporting.VendorSeverity AS vs

          ON lpp.pspplMSSeverity = vs.Id

          WHERE pgrpName = 'WU-OS-2016-May' -- Replace pgrpName with the patch group name you are trying to list.

          ORDER BY rpt.Value DESC, vs.Value -- This sorts by Patch Type, then by Vendor Severity, feel free to modify with any of the SELECT values above.

          • 2. Re: Best place for customized SQL queries
            karenpeacock Employee

            Hi Andrew


            Thank you for sharing these with the community!  We are working on a new area where customers can share scripts and other utilities / ideas centrally so they can more easily located.  We'll also post back here once this is ready.


            Best wishes