9 Replies Latest reply on Feb 15, 2017 1:52 AM by horia.gunica

    Curl JSON Query

    horia.gunica

      Hello ,

       

      I'm trying the following DB query via CURL:

       

      curl --insecure -v -u <USERNAME> https://<IP_ADDRESS>:17778/SolarWinds/InformationService/v3/Json/Query?query=SELECT+IfName+,+Alias+,+Outbps+,+Inbps+FROM+Orion.NPM.Interfaces | awk -F',' '{ out=""; for(i=2;i<=NF;i++){out=out" "$i "\n"}; print out }'

       

      What I am trying to get is every interface known by NPM, it's Name , it's Description (Alias) and it's Out/In bps counters .After I get the results - I parse them and get an output like this for each interface:

       

      {"IfName":"Po3.SI.4526"

      "Alias":"VPN Link Client Test"

      "Outbps":119033.211

      "Inbps":29914.1465}

       

      So this query works - now what I'm trying to do is do some filtering via "Alias" :

       

      curl --insecure -v -u <USERNAME> https://<IP_ADDRESS>:17778/SolarWinds/InformationService/v3/Json/Query?query=SELECT+IfName+,+Alias+,+Outbps+,+Inbps+FROM+Orion.NPM.Interfaces+WHERE+Alias+LIKE+'%Link%' | awk -F',' '{ out=""; for(i=2;i<=NF;i++){out=out" "$i "\n"}; print out }'

       

      And I get the following error:

       

      "

      * Closing connection #0

      "ExceptionType":"SolarWinds.Data.SWQL.SWQLSyntaxException"

      "FullException":"SolarWinds.Data.SWQL.SWQLSyntaxException: no viable alternative at input '%' in Where clause ---> Antlr.Runtime.NoViableAltException: A recognition error occurred.\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.unaryExpression()\u000d\u000a   --- End of inner exception stack trace ---\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.unaryExpression()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.multiplyExpression()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.additiveExpression()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.concatenation()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.relationalExpression()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.equalityExpression()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.negatedExpression()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.logicalAndExpression()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.logicalOrExpression()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.expression()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.logicalExpression()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.whereClause()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.queryRule()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.selectStatement()\u000d\u000a   at SolarWinds.Data.SWQL.Parser.SWQLParser.statement()\u000d\u000a   at SolarWinds.Data.SWQL.SWQLParser.Parse(String query)\u000d\u000a   at SolarWinds.Data.Query.Engine.QueryProcessor.ProcessInternal(String query

        IQueryExecutionContext context)\u000d\u000a   at SolarWinds.InformationService.Core.QueryPlanCache.GetQueryPlan(String query

      "

       

      If I try this using SWQL - it works just fine...

       

      Any ideas?

       

      Best Regards!

        • Re: Curl JSON Query
          Steven Klassen

          Can you edit your post and click 'Advanced Editor' and then highlight your code and format as 'Plain Text'? I think some of the characters are getting eaten by the formatter.

          1 of 1 people found this helpful
            • Re: Curl JSON Query
              horia.gunica

              I've removed formatting and and highlighted the text - Red with curl commands and blue - the output of the specific commands.

               

              Best regards!

                • Re: Curl JSON Query
                  Steven Klassen

                  You just need some quotes:

                   

                  sklassen@klassen-LT MINGW64 ~/Dropbox/Workspace/src/github.com/mrxinu/syncnodes (master)
                  $ curl --insecure -v -u your_user:your_password https://your_host:17778/SolarWinds/InformationService/v3/Json/Query?query="SELECT+IfName+,+Alias+,+Outbps+,+Inbps+FROM+Orion.NPM.Interfaces+WHERE+Alias+LIKE+'%Link%'" | awk -F',' '{ out=""; for(i=2;i<=NF;i++){out=out" "$i "\n"}; print out }'
                  

                   

                  When I changed it to 'Eth' instead I got a good return:

                   

                  sklassen@klassen-LT MINGW64 ~/Dropbox/Workspace/src/github.com/mrxinu/syncnodes (master)
                  $ curl --insecure -v -u apiuser:apiuser151515 https://192.168.21.55:17778/SolarWinds/InformationService/v3/Json/Query?query="SELECT+IfName+,+Alias+,+Outbps+,+Inbps+FROM+Orion.NPM.Interfaces+WHERE+Alias+LIKE+'%Eth%'" | awk -F',' '{ out=""; for(i=2;i<=NF;i++){out=out" "$i "\n"}; print out }'
                  
                  ...
                  > GET /SolarWinds/InformationService/v3/Json/Query?query=SELECT+IfName+,+Alias+,+Outbps+,+Inbps+FROM+Orion.NPM.Interfaces+WHERE+Alias+LIKE+'%Eth%' HTTP/1.1
                  > Host: 192.168.21.55:17778
                  > Authorization: Basic YXBpdXNlcjphcGl1c2VyMTUxNTE1
                  > User-Agent: curl/7.46.0
                  > Accept: */*
                  >
                  { [5 bytes data]
                  < HTTP/1.1 200 OK
                  < Content-Length: 515
                  < Content-Type: application/json
                  < Server: Microsoft-HTTPAPI/2.0
                  < Date: Thu, 09 Feb 2017 00:05:51 GMT
                  <
                  { [515 bytes data]
                  100   515  100   515    0     0    578      0 --:--:-- --:--:-- --:--:--   588
                  * Connection #0 to host 192.168.21.55 left intact
                   "Alias":"Connected to L1S181 on Port Eth0/0 (DMZ)"
                   "Outbps":166525.953
                   "Inbps":41279.44}
                   {"IfName":"Ethernet_15"
                   "Alias":"Ethernet 3"
                   "Outbps":0.0
                   "Inbps":0.0}
                   {"IfName":"Ethernet_19"
                   "Alias":"Ethernet 4"
                   "Outbps":0.0
                   "Inbps":0.0}
                   {"IfName":"Ethernet_10"
                   "Alias":"Ethernet"
                   "Outbps":11895.96
                   "Inbps":3133.51367}
                   {"IfName":"ethernet_32769"
                   "Alias":"Ethernet"
                   "Outbps":172296.4
                   "Inbps":5113411.0}
                   {"IfName":"Ethernet_10"
                   "Alias":"Ethernet"
                   "Outbps":127521.641
                   "Inbps":227304.438}]}
                  
                  1 of 1 people found this helpful
                    • Re: Curl JSON Query
                      horia.gunica

                      Hello Steven!

                       

                      Thank you for you reply - after adding quotes it doesn't give me any error anymore .

                       

                      If you have some spare time to look at a certain example - I have the following SWQL Query :

                       

                      SELECT IF.FullName, IF.Outbps, IF.Inbps
                      FROM Orion.NPM.Interfaces IF
                      WHERE IF.FullName LIKE '%22610%'
                      

                       

                      Which returns the following result :

                       

                      FullNameOutbpsInbps
                      dr1-PE - Vlan180 · VPN TEST ID10034 Location_1 SID: 22610368.164386.578
                      dr1-PE - Serial3/1/3:0.171 · VPN TEST ID10034 Location_1 SID: 22610468.261423.866

                       

                      This is what I was looking for - to match that "SID" value - anywhere in the IF.FullName (or IF.Alias in previous example).

                       

                      Now if I do the same thing with cURL:

                       

                      curl --insecure -v -u <USER> https://<IP_ADDR>:17778/SolarWinds/InformationService/v3/Json/Query?query="SELECT+IF.FullName+,+IF.Outbps+,+IF.Inbps+FROM+Orion.NPM.Interfaces+IF+WHERE+IF.FullName+LIKE+'%22610%'" | awk -F',' '{ out=""; for(i=2;i<=NF;i++){out=out" "$i "\n"}; print out }'
                      

                       

                      The command doesn't return any errors - but it doesn't return any output either...

                       

                      Any ideas what could be the issue ?

                       

                      Thanks your for your help!

                       

                      Best Regards!            

                        • Re: Curl JSON Query
                          tdanner

                          Do you get any output from the curl command alone without the | awk ... part?

                            • Re: Curl JSON Query
                              horia.gunica

                              Nope:

                               

                              < HTTP/1.1 200 OK

                              < Content-Length: 14

                              < Content-Type: application/json

                              < Server: Microsoft-HTTPAPI/2.0

                              < Date: Fri, 10 Feb 2017 08:47:40 GMT

                              <

                              * Connection #0 to host 172.16.126.188 left intact

                              * Closing connection #0

                              {"results":[]}

                                • Re: Curl JSON Query
                                  tdanner

                                  Oh! I see the problem. I don't know why I didn't notice that before. The % character is special in the query string of a Url. It's used for ascii-encoding special characters. ASCII 0x22 is a double quote character. So when your query gets past the REST front end to the query processor, it looks like this:

                                   

                                  SELECT IF.FullName , IF.Outbps , IF.Inbps FROM Orion.NPM.Interfaces IF WHERE IF.FullName LIKE '"610%'

                                   

                                  So if you actually had an interface whose FullName started with "610 (which is unlikely!) then it would have been returned.

                                   

                                  To avoid this problem you can either %-encode the % characters by replacing them with %25. Like this:

                                   

                                  SELECT+IF.FullName+%2C+IF.Outbps+%2C+IF.Inbps+FROM+Orion.NPM.Interfaces+IF+WHERE+IF.FullName+LIKE+'%25610%25'

                                   

                                  Another way to avoid this problem would be to switch to the POST syntax for queries, which involves putting the SWQL query into a JSON object and sending it in the HTTP request body instead of the query string, but this gets very awkward to do from the command line - it is much simpler if you can write the query to a text file and use curl's "-d @request.txt" option.

                                  1 of 1 people found this helpful