none
Power Query and Zabbix (json query) RRS feed

  • Question

  • Hello colleagues

    Need help in designing reports for Zabbix system
    I chose to use a method of Power Query for Zabbix API connect

    My query is

    let
    Content1 = "{
    ""jsonrpc"": ""2.0"",
    ""method"":""user.login"",
    ""params"":{""user"":""api"",""password"":""Password""},
    ""auth"": null,
    ""id"":3,
    }",
    Source1 = Web.Contents("http://zabbixsrv/zabbix/api_jsonrpc.php", [Content=Text.ToBinary(Content1)])
    in
    Source1

    But I get error

    DataSource.Error: Web.Contents failed to get contents from 'http://zabbixsrv/zabbix/api_jsonrpc.php' (412): Precondition Failed
    Details:
        http://zabbixsrv/zabbix/api_jsonrpc.php

    When I did test from command shell -  everything is OK

    root@zabbixsrv:~$ curl -i -X POST -H 'Content-Type:application/json' -d'{"jsonrpc": "2.0","method":"user.login","params":{"user":"api","password":"Password"},"auth": null,"id":3}' http://zabbixsrv/zabbix/api_jsonrpc.php
    HTTP/1.1 200 OK
    Date: Fri, 25 Dec 2015 12:28:49 GMT
    Server: Apache/2.4.6 (Linux/SUSE)
    X-Powered-By: PHP/5.4.20
    Access-Control-Allow-Origin: *
    Access-Control-Allow-Headers: Content-Type
    Access-Control-Allow-Methods: POST
    Access-Control-Max-Age: 1000
    Content-Length: 68
    Content-Type: application/json

    {"jsonrpc":"2.0","result":"5121c8e1418d3af1518c4bb586c32347","id":3}root@zabbixsrv:~$

    What could be wrong?

    Thanks



    Friday, December 25, 2015 12:38 PM

Answers

All replies

  • Hi Dmitry, can you look at the request made using Fiddler?
    Monday, December 28, 2015 7:50 PM
    Moderator
  • I found a bug in the code

    extra comma in the line

    Thanks

    let
    Content1 = "{
    ""jsonrpc"": ""2.0"",
    ""method"":""user.login"",
    ""params"":{""user"":""api"",""password"":""Password""},
    ""auth"": null,
    ""id"":3>>,<<
    }",
    Source1 = Web.Contents("http://zabbixsrv/zabbix/api_jsonrpc.php", [Content=Text.ToBinary(Content1)])
    in
    Source1

    Monday, December 28, 2015 8:41 PM
  • I found a bug in the code

    extra comma in the line

    Thanks

    let
    Content1 = "{
    ""jsonrpc"": ""2.0"",
    ""method"":""user.login"",
    ""params"":{""user"":""api"",""password"":""Password""},
    ""auth"": null,
    ""id"":3>>,<<
    }",
    Source1 = Web.Contents("http://zabbixsrv/zabbix/api_jsonrpc.php", [Content=Text.ToBinary(Content1)])
    in
    Source1

    Hi Dmitriy

    After this have you managed to sucessfully build the dashboards?

    I had to make 1 adition to the "Source1 =" line for this to work on Excel 2016 (power query).

    But now I must arrange someway to set up the authentication + following data requests.

    And also to parse the responses that in most part are JSON inside a JSON inside a JSON).

    Any additional help would came in handy!

    Cordial

    Daniel

    Monday, July 4, 2016 8:24 PM
  • Hi Daniel

    I did everything I planned
    In this example, I received tokenID to set up the authentication, then used it for next request:

    let
    ZabbixServer = "http://zabbixsrv/zabbix/api_jsonrpc.php",
    RequestContent = "{
    ""jsonrpc"": ""2.0"",
    ""method"": ""service.getsla"",
    ""params"": {""intervals"": [
    {""from"":""1451606400"",""to"":""1454284800""}
    ]},
    ""auth"": ""{tokenID}"",
    ""id"": 3
    }",
    Options = [Headers = [#"Content-Type" = "application/json"],Content = Text.ToBinary(RequestContent)],
    ImportedJSON = Json.Document(Web.Contents(ZabbixServer, Options)),
        result = ImportedJSON[result],
        #"Converted to Table" = Record.ToTable(result),
        #"Expand Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"status", "problems", "sla"}, {"Value.status", "Value.problems", "Value.sla"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expand Value",{"Value.problems"})
    in
        #"Removed Columns"

    Thursday, September 1, 2016 8:37 AM
  • Hi Dmitriy, 

    in the beginning of your post you put "I received tokenID to set up the authentication", how can I do that, can you show a example of that? 

    Friday, February 15, 2019 11:31 AM
  • Hi Dmitriy, 

    in the beginning of your post you put "I received tokenID to set up the authentication", how can I do that, can you show a example of that? 

    Use this and will work for you, as worked for me. @UmSaldanha

    ______________________________________________________________________________

    let
        url = "http://IpAddress/zabbix/api_jsonrpc.php",
        body = "{
          ""jsonrpc"": ""2.0"",
          ""method"": ""user.login"",
          ""params"": {
            ""user"": ""YourUser"",
            ""password"": ""YourPassword""
          },
          ""id"": ""1"",
          ""auth"": null
        }",
       Source  = Json.Document(Web.Contents(url, [Headers = [#"Content-Type"="application/json"], Content=Text.ToBinary(body)]))

    in
       Source

    __________________________________________________________

    This powerquery will return the token of your api.

    Friday, November 8, 2019 11:55 AM