Power BI - 11 - Zabbix API を Power Query で使ってみる

Web.Contents (Power Query) を使うとコンテンツのダウンロードができる。バイナリコンテンツを加工することで必要なデータにすることができるのだけど、POST が必要な Webサービスもあるので。

Web.Contents(url as text, optional options as nullable record) as binary

引数 "options" を使ってリクエスト時に Content などを渡すとよい。そこで、Zabbix を使うことがあったので、Power Query + Zabbix API を試してみた。なお、Zabbix API は JSON-RPC というプロトコルなので、JSON 形式のパラメータを送信 / JSON 形式で結果を受信する。Content-Type は application/json-rpc。

例えば、Zabbix API のバージョンを得るためのリクエストは、

{
    "jsonrpc": "2.0",
    "method": "apiinfo.version",
    "params": [],
    "id": 1
}

こんな感じなので、Power Query では

let
    ZabbixServer = "http://Server/zabbix/api_jsonrpc.php",
    RequerstJSON = Text.Format(
         "{""jsonrpc"":""2.0"",""method"":""apiinfo.version"",""id"":#{0}}",
         {Number.Round(Number.RandomBetween(1,9999))}
    ),
    Options = [Headers = [#"Content-Type" = "application/json-rpc"],
               Content = Text.ToBinary(RequerstJSON)],
    Response = Web.Contents(ZabbixServer, Options),
    ImportedJSON = try Json.Document(Response),
    Result = if ImportedJSON[HasError] 
             then ImportedJSON[Error]
             else if ImportedJSON[Value][id] = null 
                  then ImportedJSON[Value][error]
                  else ImportedJSON[Value]
in
    Result

で、user.login と user.logout を UDF にしておいて使っておこうかなと。

(optional UserName as text, optional Password as text, optional Id as number) =>
let
    ZabbixServer = "http://Server/zabbix/api_jsonrpc.php",
    Id = if Id = null then 1 else Id,
//  Id = if Id = null then Number.Round(Number.RandomBetween(1,9999)) else Id,
    _UserName = if UserName = null or UserName = "" then "Admin" else UserName,
    _Password = if UserName = null or UserName = "" then "zabbix" else Password,
    RequestContent = Text.Format(
        "{ ""jsonrpc"":""2.0"",""method"":""user.login"",""params"":{""user"":""#{0}"",""password"":""#{1}""},""id"":#{2}}",
        {_UserName, _Password, Id}),
    Options = [Headers = [#"Content-Type" = "application/json-rpc"],
               Content = Text.ToBinary(RequestContent)],
    ImportedJSON = Json.Document(Web.Contents(ZabbixServer, Options)),
    Status = if ImportedJSON[error]? = null 
             then "OK"
             else ImportedJSON[error][data],
    AddStatus = Record.AddField(ImportedJSON, "Status", Status),
    AddServer = Record.AddField(AddStatus, "ZabbixServer", ZabbixServer),
    RenameFields = Record.RenameFields(AddServer,
                                       {{"result","Token"},{"id", "Id"}},
                                       MissingField.UseNull),
    RemoveFields = Record.RemoveFields(RenameFields,
                                       {"jsonrpc","error"},
                                       MissingField.UseNull),
    ReorderFields = Record.ReorderFields(RemoveFields,
                                         {"Id", "Token", "Status", "ZabbixServer"})
in
    ReorderFields

(Id as number, Token as text, ZabbixServer as text) =>
let
    RequestContent = Text.Format(
        "{""jsonrpc"":""2.0"",""method"":""user.logout"",""id"":#{0},""auth"":""#{1}""}",
         {Id, Token}),
    Options = [Headers = [#"Content-Type" = "application/json-rpc"],
               Content = Text.ToBinary(RequestContent)],
    ImportedJSON = Json.Document(Web.Contents(ZabbixServer, Options)),
    Status = if ImportedJSON[error]? = null
             then "OK"
             else ImportedJSON[error][data]
in
    Status

ユーザグループをこんな感じで取得

let
    LoginResult = fn_UserLogin(),
    ContentJSON = Text.Format(
        "{""jsonrpc"":""2.0"",""method"":""usergroup.get"",""params"":{""output"":""extend""},""auth"":""#[Token]"",""id"":#[Id]}",
        LoginResult),
    Options = [Headers = [#"Content-Type" = "application/json-rpc"],
               Content = Text.ToBinary(ContentJSON)],
    Request = Web.Contents(LoginResult[ZabbixServer], Options),
    ImportedJSON = Json.Document(Request),
    Result = ImportedJSON[result],
    LogoutResult = fn_UserLogout(
        LoginResult[Id],
        LoginResult[Token],
        LoginResult[ZabbixServer]),
    ConvertedToTable = Table.FromList(
        Result,
        Splitter.SplitByNothing(),
        null, null,
        ExtraValues.Error),
    ExpandedColumn = Table.ExpandRecordColumn(
        ConvertedToTable,
        "Column1",
        {"usrgrpid", "name", "gui_access", "users_status", "debug_mode"},
        {"usrgrpid", "name", "gui_access", "users_status", "debug_mode"})
in
    ExpandedColumn

history.get や trigger.get で対象のデータをExcelに取り込むとかはさほど問題なく。Excelテーブルにある元データから host.create や user.create で新規作成したりすることもできるはずだけど、データがないのでそのうち試そう。