Как настроить мониторинг любых бизнес-процессов, в БД Oracle + построение графиков, используя бесплатную версию Grafana

Вводные. Зачем мне это было нужно

Лично мне нужно было организовать мониторинг домашней солнечной электростанции.

Кратко о матчасти (хотя этот пост не про неё):

  • Инвертор МАП Энергия и 3 солнечных контроллера того же производителя.

  • Внутри инвертора установлен микрокомпьютер (производитель его называет “Малина”), который кое-что умеет в плане мониторинга, но не всё что мне нужно, и не очень удобно. Ценность микрокомпьютера в том, что он снимает данные с com-портов инвертора и контроллеров и публикует их насвоём http-сервере в виде Json. Данные веб-сервисов обновляются примерно каждую секунду. Также есть веб-сервисы для управления встроенными в контроллеры и инвертор реле

  • Парочка Ethernet-устройств SR-201 это такие платы с релюхами, используются для управления нагрузкой и кое-чем еще, управляются по протоколу tcp и udp.

  • Домашний сервер под управлением Centos-8, на нём установлен Oracle (разумеется Express Edition со всеми своими ограничениями, но для домашнего сервера достаточно)

  • В оракле крутятся 2 JOBa (на самом деле это persistent процессы, которые крутят бесконечный цикл и перезапускаются примерно раз в полчаса):

    1. Раз в секуну снимает данные с вебсервисов “Малины”, текущее состояние реле устройств SR-201 и пишет это всё в БД Oracle. С Малины снимает с помощью несложных функций на основе utl_http, с реюх – через utl_tcp. Собственно это и есть статистика, которую будем мониторить

    2. Постоянно пересчитывает статистику за некоторый промежуток времени, и на основе полученных результатов, управляет нагрузкой и еще кое-чем через SR-201 и встроенные реле инвертора и контроллеров.

Вот это всё хозяйство мне нужно мониторить. Причем мониторить не события (событиями занимаетс Job2), а строить графики на основе накопленной статистической информации, визуализировать их на компе и мобилке. Сама “Малина” кое-что умеет, но во-первых не всё (про мои SR-201 она точно ничего не знает), во-вторых неудобный интерфейс – нельзя всё посмотреть на одном экране в удомном мне виде, а в третьих – в некоторых местах кривовато.

Вопросы: Почему Oracle а не Postgres например? Ну просто лень, хотелось сделать из того что умею… 🙂

Выбор пал на Grafana https://grafana.com – довольно мощное средство визуализации статистики и прочей ерунды. Легко настраивается, удобно использовать. Работает с многими БД…

Собственно описание проекта

Данный пост ни разу не претендует на качественный пошаговый tutorial, я лишь хочу указать путь, по которому Вам будет легче идти.

Итак:

Устанавливаем grafana

$ sudo nano /etc/yum.repos.d/grafana.repo
[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
dnf update
dnf install grafana

systemctl daemon-reload
systemctl enable --now grafana-server
systemctl status grafana-server

Selinux у меня отключен, файрвол тоже, так что в эти нюансы вдаваться не буду

Далее одна проблемка: Grafana конечно с Oracle работать умеет, но данная опция (плагин) предоставляется только в Enterprise версии, которая начинается от 24к$ и это в мои планы не входит. Устанавливаем плагин grafana-simple-json-datasource

grafana-cli plugins install grafana-simple-json-datasource
systemctl restart grafana-server

То есть графана у нас в оракл ходить не будет. Она будет брать данные из вебсервиса, теперь дело за малым – вебсервис написать.

Вебсервис будем делать на apache + php

Для этого потребуется установить и настроить:

httpd, php и php-fpm (у меня php 7.2) установлен и сконфигрирован вместе с freepbx которая живёт на том же сервере 🙂

Для php нужно подключить библиотеку oci8 – тут есть сложность в том, что для php 7.2 не получится поставить oci8 командой pecl.

В общем путь такой:

Подключаем репозиторий remi, и оттуда:

dnf install php-pecl-oci8

Подключаем oci8 к php

/etc/hp.d/20-oci8.ini

В принципе достаточно раскомментировать 1 строку

extension=oci8.so

Далее этот oci8 не очень хочет запускаться, тут помогут примерно такие строки в

/etc/php-fpm.d/www.conf

env[ORACLE_HOSTNAME] = myserver.localdomain
env[ORACLE_UNQNAME] = mydb
env[ORACLE_BASE] = /u01/app/oracle
env[ORACLE_HOME] = /u01/app/oracle/product/18.4.0/dbhome_1
env[ORA_INVENTORY] = /u01/app/oraInventory
env[ORACLE_SID] = mydb
env[LD_LIBRARY_PATH] = /u01/app/oracle/product/18.4.0/dbhome_1/lib:/lib:/usr/lib
env[NLS_LANG] = AMERICAN_CIS.UTF8

Теперь при исполнении php-скрипта на вебсервере, oci8 прекрасно запускается

Выкладываем скрипт на вебсервер

/var/www/html/gr/gr.php

<?php

header("Content-Type: application/json;");

$conn = oci_pconnect('www', 'www$password', 'mydb', 'AL32UTF8');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

// Подготовка выражения
$stid = oci_parse($conn, 'begin  LGRAFANA.GetJson(:vPath, :vInp, :vOut); end;');
if (!$stid) {
    $e = oci_error($conn);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

// Создадим дескрипторы
$vInp = oci_new_descriptor($conn, OCI_DTYPE_LOB);
$vOut = oci_new_descriptor($conn, OCI_DTYPE_LOB);

// Привяжем переменные
$vPath = $_SERVER["PATH_INFO"];
$postdata = file_get_contents("php://input");
$vInp->writeTemporary($postdata, OCI_TEMP_BLOB);

oci_bind_by_name($stid, ":vPath", $vPath);
oci_bind_by_name($stid, ":vInp", $vInp, -1, OCI_B_BLOB);
oci_bind_by_name($stid, ":vOut", $vOut, -1, OCI_B_BLOB);

// Выполним логику запроса
$r = oci_execute($stid);
if (!$r) {
    $e = oci_error($stid);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

echo $vOut->load(); 

$vInp ->close();
$vOut ->close();

oci_free_statement($stid);

oci_commit($conn);
oci_close($conn);
?>

Вебсервис готов.

В нашей БД есть пакет LGRAFANA, из которого наружу торчит только одна процедура

procedure GetJson(pPathInfo in varchar2, pInpPost in blob, pOutPost out blob);

она и будет обрабатывать Json – запросы от графаны. Эту процедуру целиком приведу чуть ниже, Вы понимаете что это лишь пример для моей электростанции, а в целом формат Json запросов-ответов описан тут

https://grafana.com/grafana/plugins/grafana-simple-json-datasource

Теперь настройка в самой графане:

Configuration – Data Sources – Add DataSource – Simple JSON

Дальше можно идти добавлять DashBoard и накидывать туда панели с нужными графиками

… Если у Вас уже есть реализация пакета LGRAFANA разумеется.

Да кстати про пакет. Он у меня написан не совсем на PL/SQL, но в целом Вы сможете это использовать для того чтобы понять, что надо написать в своём пакете. Это легко переводится на pl/sql.

Вкратце так:

  1. Реализуем метод, который реагирует на pahinfo=/search и отдаёт массив имён метрик которые мы умеем считать

  2. Реализуем метод /query который формирует массив данных по нужным метрикам

Полный текст пакета
pragma include([DEBUG_TRIGGER]::[MACRO_LIB]);

CPALL const varchar2(30) := 'Мощность нагр.';
CPNET const varchar2(30) := 'Мощность сеть';
CPACB const varchar2(30) := 'Мощность АКБ';
CPI2C const varchar2(30) := 'Мощность I2C';
CPADD const varchar2(30) := 'Доп. Нагрузка';
CPMP1 const varchar2(30) := 'Мощность MPPT1';
CPMP2 const varchar2(30) := 'Мощность MPPT2';
CPMP3 const varchar2(30) := 'Мощность MPPT3';
CEDAY const varchar2(30) := 'Выработка за день';
CEMP1 const varchar2(30) := 'Выработка MPPT1';
CEMP2 const varchar2(30) := 'Выработка MPPT2';
CEMP3 const varchar2(30) := 'Выработка MPPT3';
CETOB const varchar2(30) := 'На заряд батареи';
CEFRB const varchar2(30) := 'Взято от батареи';
CEFRN const varchar2(30) := 'Взято от сети';
CUNET const varchar2(30) := 'Напряжение сети';
CUOUT const varchar2(30) := 'Напряжение выход';
CUACB const varchar2(30) := 'Напряжение АКБ';

public function TsToUTs(v_Ts in timestamp) return number is
	v_Dt date;
begin
	v_Dt := v_ts;
	return trunc((v_Dt - to_date('01.01.1970','DD.MM.YYYY')) -- Кол-во дней с 1 янв 1970
	 * (24 * 60 * 60)) -- Теперь это кол-во секунд
	 * 1000 -- Теперь миллисекунд
	 + to_number(to_char(v_ts,'FF3')); -- Добавили миллисекунды
end;

procedure get_query(pInp in out nocopy JSON_OBJECT_T, pOut in out nocopy JSON_ARRAY_T) is
	type rtflag is record (
			 fTp varchar2(30)
			,fOb json_object_t
			,fAr json_array_t
		);
	type ttflag is table of rtflag index by string;
	tflag ttflag;
	
	vTmpOb json_object_t;
	vTmpAr json_array_t;
	vTmpId varchar2(30);
	
	vDBeg timestamp;
	vDEnd timestamp;
	vDDBeg date;
	vDDEnd date;
	
	num_tz number;
	curts number;
	
	function GetFlag(pFlagName in varchar2) return boolean is
	begin
		if tflag.exists(pFlagName) then
			return true;
		else
			return false;
		end if;	
	end;

	--function GetFlagType(pFlagName in varchar2) return varchar2 is
	--begin
	--	if tflag.exists(pFlagName) then
	--		return tflag(pFlagName).fTp;
	--	else
	--		pragma error('Нет значения ['||pFlagName||'] в мвссиве tflag');
	--	end if;	
	--end;

	procedure AddTrgData(pTrgName in varchar2, pStamp in number, pValue in number) is
	begin
		vTmpAr := Json_Array_t;
		vTmpAr.append(pValue);
		vTmpAr.append(pStamp);
		tFlag(pTrgName).fAr.append(vTmpAr);
	end;
begin
	&debug('pInp='||pInp.to_string())
	
	vTmpOb := pInp.get_Object('range');
	num_tz := to_number(::[GA_MAP_STAT].[LIB].GetSetting('MALINA_TIME_ZONE'));
	
	vDBeg := vTmpOb.get_Timestamp('from') + numtodsinterval(num_tz,'hour');
	vDEnd := vTmpOb.get_Timestamp('to')   + numtodsinterval(num_tz,'hour');
	vDDBeg := to_date(to_char(vDBeg,'dd.mm.yyyy hh24:mi:ss'),'dd.mm.yyyy hh24:mi:ss');
	vDDEnd := to_date(to_char(vDEnd,'dd.mm.yyyy hh24:mi:ss'),'dd.mm.yyyy hh24:mi:ss');
	
	&debug('vDBeg='||to_char(vDBeg,'dd.mm.yyyy hh24:mi:ss:ff'))
	&debug('vDEnd='||to_char(vDEnd,'dd.mm.yyyy hh24:mi:ss:ff'))

	vTmpAr := pInp.get_Array('targets');
	for i in 0 .. vTmpAr.get_size - 1 loop
		vTmpOb := JSON_OBJECT_T(vTmpAr.get(i));
		vTmpId := vTmpOb.get_string('target');
		tflag(vTmpId).fTp := vTmpOb.get_string('type');
		tflag(vTmpId).fOb := Json_object_t;
		tflag(vTmpId).fAr := Json_array_t;
		tflag(vTmpId).fOb.put('target',vTmpId);
	end loop;
		
	-- Взять значения мощностей из статистики МАП
	if GetFlag(CPALL) or GetFlag(CPNET) or GetFlag(CPACB) or GetFlag(CPI2C) or GetFlag(CUNET) or GetFlag(CUOUT) or GetFlag(CUACB) then
		for (select x(x.[QTIME]:qtime
			, x.[F__PNET_CALC]:pnet -- Мощность сеть
			, - x.[F__PLOAD_CALC] + x.[F__PNET_CALC]:pall -- Мощность нагр.
			, - x.[F__PLOAD_CALC]:pacb -- Мощность АКБ
			, x.[F__P_MPPT_AVG]:pi2c -- Мощность I2C
			, x.[F__UNET]:unet
			, x.[F__UOUTMED]:uout
			, x.[F__UACC]:uacb
			) in ::[GA_MAP_STAT] all
		where x.[QTIME] >= vDBeg and x.[QTIME] <= vDEnd
		order by x.[QTIME]
		) loop
			curts := TsToUTs(x.qtime - numtodsinterval(num_tz,'hour'));
			
			vTmpId := tflag.first;
			while vTmpId is not null loop
				case vTmpId of
				:CPALL: AddTrgData(vTmpId,curts,x.pall);
				:CPNET: AddTrgData(vTmpId,curts,x.pnet);
				:CPACB: AddTrgData(vTmpId,curts,x.pacb);
				:CPI2C: AddTrgData(vTmpId,curts,x.pi2c);
				:CUNET: AddTrgData(vTmpId,curts,x.unet);
				:CUOUT: AddTrgData(vTmpId,curts,x.uout);
				:CUACB: AddTrgData(vTmpId,curts,x.uacb);
				end;
				vTmpId := tflag.next(vTmpId);
			end loop;
		end loop;
	end if;

	-- Взять статистику панелей
	if GetFlag(CPMP1) or GetFlag(CPMP2) or GetFlag(CPMP3) then
		for (select x(x.[QTIME]:qtime
				,x.[F_UID]:fuid
				,x.[F_P_CURR]:fpower -- Мощность заряда
			) in ::[GA_MPPT_STAT] all
		where x.[QTIME] >= vDBeg and x.[QTIME] <= vDEnd
		order by x.[QTIME], x.[F_UID]
		) loop
			curts := TsToUTs(x.qtime - numtodsinterval(num_tz,'hour'));
			case x.fuid of
				:1: if GetFlag(CPMP1) then AddTrgData(CPMP1,curts,x.fpower); end if;
				:2: if GetFlag(CPMP2) then AddTrgData(CPMP2,curts,x.fpower); end if;
				:3: if GetFlag(CPMP3) then AddTrgData(CPMP3,curts,x.fpower); end if;
			end;
		end loop;
	end if;

	-- Взять значения мощностей из статистики допнагрузки
	if GetFlag(CPADD) then
		declare
			tqend timestamp;
			paend number;
		begin
			for (select x(
				  x.[QTIME]:qtime
				, x.[FPOWER]:padd -- Доп. Нагрузка
				) in ::[GA_LOAD_H] all
				where x.[QTIME] >= (
					select x(nvl(max(x.[QTIME]),to_timestamp('01.01.1970','dd.mm.yyyy')))
					in ::[GA_LOAD_H] all
					where x.[QTIME] < vDBeg
					)
				and x.[QTIME] < vDEnd
				order by x.[QTIME]
			) loop
				curts := TsToUTs(x.qtime - numtodsinterval(num_tz,'hour'));
				tqend := x.qtime;
				paend := x.padd;
				AddTrgData(CPADD,curts,x.padd);
			end loop;
			curts := TsToUTs(vDEnd - numtodsinterval(num_tz,'hour'));
			AddTrgData(CPADD,curts,paend);
		end;
	end if;

	-- Взять значения выработки по датам
	if GetFlag(CEDAY) or GetFlag(CEMP1) or GetFlag(CEMP2) or GetFlag(CEMP3) or GetFlag(CEFRN) then
		declare
			vDEBeg date;
			vDEEnd date;
			vDECur date;
			curEn number;
			prven number;
			vTSCur timestamp;
			curEnToBat number;
			curEnFromBat number;
			procedure GetCeMp(vCeMp in varchar2, vMpUID in number) is
			begin
				vDECur := vDEBeg;
				while vDECur <= vDEEnd loop
					vTSCur := to_timestamp(to_char(vDECur,'dd.mm.yyyy'),'dd.mm.yyyy');

					select x(nvl(max(x.[F_PWR_KW]),0)*1000) in ::[GA_MPPT_STAT] all
						where x.[qtime] >= vTSCur
						and x.[qtime] < (vTSCur + numtodsinterval(1,'day'))
						and x.[F_TIMESTAMP] >= vDECur
						and x.[F_TIMESTAMP] < (vDECur+1)
						and x.[F_UID] = vMpUID
						into curEn;
					
					curts := TsToUTs(vTsCur - numtodsinterval(num_tz,'hour'));
					AddTrgData(vCeMp,curts,curen);
					vDECur := vDECur + 1;
				end loop;
			end;
		begin
			vDEBeg := trunc(vDDBeg);
			vDEEnd := trunc(vDDEnd);
			
			if GetFlag(CEDAY) or GetFlag(CETOB) or GetFlag(CEFRB) then
				vDECur := vDEBeg;
				while vDECur <= vDEEnd loop
					vTSCur := to_timestamp(to_char(vDECur,'dd.mm.yyyy'),'dd.mm.yyyy');
					select x(
						 nvl(max(x.[S1].[F_MPPT_DAY_E]),0)
						,nvl(max(x.[S1].[F_ESUM_TO_BAT]),0)
						,nvl(max(x.[S1].[F_ESUM_FROM_BAT]),0)
					) in ::[GA_BAT_STAT] all
						where x.[qtime] >= vTSCur
						and x.[qtime] < (vTSCur + numtodsinterval(1,'day'))
						and x.[S1].[F_TIMESTAMP] >= vDECur
						and x.[S1].[F_TIMESTAMP] < (vDECur+1)
						into curEn,curEnToBat,curEnFromBat;
					curts := TsToUTs(vTsCur - numtodsinterval(num_tz,'hour'));
					
					if GetFlag(CEDAY) then AddTrgData(CEDAY,curts,curen); end if;
					if GetFlag(CETOB) then AddTrgData(CETOB,curts,curenToBat); end if;
					if GetFlag(CEFRB) then AddTrgData(CEFRB,curts,curenFromBat); end if;

					vDECur := vDECur + 1;
				end loop;
			end if;
			
			if GetFlag(CEMP1) then
				GetCeMp(CEMP1,1);
			end if;
			if GetFlag(CEMP2) then
				GetCeMp(CEMP2,2);
			end if;
			if GetFlag(CEMP3) then
				GetCeMp(CEMP3,3);
			end if;

			-- Посчитать сколько взято от сети
			if GetFlag(CEFRN) then
				vDECur := vDEBeg-1;
				prven := null;
				while vDECur <= vDEEnd loop
					vTSCur := to_timestamp(to_char(vDECur,'dd.mm.yyyy'),'dd.mm.yyyy');

					curen := 0;
					for (
						select x(x.[F__E_NET_B]*10:enet)
							in ::[GA_MAP_STAT] all
							where x.[qtime] >= vTSCur
							and x.[qtime] < (vTSCur + numtodsinterval(1,'day'))
							and x.[F_TIMESTAMP] >= vDECur
							and x.[F_TIMESTAMP] < (vDECur+1)
							order by x.[qtime] desc
					) loop
						curen := x.enet;
						exit;
					end loop;	
					
					if curen = 0 and prven != 0 then
						curen := prven;
					end if;	
					
					if prven is null then
						prven := curen;
					else	
						if prven = 0 then
							prven := curen;
						end if;
						curts := TsToUTs(vTsCur - numtodsinterval(num_tz,'hour'));

					&debug('1. dcur = '||to_char(vDECur,'dd.mm.yyyy')||' prven = '||prven||' curen ='||curen||' diff='||to_char(curen - prven)	)


						AddTrgData(CEFRN,curts,curen - prven);
						prven := curen;
					end if;	
					
					vDECur := vDECur + 1;
				end loop;
			end if;

						
		end;
	end if;

	-- Выгрузить собранные массивы  ответ
	vTmpId := tflag.first;
	while vTmpId is not null loop
		tflag(vTmpId).fOb.put('datapoints',tflag(vTmpId).fAr);
		tflag(vTmpId).fAr := null;
		pOut.append(tflag(vTmpId).fOb);
		tflag(vTmpId).fOb := null;
		vTmpId := tflag.next(vTmpId);
	end loop;
end;	

procedure get_search(pInp in out nocopy JSON_OBJECT_T, pOut in out nocopy JSON_ARRAY_T) is
	vTarget varchar2(100);
begin
	&debug('pInp='||pInp.to_string())
	vTarget := trim(pInp.get_String('target'));
	if vTarget is null then
		pOut.Append(CPALL);
		pOut.Append(CPNET);
		pOut.Append(CPACB);
		pOut.Append(CPI2C);
		pOut.Append(CPADD);
		pOut.Append(CPMP1);
		pOut.Append(CPMP2);
		pOut.Append(CPMP3);
		pOut.Append(CEDAY);
		pOut.Append(CEMP1);
		pOut.Append(CEMP2);
		pOut.Append(CEMP3);
		pOut.Append(CETOB);
		pOut.Append(CEFRB);
		pOut.Append(CEFRN);
		pOut.Append(CUNET);
		pOut.Append(CUOUT);
		pOut.Append(CUACB);
	end if;	
end;	

public procedure GetJson(pPathInfo in varchar2, pInpPost in blob, pOutPost out blob) is
	vInp JSON_OBJECT_T;
	vOut JSON_ARRAY_T;
begin
	vInp := JSON_OBJECT_T(pInpPost);
	vOut := JSON_ARRAY_T();

	&debug('pPathInfo='||pPathInfo)
	-- Маршрутизация запроса в зависимости от pPathInfo
	if pPathInfo = '/search' then
		get_search(vInp, vOut);
	elsif pPathInfo = '/query' then
		get_query(vInp, vOut);
	end if;
	
	pOutPost := vOut.to_Blob;
end;

Возможно это кому-то окажется полезным 🙂

Вот такие результаты:

Let’s block ads! (Why?)

Read More

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *