Connecting Power BI Desktop to Zabbix

The true power of Power BI continues to amaze me. With each monthly release, I learn new ways of unleashing the magic of Power BI. We at Spikefish Solutions have a diverse array of skill sets that we love to share with the IT community. With this post, we’ll show you how to connect Power BI Desktop to Zabbix!

We recently set up Zabbix in our lab to monitor Checkpoint firewalls. As awesome as Zabbix is, we found it necessary to improve on its reporting and visualization capabilities. As Spikefish Solutions’ lead Data Analytics Engineer, I quickly hooked us up with a Power BI solution. This post assumes a basic level of Power BI knowledge. Don’t have that? We have an expert on hand to help you; reach out to us at contact@spikefishsolutions.com.

Alright, let’s dig into the details!

First, you will need to generate a Zabbix token for Power BI to connect to Zabbix. You only need to do this once; the token will be saved and used for future data refreshes.

In Power BI Desktop, open ‘Transform Data’.
(optional) Set up four text parameters: One for your Zabbix user, one for your Zabbix password, one for your Zabbix URL, and one to store your Zabbix token:

pbiimage2 copy.jpg

Create a new blank source and rename it to ‘GetZabbixToken’:

Open the Advanced Editor for ‘GetZabbixToken’ and copy and paste the following code (this code assumes that you are using parameters, as listed above as an optional step). This JSON will make a call to your Zabbix server and retrieve a unique token.

let
    user=User,
    password=Password,
    url=ZabbixURL,
    Body = "{
        ""jsonrpc"": ""2.0"",
        ""method"": ""user.login"",
        ""params"": {
            ""user"":""" & user & """, 
            ""password"":""" & password & """
        },
        ""auth"": null,
        ""id"": 1
    }",
    Source = Json.Document (
        Web.Contents(
            url,
            [
                Headers=[
                    #"Content-Type"="application/json"
                ],
                Content = Text.ToBinary(Body)
            ]
        )
    )
in
    Source

When you close the Advanced editor, your JSON will run. If everything is correct, you should see results similar to the below screen shot, with a token in the result field. If you see ‘error’ in the result field, click it to expand the error and see what’s wrong.

pbiimage4 copy.jpg

Copy the token that was generated above, and paste into your ZabbixToken parameter.
Disable the GetZabbixToken query.

Note: You could also run GetZabbixToken each time, and reference that query instead of using a parameter. I choose the parameter method since you only need to get the token once, and there isn’t a need to generate a new token every time you refresh the data.

Next, we’ll use the token parameter above to retrieve some Zabbix data. For this example, we’ll get the user data. I’ll also provide you a code snippet with the body to get the host inventory data!

Create another blank query, and rename it to ‘ZabbixUsers’. Open the ‘Advanced Editor’, and copy and paste the below code. This will use the token parameter created earlier, and call the Zabbix API method user.get. It will then convert the resulting JSON to a table, and expand the data:

let
    token = ZabbixToken,
    Body =
    "{
        ""jsonrpc"": ""2.0"",
        ""method"": ""user.get"",
        ""params"": {
            ""output"": ""extend""
        },
        ""auth"": """ & token & """,
        ""id"": 1
    }",
    Source = Json.Document(
        Web.Contents(
            ZabbixURL,
            [
                Headers=[
                    #"Content-Type"="application/json"
                ],
                Content = Text.ToBinary(Body)
            ]
        )
    ),
    result = Source[result],
    #"Converted to Table" = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"userid", "alias", "name", "surname", "url", "autologin", "autologout", "lang", "refresh", "type", "theme", "attempt_failed", "attempt_ip", "attempt_clock", "rows_per_page"}, {"userid", "alias", "name", "surname", "url", "autologin", "autologout", "lang", "refresh", "type", "theme", "attempt_failed", "attempt_ip", "attempt_clock", "rows_per_page"})
in
    #"Expanded Column1"

Your results should be a table with all of your Zabbix users: