14 Replies Latest reply on Oct 24, 2019 12:27 AM by fmasotti

    How to filter Custom Tile

    Deltona

      Hi guys,

       

      When creating a Custom Tile resource, there's an option to filter out the results displayed. What I am trying to do is display a Custom Tile for specific Volumes and the filter should be for specific Nodes based on their custom property.

      I haven't been successfull at creating this filter so far. What I do know so far is that the filter would have to be applied to the Advanced SQL filter section of the Custom Tile.

       

      Any help/pointers would be greatly appreciated.

       

      TIA

        • Re: How to filter Custom Tile
          I LIKE EGGS

          you could try this:

           

          SWQL TILE QUERY

           

          SELECT v.Status, n.InstanceSiteId,np.n_type FROM Orion.Nodes (nolock=true) n

          INNER JOIN orion.volumes (nolock=true) as v on v.nodeid = n.nodeid

          INNER JOIN Orion.NodesCustomProperties (nolock=true) as np on np.nodeid = n.nodeid

          WHERE np.xxx = 'xxx'

           

          SWQL ASSET EXPLORER QUERY

           

          SELECT Status, DisplayName,  SiteName, InstanceSiteId, DetailsUrl, Entity

          FROM (

          SELECT v.Status, v.FullName as DisplayName, '' as SiteName, v.InstanceSiteId, v.DetailsUrl , v.InstanceType as Entity FROM Orion.Nodes (nolock=true) n

          INNER JOIN orion.volumes (nolock=true) as v on v.nodeid = n.nodeid

          INNER JOIN Orion.NodesCustomProperties (nolock=true) as np on np.nodeid = n.nodeid

          WHERE np.xxx = 'xxx'

          )

           

          SWQL ALERT QUERY

           

          SELECT

          Name,

          CASE WHEN c.Severity = 4 THEN 0

               WHEN c.Severity = 0 THEN 1

               WHEN c.Severity = 1 THEN 2

               WHEN c.Severity = 3 THEN 3

               WHEN c.Severity = 2 THEN 4 END as Priority, a.InstanceSiteId

                  FROM Orion.AlertActive (nolock=true) a

                      INNER JOIN Orion.AlertObjects (nolock=true) AS o ON o.AlertObjectID = a.AlertObjectID

                      INNER JOIN Orion.AlertConfigurations (nolock=true) AS c ON o.AlertID = c.AlertID

                      INNER JOIN Orion.NodesCustomProperties (nolock=true) AS cp ON cp.nodeid = o.relatednodeid

                       WHERE  cp.xxx = 'xxx'

                 order by a.TriggeredDateTime desc

           

          I feel like the Alert Query could be better and reflect the status that is present in the volumes table at the moment its picking up any alert that relates to that node that has the custom property enabled..

           

          3 of 3 people found this helpful
            • Re: How to filter Custom Tile
              Deltona

              Thanks for this! Got one for Interfaces as well?

              Filter interfaces displayed based on the Custom Property of the parent Node.

                • Re: How to filter Custom Tile
                  I LIKE EGGS

                  surething, you could use something like this:

                   

                  SWQL TILE QUERY

                  SELECT Status, InstanceSiteId FROM (

                  SELECT i.Status, i.InstanceSiteId

                  FROM Orion.NPM.Interfaces (nolock=true) i

                  INNER JOIN Orion.NodesCustomProperties (nolock=true) as np on np.nodeid = i.nodeid

                  WHERE np.xxx LIKE 'xxx'

                  )

                   

                  SWQL ASSET EXPLORER QUERY

                   

                  SELECT Status, DisplayName,  SiteName, InstanceSiteId, DetailsUrl, Entity

                   

                  FROM (

                   

                  SELECT Distinct i.FullName as DisplayName, i.Status,'' as SiteName, i.InstanceSiteId, i.DetailsUrl , i.InstanceType as Entity

                  FROM Orion.NPM.Interfaces (nolock=true) i

                  INNER JOIN ORION.Nodes n on n.NodeID = i.nodeid

                  INNER JOIN Orion.NodesCustomProperties (nolock=true) as np on np.nodeid = N.NodeID

                  where np.xxx  LIKE 'xxx'

                  )

                   

                  SWQL ALERT QUERY

                   

                  SELECT

                  Name,

                  CASE WHEN c.Severity = 4 THEN 0

                       WHEN c.Severity = 0 THEN 1

                       WHEN c.Severity = 1 THEN 2

                       WHEN c.Severity = 3 THEN 3

                       WHEN c.Severity = 2 THEN 4 END as Priority, a.InstanceSiteId

                          FROM Orion.AlertActive (nolock=true) a

                              INNER JOIN Orion.AlertObjects (nolock=true) AS o ON o.AlertObjectID = a.AlertObjectID

                              INNER JOIN Orion.AlertConfigurations (nolock=true) AS c ON o.AlertID = c.AlertID

                              INNER JOIN Orion.NodesCustomProperties (nolock=true) AS cp ON cp.nodeid = o.relatednodeid

                    WHERE cp.xxx LIKE 'xxx'

                  ORDER  BY  a.TriggeredDateTime desc

                   

                   

                  id clean up the alerts part up and few other bits, like not using the Distinct in the Asset Explorer.. hopfully this helps

                  4 of 4 people found this helpful
              • Re: How to filter Custom Tile
                mjalden1

                Anyone know how to get this option back, under add widgets I am missing Custom Tile

                  • Re: How to filter Custom Tile
                    I LIKE EGGS

                    You will need to add the widget back to the view, its worth mentioning that once you remove the widget it will go back to its default

                     

                    2 of 2 people found this helpful
                      • Re: How to filter Custom Tile
                        jblankjblank

                        This has not gone away!  Apologies for the confusion but the Custom Tile widget has has now been properly renamed to the Status Summary Widget. This should clearly indicate the purpose of the widget and remove any confusion about why it was called Custom Tile from the days of EOC 2.0.  We also made some small improvements. Within the Advanced Entity Filter, things have been properly alphabetized to ensure you are able to quickly and easily find the property you are looking for.  Screen real estate can also be a critical commodity, so to allow you to secure as much as possible, we decided to provide an option where you could hide the Active Alerts section of the widget.  Some have found it quite beneficial to create a condensed scoreboard of status in their environments. Images demonstrate below:

                         

                         

                         

                         

                        4 of 4 people found this helpful
                          • Re: How to filter Custom Tile
                            Deltona

                            Hi,

                             

                            I'm on 12.4 2019.2 HF3 and can't find the Status Summary Widget. Is this something that will be available in a future release?

                            Also, I appreciate the resource's ability to be customized with SWQL, as this gives a lot of flexibility for those that need it.

                            But how about including a basic filtering option where one could filter interfaces by Node custom properties?

                            It would be awesome if the default behavior was to provide a basic filter, while still providing advanced filtering options if required.

                              • Re: How to filter Custom Tile
                                I LIKE EGGS

                                Deltona I Agree, this would make my life allot easier specially when i use these tiles inside EOC and use grouping customers with custom properties is quite tedious.

                                 

                                It would also be good to have the ability to not only link to nodes/ volumes and interfaces etc but to have it link to Alerts and sync criticality of alert and have some basic informaiont about the alert. anyways i have created a work around to get tiles to populate based on alerts and have some basic information on the asset explorer, would be nice to have this feature aswel

                                  

                                 

                                Swql Title Query: ( i have just pulled the queries out you may need to chop and change the queries )

                                SELECT Status, InstanceSiteId FROM (
                                Select case when c.Severity = 2 then 14
                                            when c.Severity = 1 then 3
                                ELSE '1'
                                             END as [status],
                                            aA.InstanceSiteId
                                    FROM Orion.AlertActive (nolock=true) aa
                                    INNER JOIN Orion.AlertObjects (nolock=true) O ON O.ALERTOBJECTID = Aa.AlertObjectID AND o.InstanceSiteId = aa.InstanceSiteId
                                    INNER JOIN Orion.AlertConfigurations (nolock=true) AS c ON o.AlertID = c.AlertID AND o.InstanceSiteId = c.InstanceSiteId
                                   
                                Where aa.InstanceType IN('Orion.AlertActive') 
                                AND aa.Acknowledged IS NULL 
                                AND Aa.InstanceSiteId = 14
                                
                                
                                
                                
                                UNION
                                (
                                SELECT n.Status, n.InstanceSiteId FROM Orion.Nodes (nolock=true)  n
                                inner join orion.NodesCustomProperties (nolock=true) AS np ON n.NodeID = np.NodeID
                                WHERE n.InstanceSiteId IN(14) and np.n_mute = 0 and n.status  IN ('1')
                                ))

                                 

                                Asset: SELECT Status, DisplayName, SiteName, InstanceSiteId, DetailsUrl, Entity FROM (

                                Select Status, InstanceSiteId,[DisplayName],[SiteName],[DetailsUrl],Entity FROM 
                                
                                
                                (
                                Select case when c.Severity = 2 then 14
                                            when c.Severity = 1 then 3
                                            END as [Status],
                                            A.InstanceSiteId,
                                'Active: ' + Case
                                  When  Floor((SecondDiff(a.TriggeredDateTime,GetUtcDate()) + 0.0)/86400)>0 Then
                                  ToString(ToString(Floor((SecondDiff(A.TriggeredDateTime,GetUtcDate()) +0.0)/86400))+' days ago | ALERT: '+ a.TriggeredMessage + ' | UnAcknowledged '  )
                                  When Floor(((SecondDiff(a.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(A.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600)>0 Then
                                  ToString(ToString(Floor(((SecondDiff(A.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(A.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+
                                  ToString(Floor(((SecondDiff(A.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(a.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ' + ' | ALERT: '  + a.TriggeredMessage + ' | UnAcknowledged ' )
                                  When Floor(((SecondDiff(a.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(a.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60)>0 Then 
                                  ToString(ToString(Floor(((SecondDiff(A.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(A.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+' m ' + ' | ' + a.TriggeredMessage + ' | UnAcknowledged ' )
                                Else ''
                                End AS [DisplayName],
                                --'Alert: ' + ToString(DayDIFF(A.TriggeredDateTime,GETDATE())) + ' days ago  |' + ' ' + a.TriggeredMessage + ' ' + '|' + ' UnAcknowledged '  AS [DisplayName],
                                 s.name as [SiteName],
                                          '/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(a.AlertObjectID) as [DetailsUrl], 
                                 --o.EntityDetailsUrl as [DetailsUrl],
                                n.InstanceType as Entity
                                    FROM Orion.AlertActive (nolock=true) a
                                    INNER JOIN Orion.AlertObjects (nolock=true) O ON O.ALERTOBJECTID = A.AlertObjectID AND o.InstanceSiteId = a.InstanceSiteId
                                    INNER JOIN Orion.AlertConfigurations (nolock=true) AS c ON o.AlertID = c.AlertID AND o.InstanceSiteId = c.InstanceSiteId
                                    INNER join Orion.Sites (nolock=true) as s ON S.SITEID = A.InstanceSiteId
                                    INNER join orion.nodes (nolock=true) as n ON n.nodeid = o.relatednodeid
                                    left JOIN ORION.NodesCustomProperties (nolock=true) AS cp ON cp.nodeid = o.relatednodeid
                                
                                
                                WHERE n.InstanceType IN('Orion.nodes') AND a.Acknowledged IS NULL  
                                AND A.InstanceSiteId = 12
                                ) 
                                
                                
                                UNION all 
                                (
                                select g.Status, g.InstanceSiteId,g.[DisplayName],s.name as [SiteName],[DetailsUrl], g.EntityType as [Entity]
                                FROM Orion.Nodes g
                                left join Orion.Sites (nolock=true) as s ON S.SITEID = g.InstanceSiteId  WHERE g.InstanceSiteId IN(14)  and g.status = 1 
                                
                                
                                
                                
                                ORDER BY DisplayName WITH NOLOCK
                                )
                                ) as e

                                 

                                Alert:

                                SELECT 
                                Name,
                                --case when a.Acknowledged is null then CONCAT(c.Name, ' | Unacknowledged ') ELSE CONCAT(c.Name,' | Acknowledged ') END as name,
                                  -- WHEN a.Acknowledged IS NULL then c.Name END as NAMeE,
                                CASE WHEN c.Severity = 4 THEN 0 
                                    WHEN c.Severity = 0 THEN 1 
                                    WHEN c.Severity = 1 THEN 2 
                                    WHEN c.Severity = 3 THEN 3 
                                    WHEN c.Severity = 2 THEN 4 END as Priority, a.InstanceSiteId,TriggeredDateTime
                                        FROM Orion.AlertActive (nolock=true) a 
                                            INNER JOIN Orion.AlertObjects (nolock=true) AS o ON o.AlertObjectID = a.AlertObjectID AND o.InstanceSiteId = a.InstanceSiteId
                                            INNER JOIN Orion.AlertConfigurations (nolock=true) AS c ON o.AlertID = c.AlertID AND o.InstanceSiteId = c.InstanceSiteId
                                
                                    WHERE  a.InstanceType IN('Orion.AlertActive') 
                                         AND InstanceSiteId IN(13) and a.Acknowledged IS NULL  
                                order by a.TriggeredDateTime desc
                                1 of 1 people found this helpful
                                  • Re: How to filter Custom Tile
                                    jblankjblank

                                    Deltona and i_like_eggs - Thank you for your feedback!  So we have the basic filtering option, then the Advanced Entity Filter, Then the Advanced SWQL which we hope adds a number of ways in which to filter for virtually anything.  What I am hearing is that you would like a way in the Advanced Entity filter to potentially leverage Parent/ Ancestor objects for further filtering?  Is that accurate?  So in other words, I might be looking for a summary of interfaces or volumes, but I am using the specific subset of nodes as an additional filter? 

                                      • Re: How to filter Custom Tile
                                        Deltona

                                        Correct. Basically what I would prefer to work with was something that didn't oblige/force me to drop everything that I am doing in order to figure out what SWQL is and what query I am going to need to 'code' a resource.

                                        It takes way too long to get the information that I need to be presented in such a small resource. I can't imagine trying to manually filter this out using SWQL in at least 3 sections as anything other than time consuming, even for SWQL veterans!

                                        In short, if we had the same point and click method of specifying the datasource, as we currently do if we used the Custom Table resource, then creating Custom Tile/Status Summary would exceed my expectations.

                                         

                                        Unexpected Simplicity

                                  • Re: How to filter Custom Tile
                                    fryman

                                    I have the exact same query, what was the chances of finding my request from a post created in recent times. I wanted to remove the alerts part from a custom tile, and am running 2019.2 HF3, and do not have status summary anywhere.