Как настроить мониторинг любых бизнес-процессов, в БД 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.

Вкратце так:

Реализуем метод, который реагирует на pahinfo=/search и отдаёт массив имён метрик которые мы умеем считать
Реализуем метод /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;

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

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