2 Replies Latest reply on Oct 31, 2015 7:59 PM by njoylif

    Custom SQL alert doubt

    contracer

      Hi,

      Is there any way to change this sql header in the Custom SQL alert ?

       

      alert_npm.jpg

       

      I need insert a little different sql.

      Thanks

        • Re: Custom SQL alert doubt
          ladames

          Are you still using the alert report writer on the Orion poller?

          • Re: Custom SQL alert doubt
            njoylif

            completely unsupported, of course, but....***note gets tricky with quotes and double quoting to escape quote b/c writing SQL to column...test in non-prod or on dummy alert first and always BU DB before doing work.

            uses alertdefinitions table (pre 11.x)...and this is complex example...  I select then update

             

             

            select * from alertdefinitions where AlertName like '%Shared Volume Groups%'

             

            -- update

            alertdefinitions

            set ResetQuery='SELECT distinct(Containers_AlertsAndReportsData.GroupID) AS NetObjectID, Containers_AlertsAndReportsData.GroupName AS Name

            FROM Containers_AlertsAndReportsData

            /*SplitMarker*/

            INNER JOIN

            (   select vc.caption, total, issues, convert(varchar(10),convert(int,100*issues/total)) as perc  

                FROM   (    select v.caption, count(v.caption) as total  

                        from volumes v WHERE caption in   

                        (      select distinct(name)    

                            from containermembersnapshots

                            WHERE containerID in

                            (       select containerid

                                from containers

                                WHERE name like ''%dsmounts-%'' --NOTE 2x single qoutes     

                            )   

                        )  

                        group by caption 

                    ) as vc 

                left join  

                    (    select caption, count(caption) as issues  

                        from volumes

                        WHERE caption in   

                        (      select distinct(name)    

                            from containermembersnapshots

                            WHERE containerID in

                            (       select containerid   

                                from containers

                                WHERE name like ''%dsmounts-%''      

                            )   

                        )   

                        AND       

                        (    

                            (Critical IS NOT NULL)

                            AND     

                            (VolumePercentUsed >= Critical)      

                        )   

                        group by caption 

                    ) as vcwe 

                ON vc.caption=vcwe.caption

                WHERE (ISNULL(convert(varchar(10),convert(int,100*issues/total)),0) <=50)

                ) as a

            ON Containers_AlertsAndReportsData.GroupMemberName=a.caption'

            where AlertName like '%Shared Volume Groups%'