2 Replies Latest reply on May 17, 2017 12:24 PM by bmacmt

    Include more than the top record in an swql query for an email action?

    bmacmt

      I am learning swql and have a query I want to include in the trigger action for an email alert.

      The query returns a top xx result set.

       

      I found the article that says only the top row and first column will be returned.

      I can use the concat to combine the fields but is there a way to render the swql so I only get one record so I can get the multiple records to display?

       

      or is there another way around getting multiple records to display in the trigger action?

       

      thanks, bob

        • Re: Include more than the top record in an swql query for an email action?
          mesverrum

          Can you give a little more detail about the contents of the query, might help to figure out a way to jam it all together?  I would probably build a report/custom swql resource that includes the data set you want and instead of using a send email action I would use the email a web page action and point it at that resource.

            • Re: Include more than the top record in an swql query for an email action?
              bmacmt

              Sure, here is the query:

              select  top 4   concat(ToLocal(f.timestamp) ,' | ',n.nodename, ' | ', f.sourceip, ' | ', f.destinationip, ' | ', round((sum(f.bytes) * 8) / 60 /1000000,2),' | ',  max(port))  as lline

              from orion.npm.interfaces i

              --FROM Orion.Netflow.Flowsbyconversation f

              inner join orion.netflow.flowsbyconversation f on f.interfaceidrx = i.interfaceid

              inner join orion.nodes n on n.nodeid = i.nodeid

              WHERE f.TimeStamp >= GetUtcDate() - 0.0021

              and n.nodename='name of switch to evaluate'

              group by ToLocal(f.timestamp), f.sourceip, f.destinationip, nodename

              having bytes > 1000000

              order by round((sum(f.bytes) * 8) / 60 /1000000,2) DESC

               

              And here is the query result example:

              05/17/2017 11:11:00 | NORTH | 10.12.2.107 | 10.101.1.20 | 5.17 | 23233

              05/17/2017 11:12:00 | NORTH | 10.12.2.5 | 10.101.1.20 | 3.51 | 23233

              05/17/2017 11:11:00 | NORTH | 10.12.136.111 | 31.13.76.84 | 2.97 | 443

              05/17/2017 11:12:00 | NORTH | 10.12.2.121 | 10.101.1.20 | 2.73 | 23233

               

              I'm wanting to not have to login to orion to get the information on consumers.

              I did solve it a different way (would prefer to do it in wsql though)

              In the alert trigger action I duplicated the call to the above query 4 times and appended the clause

                    with rows x to x

              to the above query.

              (the first one being with rows 1 to 1, the 2nd one being with rows 2 to 2 and so on)

              for the specific row I wanted to report. I then got the top 4 consumers.

               

              thanks for the reply!!!

              bob