[ZABBIX] SQL DB2 cash windows - Заметки Эникейщика

дополнение к https://7d3.ru/wiki/776.

Теперь у нас имеется db2 + виндовый сервак. Поэтому юзаем powershell + zabbix

Создаем VIEW на BD. Zabbix не преобразует пустое значение в 0 (когда вью ничего не вернула). Поэтому, надо написать такую вьюху, которая возвращала бы 0 вместо "нет результатов"
Пример:

CREATE OR REPLACE VIEW mon.events_distinct_customer_platform AS
WITH event_distinct_customer_platform_a(platform, "COUNT") AS (
основной SELECT
)
SELECT s.platform,
COALESCE(( SELECT a."COUNT"
FROM event_distinct_customer_platform_a a
WHERE a.platform::text = s.platform), 0) AS "COUNT"
FROM ( дополнительный SELECT с необходимыми значениями для заббикса) s(platform);

CREATE OR REPLACE VIEW mon.events_distinct_customer_platform AS

WITH event_distinct_customer_platform_a(platform, "COUNT") AS (

основной SELECT

)

SELECT s.platform,

COALESCE(( SELECT a."COUNT"

FROM event_distinct_customer_platform_a a

WHERE a.platform::text = s.platform), 0) AS "COUNT"

FROM ( дополнительный SELECT с необходимыми значениями для заббикса) s(platform);

Наша вьюха:

CREATE OR REPLACE VIEW mon.events_distinct_customer_platform AS
WITH event_distinct_customer_platform_a(platform, "COUNT") AS (
SELECT ep.value,
count(DISTINCT e.customer_id) AS count
FROM auth.event e
JOIN auth.event_param ep ON e.id = ep.event_id
WHERE ep.param = 'platform' AND e.creation > CURRENT TIMESTAMP - 5 MINUTE WITH ur
GROUP BY ep.value
)
SELECT s.platform,
COALESCE(( SELECT a."COUNT"
FROM event_distinct_customer_platform_a a
WHERE a.platform = s.platform), 0::bigint) AS "COUNT"
FROM ( VALUES ('WEB'), ('ANDROID'), ('IOS')) s(platform);

CREATE OR REPLACE VIEW mon.events_distinct_customer_platform AS

WITH event_distinct_customer_platform_a(platform, "COUNT") AS (

SELECT ep.value,

count(DISTINCT e.customer_id) AS count

FROM auth.event e

JOIN auth.event_param ep ON e.id = ep.event_id

WHERE ep.param = 'platform' AND e.creation > CURRENT TIMESTAMP - 5 MINUTE WITH ur

GROUP BY ep.value

)

SELECT s.platform,

COALESCE(( SELECT a."COUNT"

FROM event_distinct_customer_platform_a a

WHERE a.platform = s.platform), 0::bigint) AS "COUNT"

FROM ( VALUES ('WEB'), ('ANDROID'), ('IOS')) s(platform);

Можно сразу задать application и description для discovery zabbix. Тогда вьюха будет выглядеть так

CREATE OR REPLACE VIEW mon.events_distinct_customer_platform AS
WITH event_distinct_customer_platform_a(platform, "COUNT") AS (
SELECT ep.value,
count(DISTINCT e.customer_id) AS count
FROM auth.event e
JOIN auth.event_param ep ON e.id = ep.event_id
WHERE ep.param = 'platform' AND e.creation > CURRENT TIMESTAMP - 5 MINUTE WITH ur
GROUP BY ep.value
)(
        SELECT
            'Applications_name',
            'WEB',
            COUNT(*),
            'описание для айтема WEB'
        FROM
            event_distinct_customer_platform_a
        WHERE
            platform = 'WEB'
    UNION SELECT
            'Applications_name',
            'ANDROID',
            COUNT(*),
            'описание для айтема ANDROID'
        FROM
            event_distinct_customer_platform_a
        WHERE
            platform = 'ANDROID'
     UNION SELECT
            'Applications_name',
            'IOS',
            COUNT(*),
            'описание для айтема IOS'
        FROM
            event_distinct_customer_platform_a
        WHERE
            platform = 'IOS'

    )

CREATE OR REPLACE VIEW mon.events_distinct_customer_platform AS

WITH event_distinct_customer_platform_a(platform, "COUNT") AS (

SELECT ep.value,

count(DISTINCT e.customer_id) AS count

FROM auth.event e

JOIN auth.event_param ep ON e.id = ep.event_id

WHERE ep.param = 'platform' AND e.creation > CURRENT TIMESTAMP - 5 MINUTE WITH ur

GROUP BY ep.value

)(

SELECT

'Applications_name',

'WEB',

COUNT(*),

'описание для айтема WEB'

FROM

event_distinct_customer_platform_a

WHERE

platform = 'WEB'

UNION SELECT

'Applications_name',

'ANDROID',

COUNT(*),

'описание для айтема ANDROID'

FROM

event_distinct_customer_platform_a

WHERE

platform = 'ANDROID'

UNION SELECT

'Applications_name',

'IOS',

COUNT(*),

'описание для айтема IOS'

FROM

event_distinct_customer_platform_a

WHERE

platform = 'IOS'

)

на standby сервере DB или ином другом устанавливаем драйвер и каталогизируем базу.

db2 catalog tcpip node <NODE_NAME> remote <SERVER_NAME> server <PORT> SYSTEM <SERVER_NAME> OSTYPE WIN
db2 catalog DB <DATABASE_NAME> AS <DATABASE_ALIAS> AT NODE <NODE_NAME>
db2 catalog system odbc data source <DATABASE_NAME>
<NODE_NAME> - имя ноды
<DATABASE_NAME> - имя базы данных
<SERVER_NAME> - имя сервера, где расположена база
<PORT> - порт (по умолчанию 50000)

db2 catalog tcpip node <NODE_NAME> remote <SERVER_NAME> server <PORT> SYSTEM <SERVER_NAME> OSTYPE WIN

db2 catalog DB <DATABASE_NAME> AS <DATABASE_ALIAS> AT NODE <NODE_NAME>

db2 catalog system odbc data source <DATABASE_NAME>

<NODE_NAME> - имя ноды

<DATABASE_NAME> - имя базы данных

<SERVER_NAME> - имя сервера, где расположена база

<PORT> - порт (по умолчанию 50000)

Проверяем подключение и сохраняем пароль в Data Sources (ODBC)

Скрипт исполнения запроса. D:\scripts\zabbix\get_data.ps1
get_data.ps1

#Author: CTAPOMAK

#Params
#-view mon.events_distinct_customer_platform

param ( $view = "ERROR" )

if ($view -eq "ERROR"){
  echo("view param not found")
  exit
}

function Get-ODBC-Data{
   param([string]$query=$(throw 'query is required.'))
   $conn = New-Object System.Data.Odbc.OdbcConnection
   $conn.ConnectionString = "DSN=DB_NAME;"
   $conn.open()
   $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)
   $ds = New-Object system.Data.DataSet
   (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
   $conn.close()
   $ds.Tables[0]
}

function Set-ODBC-Data{
   param([string]$query=$(throw 'query is required.'))
  $conn = New-Object System.Data.Odbc.OdbcConnection
  $conn.ConnectionString= "DSN=DB_NAME;"
  $cmd = new-object System.Data.Odbc.OdbcCommand($query,$conn)
  $conn.open()
  $cmd.ExecuteNonQuery()
  $conn.close()
}

function get_data{
  $query = "SELECT * FROM $view"
  #создаем лок файл
  ([int][double]::Parse((Get-Date -UFormat %s))) > $lockFile
  $result = Get-ODBC-Data -query $query
  $result|  ConvertTo-Csv  -NoType -delimiter "|"| Select-Object -Skip 1 | Foreach-Object {$_ -replace '^"', ''}| Foreach-Object {$_ -replace '"$', ''}| Foreach-Object {$_ -replace '"\|"', '|'} > $extractFile
  #удаляем лок файл
  Remove-Item $lockFile
}

$extractFile="$view.stat"
$lockFile="$view.lock"

if((Test-Path $lockFile) -eq "True") {
  $date_now=[int][double]::Parse((Get-Date -UFormat %s))
  $date_stats=Get-Content $lockFile
  if([string]::IsNullOrWhiteSpace($date_stats)){
    $date_stats=0
  }
  $date_rusult=$date_now - $date_stats
  if($date_rusult -gt 1200){
    get_data
  }else{
    echo "work"
    exit
  }
}else{
  get_data
}

#Author: CTAPOMAK

#Params

#-view mon.events_distinct_customer_platform

param ( $view = "ERROR" )

if ($view -eq "ERROR"){

echo("view param not found")

exit

}

function Get-ODBC-Data{

param([string]$query=$(throw 'query is required.'))

$conn = New-Object System.Data.Odbc.OdbcConnection

$conn.ConnectionString = "DSN=DB_NAME;"

$conn.open()

$cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)

$ds = New-Object system.Data.DataSet

(New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null

$conn.close()

$ds.Tables[0]

}

function Set-ODBC-Data{

param([string]$query=$(throw 'query is required.'))

$conn = New-Object System.Data.Odbc.OdbcConnection

$conn.ConnectionString= "DSN=DB_NAME;"

$cmd = new-object System.Data.Odbc.OdbcCommand($query,$conn)

$conn.open()

$cmd.ExecuteNonQuery()

$conn.close()

}

function get_data{

$query = "SELECT * FROM $view"

#создаем лок файл

([int][double]::Parse((Get-Date -UFormat %s))) > $lockFile

$result = Get-ODBC-Data -query $query

$result| ConvertTo-Csv -NoType -delimiter "|"| Select-Object -Skip 1 | Foreach-Object {$_ -replace '^"', ''}| Foreach-Object {$_ -replace '"$', ''}| Foreach-Object {$_ -replace '"\|"', '|'} > $extractFile

#удаляем лок файл

Remove-Item $lockFile

}

$extractFile="$view.stat"

$lockFile="$view.lock"

if((Test-Path $lockFile) -eq "True") {

$date_now=[int][double]::Parse((Get-Date -UFormat %s))

$date_stats=Get-Content $lockFile

if([string]::IsNullOrWhiteSpace($date_stats)){

$date_stats=0

}

$date_rusult=$date_now - $date_stats

if($date_rusult -gt 1200){

get_data

}else{

echo "work"

exit

}

}else{

get_data

}

скрипт возвращает возможные значения айтемов. D:\scripts\zabbix\stat_items.ps1
stat_items.ps1

#Author: CTAPOMAK

#Params
#-view mon.events_distinct_customer_platform

param ( $view = "ERROR" )

if ($view -eq "ERROR"){
  echo("view param not found")
  exit
}

$extractFile="D:\scripts\zabbix\$view.stat"

if((Test-Path $extractFile) -eq "True") {
  echo '{"data":['
  $text=""
  foreach ($item in get-content $extractFile){
    $item = $item.Split("|")
    #echo $item[0];
	$text=$text+'{"{#APPLICATIONNAME}":"'+$item[0]+'", "{#ITEMNAME}":"'+$item[1]+'", "{#DESCRIPTIONNAME}":"'+$item[3]+'"},'
  }
  echo $text.TrimEnd(",")
  echo "]}"
}else{
  echo "ERROR: $extractFile not found"
}

#Author: CTAPOMAK

#Params

#-view mon.events_distinct_customer_platform

param ( $view = "ERROR" )

if ($view -eq "ERROR"){

echo("view param not found")

exit

}

$extractFile="D:\scripts\zabbix\$view.stat"

if((Test-Path $extractFile) -eq "True") {

echo '{"data":['

$text=""

foreach ($item in get-content $extractFile){

$item = $item.Split("|")

#echo $item[0];

$text=$text+'{"{#APPLICATIONNAME}":"'+$item[0]+'", "{#ITEMNAME}":"'+$item[1]+'", "{#DESCRIPTIONNAME}":"'+$item[3]+'"},'

}

echo $text.TrimEnd(",")

echo "]}"

}else{

echo "ERROR: $extractFile not found"

}

скрипт возвращает значения айтемов. D:\scripts\zabbix\stat_items_data.ps1
stat_items_data.ps1

#Author: CTAPOMAK

#Params
#-view mon.events_distinct_customer_platform -itemname WEB

param ( $view = "ERROR", $itemname = "ERROR" )

if ($view -eq "ERROR"){
  echo("view param not found")
  exit
}
if ($itemname -eq "ERROR"){
  echo("itemname param not found")
  exit
}

get-content D:\scripts\zabbix\$view.stat | Select-String "\|$itemname\|" | %{($_ -split "\|")[2]}

#Author: CTAPOMAK

#Params

#-view mon.events_distinct_customer_platform -itemname WEB

param ( $view = "ERROR", $itemname = "ERROR" )

if ($view -eq "ERROR"){

echo("view param not found")

exit

}

if ($itemname -eq "ERROR"){

echo("itemname param not found")

exit

}

get-content D:\scripts\zabbix\$view.stat | Select-String "\|$itemname\|" | %{($_ -split "\|")[2]}

Шедулер
Zabbix. Настройка описана в основной статье. Повторять не имеет смысла. Есть одно лишь отличие - мы добавили во вьюху описание айтема и наименование приложения. Скрипт отдает для них переменные
{#APPLICATIONNAME} {#ITEMNAME} {#DESCRIPTIONNAME}
их можно использовать при создании прототипов item. APPLICATIONNAME необходимо добавлять в поле New application prototype

Добавить комментарий Отменить ответ