Download Zabbix Graph chart through PowerShell

Keywords: Linux Zabbix Database MySQL PHP

Generally, the chart of Zabbix Graph page is mostly used to write reports, forming a more intuitive report.
However, if there are a large number of charts to view, it will lead to screenshots for each item of each host, which is inefficient. This time, I specially wrote a script to complete this work.

In order to understand, I set up a zabbix test environment, and set its IP address to 192.1.31.66. This IP address in this article refers to zabbix server in particular. Of course, this operation also involves mysql databases, which are generally installed on the same host as the Server. Of course, it may be different hosts. In this article, they are put together.

We can see that there is a picture of the chart under the Graphs interface. You can use chrome to open the picture separately in the new label. You may get the address of a web page.
For example: http://192.1.31.66/zabbix/chart2.php?graphid=919&from=now-1h&to=now&profileIdx=web.graphs.filter&profileIdx2=919&width=1243&height=201&_=u5ptsndf&screenid=

You can see that this picture is generated by php page. These are the valid parameters. period,graphid,width,height.
Where period is filled with a number that represents the number of seconds of the length of time shown in the entire table. For example, if a day has 86400 seconds, then to view the data of a day, you need to fill in 86400.
Width and height are the length and width of the image, note that the width does not include the lower annotation section.
And graphic id is the most important thing for this time. Each host item will have a graphic id. after entering a different id in the website, different graphs will be displayed. Until I find that the id will repeat.

Reference documents: https://www.cnblogs.com/dreamer-fish/p/5485556.html
The author has written well in the above. The author explained the origin of graphid in detail, but he did not write out a specific query code. It's just a demonstration. The graphid is generated in the database.
Database name: zabbix

The relationship is: first, there is a hostname, which is the hostname configured in the ZABBIX agent configuration file.
The hostname can be retrieved directly from the database. The command taken out is:

select host from hosts;

This hostname will correspond to a hostid in the database.

select hostid from hosts where host='Zabbix Server';

Then according to the hostid and the "key" (key name) of the corresponding item can be obtained. The key name can be seen in the web page. For example, I want to find a CPU utilization.

I'll find the items in the picture first,

An example of the query command is as follows. The retrieved command is itemid.

select itemid from items where hostid=10086 and key_=vm.memory.size[free];"

According to this item ID, you can find that not all items have charts. For example, agent.hostname has no graphid.

select graphid from graphs_items where itemid=2330;

Finally, I got the graphic ID. now, what I need to do is to access the php website through the web page (the web page data is a png picture), and then download the picture. This picture must be submitted for a cookie access, otherwise the picture will have no data, and only a sentence printed by the red font "no permission".

This cookie, called "zbx_sessionid", can be obtained through chrome packet capturing.

Then download the picture directly to this computer.

I intend to use powershell to complete the whole operation. First, there must be a way for powershell to access mysql database.
Reference documents: https://www.cnblogs.com/zhr1217/p/9686514.html
The above article contains the entire access code. We need to go to MySQL's official website to download the whole component, but only need to install. Net connector 8.0, and only need a DLL file MySQL.Data.dll.

Here is the code to download the picture.

#Written By Heartnight
function ConvertTo-Base64String([string]$string)
{
    $byteArray = [System.Text.UnicodeEncoding]::Unicode.GetBytes($string)
    [Convert]::ToBase64String( $byteArray )
}

function ConvertFrom-Base64String([string]$string)
{
    $byteArray = [Convert]::FromBase64String($string)
    [System.Text.UnicodeEncoding]::Unicode.GetString($byteArray)
}

function Get-DaySecond([int]$Day)
{
return $Day * 24 * 3600
}

$mySQLDataDLL = "C:\MySQL.Data.dll"
if (-not (Test-Path -Path $mySQLDataDLL))
{
    return "MySQL.Data.dll is missing."
}

[void][system.reflection.Assembly]::LoadFrom($mySQLDataDLL)

function MySQL-GetData([string]$querysql)
{
if($connectionStr -eq $null){
    "Error: Empty database information."
    $DBhostname = Read-Host "Please input database hostname:<ip>"
    $DBusername = Read-Host "Please input database username:"
    $DBpassword = Read-Host "Please input database password:"
    $DBname = Read-Host "Please input database name:"
    $connectionStr = "Server="+$DBhostname+";Uid="+$DBusername+";Pwd="+$DBpassword+";database="+$DBname+";"

}

$connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$connection.ConnectionString = $connectionStr
$connection.Open()

$command = New-Object MySql.Data.MySqlClient.MySqlCommand($querysql, $connection)
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
$dataSet = New-Object System.Data.DataSet
$recordCount = $dataAdapter.Fill($dataSet)
$table = $dataSet.Tables[0]
return $table
}

function Zabbix-DownloadPic([string]$Zabbix_agent_hostname,[string]$Zabbix_Item,[string]$PicSavePath)
{

if($Zabbix_cookies -eq $null)
{
    $Zabbix_cookies = Read-Host "Please input cookies:"
}

$hostid = (MySQL-GetData("select hostid from hosts where host='"+$Zabbix_agent_hostname+"';")).hostid

if($hostid -eq $null)
{
    Write-Host "Error: hostid is empty. Current Server: "$Zabbix_agent_hostname
    return 1
}

$itemid = (MySQL-GetData("select itemid from items where hostid="+$hostid+" and key_='"+$Zabbix_Item+"';")).itemid
if($itemid -eq $null)
{
    Write-Host "Error: itemid is empty. Current Server: "$Zabbix_agent_hostname
    Write-Host "Please check your zabbix_item. Current is :"$Zabbix_Item
    return 2
}

$graphid = (MySQL-GetData("select graphid from graphs_items where itemid="+$itemid+";")).graphid
if($graphid -eq $null)
{
    Write-Host "Error: graphid is empty. Current Server: "$Zabbix_agent_hostname
    return 3
}

$url = "http://"+$Zabbix_Server_Hostname+"/zabbix/chart2.php?graphid="+$graphid+"&period="+(get-daysecond(90)).tostring()

$session = New-Object Microsoft.PowerShell.Commands.WebRequestSession
$cookie = New-Object System.Net.Cookie 

$cookie.Name = "zbx_sessionid"
$cookie.Value = $Zabbix_cookies
$cookie.Domain = $Zabbix_Server_Hostname

$session.Cookies.Add($cookie);

Invoke-WebRequest $url -WebSession $session -TimeoutSec 10 -OutFile $PicSavePath

Write-Host $Zabbix_agent_hostname" sucessfully saved as "$PicSavePath
return 0
}

$Zabbix_Server_Hostname = "192.1.31.66"
$MySQLpwd_Base64 = "MQAyADMANAA1ADYA"
$connectionStr = "Server="+$Zabbix_Server_Hostname+";Uid=zabbix;Pwd="+(ConvertFrom-Base64String($MySQLpwd_Base64))+";database=zabbix;"

$Zabbix_hostname_csv = Import-Csv .\list.csv
$Zabbix_cookies = "b111b7e49d6fef47c91d41384ae8f5ea"
$Zabbix_Item = "system.cpu.load[all,avg1]"
#vm.memory.size[free]

for( $i=0;$i -lt $Zabbix_hostname_csv.zabbixhost.count;$i++)
{
$Zabbix_agent_hostname = $Zabbix_hostname_csv[$i].zabbixhost

$PicSavePath = "C:\test\"+($Zabbix_agent_hostname -split " ")[0]+".png"

$result = Zabbix-DownloadPic $Zabbix_agent_hostname $Zabbix_Item $PicSavePath
    if($result -ne 0)
    {
    continue
    } 
}

The script will find a file called list.csv in the same directory, read the hostname list, query the database and download the pictures.
In this script, the following content changes according to the actual situation:

$mySQLDataDLL = "C:\MySQL.Data.dll"
Fill in the path where you actually store the dll. The file must exist.

$Zabbix_Server_Hostname = "192.1.31.66"
zabbix server ip address

$MySQLpwd_Base64 = "MQAyADMANAA1ADYA"
The password of zabbix mysql must be encrypted with base64 method. Encrypted function has been provided in the script. Usage:

ConvertTo-Base64String("123456")

$connectionStr = "Server="+$Zabbix_Server_Hostname+";Uid=zabbix;Pwd="+(ConvertFrom-Base64String($MySQLpwd_Base64))+";database=zabbix;"
This variable determines which database you are using. The default database is usually installed with the zabbix server. Of course, it depends on the actual situation. The default server is zabbix server, the user name is zabbix, the password is determined by $MySQL pwd_base64, and the database accessed by default is zabbix.

$Zabbix_hostname_csv = Import-Csv .\list.csv
The file list of the host name must be filled in with ZABBIX ﹣ agent ﹣ hostname. It must be completed in the following format:

zabbixhost
<hostname1>
<hostname2>
...

The file must exist.

$Zabbix_cookies = "b111b7e49d6fef47c91d41384ae8f5ea"
Fill in the zabbix cookie.

$Zabbix_Item = "system.cpu.load[all,avg1]"
Fill in the name of the item to be queried.

Before that, I wrote a vbs that can submit cookie s and download pictures, but later, because pictures can be downloaded in Powershell, I abandoned this script.

Set wshnamed=wscript.arguments.named
strGraphID = wshnamed.item("graphid")
strPicSavePath = wshnamed.item("PicSavePath")
strCookies = wshnamed.item("Cookies")

zabbix_url = "192.1.31.66"

Sub DownloadZabbixPic(url,strPath,cookie)
    Set Winhttp = CreateObject("WinHttp.WinHttpRequest.5.1") 
    Winhttp.Open "GET", url
    Winhttp.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded" 
    If(cookie <> "")then
        Winhttp.SetRequestHeader "Cookie",cookie
    End If
    Winhttp.Send

    Set sGet = CreateObject("ADODB.Stream")
    sGet.Mode = 3
    sGet.Type = 1
    sGet.Open()
    sGet.Write(Winhttp.ResponseBody)
    sGet.SaveToFile strPath
End Sub

'zbx_sessionid=b111b7e49d6fef47c91d41384ae8f5ea;
Function GetDaySecond(Day)
    GetDaySecond = Day * 24 * 3600
End Function

DownloadZabbixPic "http://" & zabbix_url & "/zabbix/chart2.php?graphid=" & strGraphID & "&period=" & GetDaySecond(30),strPicSavePath,strCookies

Its calling method is:

cscript .\zabbix_post.vbs //Nologo /GraphID:$graphid /PicSavePath:$PicSavePath /Cookies:$Zabbix_cookies

You can call it in powershell.

In order for this script to run normally, you must open the mysql database with full remote access.

grant select on *.* to zabbix@<ip> identified by '123456' with grant option;
FLUSH PRIVILEGES;

The IP part is the IP of the host running the powershell script

Posted by Karlos2394 on Fri, 20 Mar 2020 02:24:48 -0700