Clever Geek Handbook

Temperature monitoring in the enterprise







The task came up to come up with “something” for viewing and controlling the temperatures at the factory. A PLC 160 controller has already been installed and temperature sensors are connected via the RS-485 interface ( Wikipedia ).



The controller and sensors were installed before me.



There was an example connection diagram:









Used CoDeSys ( Wikipedia ) to view.



There was no history of temperatures and it is not known when the accident occurred.



Start



The idea came this way - to create a web site in conjunction with the MySQL database and store information on temperatures and accidents there.



Initial Tasks:





Later it turned out the following:



The minimum and maximum are insufficient to control accidents.



A critical maximum and a critical minimum were added, as well as the time during which the temperature could return to normal.



  1. If the temperature has gone beyond the minimum or maximum, but returned to normal during time T , then this is a minor accident (but this accident was recorded as insignificant).



    image

  2. If the temperature goes beyond the critical minimum or critical maximum, then this is a critical accident immediately.



    image



It was required to differentiate access:





Software part



A virtual machine was created with a bunch of PLC 160 over the local network.

Installed CoDeSys.



IP addresses are configured so that the computer sees the controller.



image



The project is located on the path c: \ project \ pro \ and is called my_work.pro .



The project itself is launched through the run.cmd file 



"C:\Program Files\3S Software\CoDeSys V2.3\Codesys.exe" "C:\project\pro\my_work.pro" /userlevel 0 /password 157999 /online


Application launches run.cmd file 



 WinExec(Pchar(“c:\run.cmd”), SW_HIDE);


I used DDE to get temperature values ​​( Wikipedia )



config.ini 



 [CoDeSys] service=CoDeSys topic=C:\project\pro\my_work.pro item=C:\Program Files\3S Software\CoDeSys V2.3\ cmd=C:\run.cmd [db] host=127.0.0.1 port=3306 user=root key=keypassword db=workdb


Program start:



  1. Download CoDeSys configuration parameters from “config.ini”



    Loading MySQL configuration parameters from “config.ini”



    By Timer (It was decided that it will be enough to read data once a minute):



    • Get the number of sensors with MySQL
    • For each sensor, create a component DDE.DDEConv : 



       DDE.DDEConv[…]:= TDdeClientConv.Create(Self) DDE.DDEConv[…].ServiceApplication:=”patchcodesys” DDE.DDEConv[…].SetLink(“name”,”patchdde”)


      We create the DDE.DDEItem component and bind it to the DDE.DDEConv component: 



       DDE.DDEItem[…]:=TDdeClientItem.Create(Self) DDE.DDEItem[…].DdeConv:=DDE.DDEConv[…]


      We pass the name of the sensor with MySQL : 



       DDE.DDEItem[…].DdeItem:=MySQL.GetSensorName(…)


      As a result, we obtain the temperature value: 



       DDE.DDEItem[…].Text


      We save the current temperature value and their parameters for each sensor. 



       MySQL.InsertTemp(MySQL.GetSensorName(...),””,INSQL(UMin[...]),INSQL(UMax[...]),INSQL(CRMin[...]),INSQL(CRMax[...]))


    • We get from MySQL at the current date and time:



      Minimum 



       UMin[I…]:=OUTSQL(MySQL.GetMin(MySQL.GetSensorName(…)))


      Maximum 



       UMax[…]:=OUTSQL(MySQL.GetMax(MySQL.GetSensorName(...)))


      Critical minimum 



       CRMin[…]:=OUTSQL(MySQL.GetCriticalMin(MySQL.GetSensorName(…)))


      Critical maximum 



       CRMax[…]:=OUTSQL(MySQL.GetCriticalMax(MySQL.GetSensorName(…)))


      Time 



       CRTime[…]:=MySQL.GetCriticalTime(MySQL.GetSensorName(…))


      Note: "Protection from the fool " - if the minimum is greater than the maximum or vice versa - then we change these values ​​in places. 



       if (UMin[…]>=UMax[…]) then begin UM[…]:=UMin[…]; UMin[…]:=UMax[…]; UMax[…]:=UM[…]; end;


    • Crash:



      If there was no accident, create a record 



       MySQL.InsertCrash(FormatDateTime('yyyy-mm-dd hh:nn:ss', dt),FormatDateTime('yyyy-mm-dd hh:nn:ss', dt),MySQL.GetSensorName(...),…)


      If there was an accident we update 



       MySQL.UpdateCrash(MySQL.GetCrashID(MySQL.GetSensorName(...)),FormatDateTime('yyyy-mm-dd hh:nn:ss', dt),…)


      The accident has ended. We set the completion flag.



    Web site



    Wrote pages in PHP .



    Main page (a piece of code, do not kick much): 



     <?php require 'config.php'; session_start(); $page = isset( $_GET['page'] ) ? $_GET['page'] : ""; switch ( $page ) { case 'login': login(); break; case 'logout': logout(); break; case 'list': listpage(); break; ………………….. ?>


    The remaining pages are about the same type. Each page processes its data.



    What is done:



    • List of sensors. Names, Sensor name for the program, Sensor type.



      image
    • Sensors were grouped by purpose.



      image
    • Added “accident statuses”: In the process of an accident, Accident completed, Critical accident.
    • Implemented adding users and their roles.
    • Logging who did what.
    • Archive of all accidents.
    • Charts.



    Crutches



    1. When the CoDeSys program starts, a window appears:



      image

      We close it programmatically. 



       W_WND_Button_Run: HWND: W_WND_RUN: HWND; C_Button_Message='Button'; C_CoDeSys_Message='CoDeSys'; W_WND_RUN := FindWindow(nil,C_CoDeSys_Message); if W_WND_RUN<>0 then begin W_WND_Button_Run:=FindWindowEx(W_WND_RUN, 0,C_Button_Message, nil); if W_WND_Button_Run<>0 then begin SendMessage(W_WND_Button_Run, WM_LBUTTONDOWN, 10, 10); SendMessage(W_WND_Button_Run, WM_LBUTTONUP, 10, 10); SendMessage(W_WND_Button_Run, WM_LBUTTONDOWN, 10, 10); SendMessage(W_WND_Button_Run, WM_LBUTTONUP, 10, 10); end; end;


    2. Suddenly the controller was turned off.



      image 



       W_WND_Error:=FindWindow(nil,''); if W_WND_Error<>0 then begin W_WND_Button_Error:=FindWindowEx(W_WND_Error,0,'Button', nil); if W_WND_Button_Error<>0 then begin SendMessage(W_WND_Button_Error, WM_LBUTTONDOWN, 10, 10); SendMessage(W_WND_Button_Error, WM_LBUTTONUP, 10, 10); SendMessage(W_WND_Button_Error, WM_LBUTTONDOWN, 10, 10); SendMessage(W_WND_Button_Error, WM_LBUTTONUP, 10, 10); PostMessage(FindWindow(PChar(C_CoDeSys),nil), WM_QUIT, 0, 0); end; end;


    3. Incomprehensible hang.



      image



      We restart the application. 



       C_CLOSE_DEBUG='CoDeSys for Automation Alliance (debug)'; W_WND_CLOSE:=FindWindow(nil,C_CLOSE_DEBUG); if W_WND_CLOSE<>0 then begin KillProcess('Codesys.exe'); KillProcess('WerFault.exe'); PostMessage(FindWindow(PChar(C_Close_DEBUG),nil), WM_QUIT, 0, 0); PostMessage(FindWindow(PChar(C_CoDeSys),nil), WM_QUIT, 0, 0); MySQL.InsertLog('Error debug.. Kill process - codesys.exe and WerFault.exe'); MySQL.InsertLog('Restart programm'); RestartThisApp; end; //  function KillProcess(ExeName: string): LongBool; var B: BOOL; ProcList: THandle; PE: TProcessEntry32; begin Result := False; ProcList := CreateToolHelp32Snapshot(TH32CS_SNAPPROCESS, 0); PE.dwSize := SizeOf(PE); B := Process32First(ProcList, PE); while B do begin if (UpperCase(PE.szExeFile) = UpperCase(ExtractFileName(ExeName))) then Result := TerminateProcess(OpenProcess($0001, False, PE.th32ProcessID), 0); B:= Process32Next(ProcList, PE); end; CloseHandle(ProcList); end; //  procedure TForm1.RestartThisApp; begin ShellExecute(Handle, nil, PChar(Application.ExeName), nil, nil, SW_SHOWNORMAL); Application.Terminate; // or, if this is the main form, simply Close; end;


    Zabbix



    Created a host with the address 127.0.0.1 .



    It has a detection rule named “Sensors”.



    image



    image



    Prototypes of data elements.



    image



    Prototype triggers.



    image



    Add to zabbix_agentd.conf 



     UserParameter=sensors[*],/usr/lib/zabbix/alertscripts/sensors.sh UserParameter=crash[*],/usr/lib/zabbix/alertscripts/crash.sh $1


    The scripts themselves:



    sensors.sh 



     #!/bin/sh unset id unset res id=(`echo "select id FROM sensor WHERE type='1'" | mysql -uroot -p -D workdb -h 0.0.0.0 --default-character-set=utf8 2>/dev/null`) echo '{ "data": [' for (( count=1; count<${#id[@]}; count++ )) do res=(`echo "select name FROM sensor WHERE (type='1' and id='${id[$count]}') " | mysql -uroot -p -D workdb -h 0.0.0.0 --default-character-set=utf8 2>/dev/null `) r={${res[@]} l=${#r} res1=(`echo "select param FROM sensor WHERE (type='1' and id='${id[$count]}') " | mysql -uroot -p -D workdb -h 0.0.0.0 --default-character-set=utf8 2>/dev/null `) r1={${res1[@]} l1=${#r1} res2=(`echo "select ddename FROM sensor WHERE (type='1' and id='${id[$count]}') " | mysql -uroot -p -D workdb -h 0.0.0.0 --default-character-set=utf8 2>/dev/null `) r2={${res2[@]} l2=${#r2} res3=(`echo "select min FROM temp_${r2:17:l2} ORDER BY id DESC LIMIT 1 " | mysql -uroot -ps -D workdb -h 0.0.0.0 --default-character-set=utf8 2>/dev/null`) r3={${res3[@]} l3=${#r3} res4=(`echo "select max FROM temp_${r2:17:l2} ORDER BY id DESC LIMIT 1 " | mysql -uroot -p -D workdb -h 0.0.0.0 --default-character-set=utf8 2>/dev/null`) r4={${res4[@]} l4=${#r4} res5=(`echo "select cmin FROM temp_${r2:17:l2} ORDER BY id DESC LIMIT 1 " | mysql -uroot -p -D workdb -h 0.0.0.0 --default-character-set=utf8 2>/dev/null`) r5={${res5[@]} l5=${#r5}2>/dev/null res6=(`echo "select cmax FROM temp_${r2:17:l2} ORDER BY id DESC LIMIT 1 " | mysql -uroot -p -D workdb -h 0.0.0.0 --default-character-set=utf8 2>/dev/null`) r6={${res6[@]} l6=${#r6} res7=(`echo "select param FROM temp_${r2:17:l2} ORDER BY id DESC LIMIT 1 " | mysql -uroot -p -D workdb -h 0.0.0.0 --default-character-set=utf8 2>/dev/null`) r7={${res7[@]} l7=${#r7} s=$s'{ "{#SID}": "'${id[$count]}'", "{#SNAME}": "'${r:5:l}'", "{#SDDENAME}": "'${r2:17:l2}'" , "{#SPARAM}": "'${r7:7:l7}'", "{#SMIN}": "'${r3:5:l3}'", "{#SMAX}": "'${r4:5:l4}'", "{#SCMIN}": "'${r5:6:l5}'", "{#SCMAX}": "'${r6:6:l6}'" },' done a=${#s} b=${s: 0: $a-1} c=${#b} d=$b echo $d']}'


    crash.sh 



     #!/bin/sh a=$1 unset res res=(`echo "select flag, id_status FROM crash WHERE id_sensor='$a' ORDER BY id DESC LIMIT 1 " | mysql -uroot -p -D workdb -h 0.0.0.0 --default-character-set=utf8 2>/dev/null `) for (( count=2; count<${#res[@]}; count++ )) do s=$s' '${res[$count]} done b=${s:0:2} c=${s:3:4} if [ $b = 0 -a $c = 1 ] then echo 0 else echo 1 fi


    And then through zabbix you can send to e-mail and SMS and much more.



    Result



    The result was a temperature monitoring system at the enterprise with a review of current and past accidents.



    image



    Read more about the accident.



    image



    Currently added sensors for opening / closing doors.



    Pros:



    • Minimum costs ( relative ).
    • Plus to karma (?).
    • Monitoring has been running for 3 years.


    Minuses:



    • Many points of failure: controller, network, CoDeSys program, virtual machine, MySQL , IIS .


    PS



    Do not kick much. This is my first article.


More articles:


All Articles