9 Replies Latest reply on Nov 11, 2011 7:04 AM by parlevjo

    ePO SQL query to see updatestatus of Hotfix distribution

    online83

      Hi everyone,

       

      is it possible to create a sql query to report the update-status of a Hotfix distribution?

      I mean, it is easy to do it for a patch, but I have not found a possibility to report a Hotfix.

       

      So, I would like to have a sql query to be able to report the distribution of VirusScan Enterprise 8.7i Hotfix 638179

       

      Thanks!

       

      regrads,

      online

        • 1. Re: ePO SQL query to see updatestatus of Hotfix distribution
          Trooper

          Hi,

           

          try this:

           

          select count(*) as 'count' from [EPOLeafNode] left join [EPOProdPropsView_VIRUSCAN] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID] where ( ( ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] = 8 and [EPOProdPropsView_VIRUSCAN].[verProductMinor] = 7 and [EPOProdPropsView_VIRUSCAN].[verProductRevision] = 0 and [EPOProdPropsView_VIRUSCAN].[verProductBuild] = 570 ) and ( [EPOProdPropsView_VIRUSCAN].[hotfix] = N'638179' ) ) and  ( [EPOLeafNode].[ParentID] IN (SELECT [EndAutoID] FROM [EPOBranchNodeEnum] WHERE [StartAutoID] = N'13' UNION SELECT N'13'))  ) union select count(*) as 'count' from [EPOLeafNode] left join [EPOProdPropsView_VIRUSCAN] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID] where ( ( not ( ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] = 8 and [EPOProdPropsView_VIRUSCAN].[verProductMinor] = 7 and [EPOProdPropsView_VIRUSCAN].[verProductRevision] = 0 and [EPOProdPropsView_VIRUSCAN].[verProductBuild] = 570 ) and ( [EPOProdPropsView_VIRUSCAN].[hotfix] = N'638179' ) ) )  and  ( [EPOLeafNode].[ParentID] IN (SELECT [EndAutoID] FROM [EPOBranchNodeEnum] WHERE [StartAutoID] = N'13' UNION SELECT N'13'))  )

           

          do you have EPO-Server version (4.5)?

          if yes , you can import this syntax into your dashboard.

           

           

          trooper!

          1 of 1 people found this helpful
          • 2. Re: ePO SQL query to see updatestatus of Hotfix distribution
            online83

            Hey Trooper,

            that's great. I think this is the correct count of systems.

            But unfortunately I do not know how to import the sql query, to show it on the dashboard...

             

            Yes, we use ePO 4.5 Patch 5

             

            regards,

            online

            • 3. Re: ePO SQL query to see updatestatus of Hotfix distribution
              Trooper

              Hello,

               

              On EPO-Server go to Queries, then select new Query, and follow the pictures that i send yuo:

              query1.jpg

               

              query2.jpg

               

              query3.jpg

               

              query4.jpg

              at end run your query and save.

               

              I hope this wolud help.

               

              Trooper!

               

               


              • 4. Re: ePO SQL query to see updatestatus of Hotfix distribution
                online83

                Hi Trooper,

                that's what I tried before. But it does not work. I only get the result that all client are not compliant.

                 

                I also tried to use a "pie chart" (see below) to filter only for "Hotfix/Patch-Version (VSE)" but then I get only the Patch-Versions listed, but not the hotfix...

                 

                online

                 

                query_1.jpg

                • 5. Re: ePO SQL query to see updatestatus of Hotfix distribution

                  With the previous fix i had this sql. It is not an optimal query. I intercepted most of the code with sql profiler.

                  With an distinct in front it worked for me.

                   

                  SELECT distinct EPOComputerProperties.computerName,  EPOComputerProperties.systemDescription,  EPOLeafNode.ParentID AS groupID,  EPOProductFamilies.FamilyDispName AS fmDispName,  EPOSoftware.SoftwareName AS swSoftwareName,  EPOProductProperties.ProductCode, 51, EPOProductSettings.*

                  FROM EPOProductProperties 

                      LEFT JOIN EPOLeafNode ON EPOLeafNode.AutoID=EPOProductProperties.ParentID 

                      LEFT JOIN EPOComputerProperties ON EPOComputerProperties.ParentID=EPOLeafNode.AutoID 

                      LEFT JOIN EPOProductFamilies ON EPOProductFamilies.ProductCode=EPOProductProperties.ProductCode 

                      LEFT JOIN EPOSoftware ON EPOSoftware.ProductCode=EPOProductProperties.ProductCode 

                      LEFT JOIN EPOProductSettings ON EPOProductProperties.AutoID=EPOProductSettings.ParentID

                  WHERE settingname='fixes'

                  order by computername

                   

                  I only had one fix so far.

                  With one fixed i used this to see which system where not updated yet.

                   

                  SELECT distinct EPOComputerProperties.computerName,  EPOComputerProperties.systemDescription,  EPOLeafNode.ParentID AS groupID,  EPOProductFamilies.FamilyDispName AS fmDispName,  EPOSoftware.SoftwareName AS swSoftwareName,  EPOProductProperties.ProductCode, 51, EPOProductSettings.*

                  FROM EPOProductProperties 

                      LEFT JOIN EPOLeafNode ON EPOLeafNode.AutoID=EPOProductProperties.ParentID 

                      LEFT JOIN EPOComputerProperties ON EPOComputerProperties.ParentID=EPOLeafNode.AutoID 

                      LEFT JOIN EPOProductFamilies ON EPOProductFamilies.ProductCode=EPOProductProperties.ProductCode 

                      LEFT JOIN EPOSoftware ON EPOSoftware.ProductCode=EPOProductProperties.ProductCode 

                      LEFT JOIN EPOProductSettings ON EPOProductProperties.AutoID=EPOProductSettings.ParentID

                  WHERE settingname='fixes'

                  and value <> '643440' order by computername

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                  • 6. Re: ePO SQL query to see updatestatus of Hotfix distribution

                    With 2 hotxfixes it is almost the same query. Only difference is ==> and value <> '638179,643440'

                     

                    SELECT distinct EPOComputerProperties.computerName,  EPOComputerProperties.systemDescription,  EPOLeafNode.ParentID AS groupID,  EPOProductFamilies.FamilyDispName AS fmDispName,  EPOSoftware.SoftwareName AS swSoftwareName,  EPOProductProperties.ProductCode, 51, EPOProductSettings.*

                    FROM EPOProductProperties 

                        LEFT JOIN EPOLeafNode ON EPOLeafNode.AutoID=EPOProductProperties.ParentID 

                        LEFT JOIN EPOComputerProperties ON EPOComputerProperties.ParentID=EPOLeafNode.AutoID 

                        LEFT JOIN EPOProductFamilies ON EPOProductFamilies.ProductCode=EPOProductProperties.ProductCode 

                        LEFT JOIN EPOSoftware ON EPOSoftware.ProductCode=EPOProductProperties.ProductCode 

                        LEFT JOIN EPOProductSettings ON EPOProductProperties.AutoID=EPOProductSettings.ParentID

                    WHERE settingname='fixes'

                    and value <> '638179,643440' order by computername

                    • 7. Re: ePO SQL query to see updatestatus of Hotfix distribution
                      online83

                      Thanks a lot!

                      The query works for me.

                      • 8. Re: ePO SQL query to see updatestatus of Hotfix distribution

                        You can also add a where clause to see only the systems contacted since a certain days, eg: and lastupdate > dateadd(day,-7,getdate())

                         

                        SELECT distinct EPOComputerProperties.computerName,  EPOLeafNode.lastupdate,  EPOComputerProperties.systemDescription,  EPOLeafNode.ParentID AS groupID,  EPOProductFamilies.FamilyDispName AS fmDispName,  EPOSoftware.SoftwareName AS swSoftwareName,  EPOProductProperties.ProductCode, 51, EPOProductSettings.*

                        FROM EPOProductProperties 

                            LEFT JOIN EPOLeafNode ON EPOLeafNode.AutoID=EPOProductProperties.ParentID 

                            LEFT JOIN EPOComputerProperties ON EPOComputerProperties.ParentID=EPOLeafNode.AutoID 

                            LEFT JOIN EPOProductFamilies ON EPOProductFamilies.ProductCode=EPOProductProperties.ProductCode 

                            LEFT JOIN EPOSoftware ON EPOSoftware.ProductCode=EPOProductProperties.ProductCode 

                            LEFT JOIN EPOProductSettings ON EPOProductProperties.AutoID=EPOProductSettings.ParentID

                        WHERE settingname='fixes'

                        and value <> '638179,643440'

                        and lastupdate > dateadd(day,-7,getdate())

                        order by value,lastupdate desc

                        • 9. Re: ePO SQL query to see updatestatus of Hotfix distribution

                          Another query with Last Communication date (modifiedpropsdate) used instead of lastupdate.

                           

                          SELECT distinct EPOComputerProperties.computerName, EPOComputerProperties.username,

                              convert(char(20),EPOLeafNode.lastupdate,120) as lastupdate,

                              substring(EPOLeafNode.ModifiedPropsDate,1,4)+'-'+

                              substring(EPOLeafNode.ModifiedPropsDate,5,2)+'-'+

                              substring(EPOLeafNode.ModifiedPropsDate,7,2)+' '+

                              substring(EPOLeafNode.ModifiedPropsDate,9,2)+':'+

                              substring(EPOLeafNode.ModifiedPropsDate,11,2)+':'+

                              substring(EPOLeafNode.ModifiedPropsDate,13,2) as ModifiedPropsDate,

                            EPOComputerProperties.systemDescription,  EPOLeafNode.ParentID AS groupID,  EPOProductFamilies.FamilyDispName AS fmDispName,  EPOSoftware.SoftwareName AS swSoftwareName,  EPOProductProperties.ProductCode, 51, EPOProductSettings.*

                          FROM EPOProductProperties 

                              LEFT JOIN EPOLeafNode ON EPOLeafNode.AutoID=EPOProductProperties.ParentID 

                              LEFT JOIN EPOComputerProperties ON EPOComputerProperties.ParentID=EPOLeafNode.AutoID 

                              LEFT JOIN EPOProductFamilies ON EPOProductFamilies.ProductCode=EPOProductProperties.ProductCode 

                              LEFT JOIN EPOSoftware ON EPOSoftware.ProductCode=EPOProductProperties.ProductCode 

                              LEFT JOIN EPOProductSettings ON EPOProductProperties.AutoID=EPOProductSettings.ParentID

                          WHERE settingname='fixes'

                          and EPOProductSettings.value <> '638179,643440'

                          --and EPOComputerProperties.lastupdate > dateadd(day,-21,getdate())

                          and EPOLeafNode.ModifiedPropsDate > replace(replace(replace(convert(char(19),dateadd(day,-21,getdate()),120),'-','' ),' ',''),':','')

                          --and EPOComputerProperties.computername='xxxxxxxx'

                          order by EPOProductSettings.value,convert(char(20),EPOLeafNode.lastupdate,120) desc