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!

Get your Zabbix Token

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:

• 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.

• 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.

Get Zabbix 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:

You can also get information on the Zabbix host inventory by calling host.get, and setting the ‘selectInventory’ parameter to ‘true’:

let
    token = ZabbixToken,
    Body =
    "{
        ""jsonrpc"": ""2.0"",
        ""method"": ""host.get"",
        ""params"": {
            ""selectInventory"":true,
            ""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", {"hostid", "proxy_hostid", "host", "status", "disable_until", "error", "available", "errors_from", "lastaccess", "ipmi_authtype", "ipmi_privilege", "ipmi_username", "ipmi_password", "ipmi_disable_until", "ipmi_available", "snmp_disable_until", "snmp_available", "maintenanceid", "maintenance_status", "maintenance_type", "maintenance_from", "ipmi_errors_from", "snmp_errors_from", "ipmi_error", "snmp_error", "jmx_disable_until", "jmx_available", "jmx_errors_from", "jmx_error", "name", "flags", "templateid", "description", "tls_connect", "tls_accept", "tls_issuer", "tls_subject", "tls_psk_identity", "tls_psk", "proxy_address", "auto_compress", "discover", "inventory_mode", "inventory"}, {"hostid", "proxy_hostid", "host", "status", "disable_until", "error", "available", "errors_from", "lastaccess", "ipmi_authtype", "ipmi_privilege", "ipmi_username", "ipmi_password", "ipmi_disable_until", "ipmi_available", "snmp_disable_until", "snmp_available", "maintenanceid", "maintenance_status", "maintenance_type", "maintenance_from", "ipmi_errors_from", "snmp_errors_from", "ipmi_error", "snmp_error", "jmx_disable_until", "jmx_available", "jmx_errors_from", "jmx_error", "name", "flags", "templateid", "description", "tls_connect", "tls_accept", "tls_issuer", "tls_subject", "tls_psk_identity", "tls_psk", "proxy_address", "auto_compress", "discover", "inventory_mode", "inventory"}),
    #"FILTER: inventory_mode = 1" = Table.SelectRows(#"Expanded Column1", each ([inventory_mode] = "1")),
    #"Expanded inventory" = Table.ExpandRecordColumn(#"FILTER: inventory_mode = 1", "inventory", {"type", "type_full", "name", "alias", "os", "os_full", "os_short", "serialno_a", "serialno_b", "tag", "asset_tag", "macaddress_a", "macaddress_b", "hardware", "hardware_full", "software", "software_full", "software_app_a", "software_app_b", "software_app_c", "software_app_d", "software_app_e", "contact", "location", "location_lat", "location_lon", "notes", "chassis", "model", "hw_arch", "vendor", "contract_number", "installer_name", "deployment_status", "url_a", "url_b", "url_c", "host_networks", "host_netmask", "host_router", "oob_ip", "oob_netmask", "oob_router", "date_hw_purchase", "date_hw_install", "date_hw_expiry", "date_hw_decomm", "site_address_a", "site_address_b", "site_address_c", "site_city", "site_state", "site_country", "site_zip", "site_rack", "site_notes", "poc_1_name", "poc_1_email", "poc_1_phone_a", "poc_1_phone_b", "poc_1_cell", "poc_1_screen", "poc_1_notes", "poc_2_name", "poc_2_email", "poc_2_phone_a", "poc_2_phone_b", "poc_2_cell", "poc_2_screen", "poc_2_notes"}, {"inventory.type", "inventory.type_full", "inventory.name", "inventory.alias", "inventory.os", "inventory.os_full", "inventory.os_short", "inventory.serialno_a", "inventory.serialno_b", "inventory.tag", "inventory.asset_tag", "inventory.macaddress_a", "inventory.macaddress_b", "inventory.hardware", "inventory.hardware_full", "inventory.software", "inventory.software_full", "inventory.software_app_a", "inventory.software_app_b", "inventory.software_app_c", "inventory.software_app_d", "inventory.software_app_e", "inventory.contact", "inventory.location", "inventory.location_lat", "inventory.location_lon", "inventory.notes", "inventory.chassis", "inventory.model", "inventory.hw_arch", "inventory.vendor", "inventory.contract_number", "inventory.installer_name", "inventory.deployment_status", "inventory.url_a", "inventory.url_b", "inventory.url_c", "inventory.host_networks", "inventory.host_netmask", "inventory.host_router", "inventory.oob_ip", "inventory.oob_netmask", "inventory.oob_router", "inventory.date_hw_purchase", "inventory.date_hw_install", "inventory.date_hw_expiry", "inventory.date_hw_decomm", "inventory.site_address_a", "inventory.site_address_b", "inventory.site_address_c", "inventory.site_city", "inventory.site_state", "inventory.site_country", "inventory.site_zip", "inventory.site_rack", "inventory.site_notes", "inventory.poc_1_name", "inventory.poc_1_email", "inventory.poc_1_phone_a", "inventory.poc_1_phone_b", "inventory.poc_1_cell", "inventory.poc_1_screen", "inventory.poc_1_notes", "inventory.poc_2_name", "inventory.poc_2_email", "inventory.poc_2_phone_a", "inventory.poc_2_phone_b", "inventory.poc_2_cell", "inventory.poc_2_screen", "inventory.poc_2_notes"})
in
    #"Expanded inventory"

Your result should look like this:

As an example of what data you can retrieve from Zabbix, here’s the data we’re using:

Visualizations

Finally, we’ll visualize the data to view the inventory and recent problems that occurred in Zabbix:

Summary

You can use the above examples as a base to get any data available from the Zabbix API. Zabbix provides great documentation on all the API methods and parameters here: https://www.zabbix.com/documentation/current/manual/api/reference (make sure you’re looking at the documentation for your version of Zabbix).

Want more information on how to set up Zabbix? See Devin Marks blog post here: https://www.spikefishsolutions.com/post/monitoring-check-point-infrastructure-with-zabbix

Need a tool to automatically populate Zabbix with your Check Point infrastructure? Check out our free tool, Pointix, on GitHub! https://github.com/SpikefishSolutions/Pointix

You can reach out to us at contact@spikefishsolutions.com if you have questions, or need tools customized for your needs; we have MCSE’s specializing in SQL Server and Power BI ready to assist you. We also really like memes and cute animal pictures, so go ahead and show us what you have on Instagram, follow us @teamspikefish!

Thanks for following! Let us know if you have ideas for future blogs.