Create a new Console Application (.NetFramework) using Visual Studio.
The version we have used was VS2019 with .NetFramework 4.6.2, Please feel free to change it as per your need.
Copy the program.cs and paste it into your program.cs
Make sure to include the PI reference into your project (pisdk.dll/PISDKCommon.dll/PITimeServer.dll)
If you have properly installed the PI-SDK into your machine, then you should find easily the above.
we have included a Reference folder in case you might need it.
Please publish your application into your desired location.
The user account executing this application should have access on OSISoft PI point reading database, please contact your PI System Administrator if it is not your case.
Create a database into an SQL server instance, please name it as you like, in our case, we have named it "SMS"
Right Click on this created Database name, and click on New Query then copy and paste these SQL Queries with the following order
1st:Run PIDistributionGroup.sql to create the table of users group. Basically, we have used this group to store the name of an existing department within your company. GroupID should be unique. It's a primary key.
2nd: Run PIDeliveryListUsers.sql to create the table of users list. Please specify the name of the recipient list as well as their respective cell phone numbers, their groupID should be part of groupID listed in the created PIDistributionGroup table. It's a foreign key.
3rd: Run PIPointList.sql to create the table of PI point list. Please specify the list of pi point names, these points should be already available in the PI System before you add it, please indicate the Operator such as > to say more than, = to say equal, and finally specify the trigger condition. example1: SINUSOID > 0.5 example2: 32HVAC01 = on
4th: Run PIPointSnapshot.sql to create the table of pi point snapshot list.
This table will enable you to see the snapshot value of your pi point. This table is fed from your application not from your action. just leave it once created
In normal operation, data found in this table should be exactly equal to the snapshot value of your pi point found in your PI System, it is fed from your sdk application.
5th: Run PISmsQueueList.sql to create the table of sms status. This table will enable you to see whether an sms is sent or ready to be sent.
This table is partially fed from your application, once created, you have to initialize the SMS Status column by updating it using the last step
It is a mapping of data where you will see the points, the values, the trigger conditions, the users, their respective cell phone numbers and finally the status of their sms: ready/sent.
Create View by running SMS_COMBO_1.sql, it will be used in view SMS_COMBO_2.
Create View by running SMS_COMBO_2.sql. This view is used during the execution of our stored procedure to Generate SMS Queue List.
Create the 3 stored procedures by executing the following SQL queries
Run GenerateSMSQueueList.sql used to update the PISmsQueueList table
Run MergeTable.sql used to update the PIPointSnapshot table
Run ReadSMSTable.sql used to read the list of pi point name
Installing a modem hardware on a dedicated server is mandatory to allow sending/receiving our sms.
Assuming it is installed in your server with all configuration done as per the vendor of your product: port number, speed...
The next step is to make a test of your modem.
Please try to send an sms manually using AT command from the terminal and make sure the sms is sent properly.
Please download and install an HyperTerminal for testing your modem.
You should receive OK signal before you move to the next step.
For our case, we have used a Wireless Sierra Modem, but you can also use those old mobile cell phone connected to usb port. Example: Nokia N95, :) ...
Please feel free to Run a scheduled task as per your need.
In our case, we have created 2 windows tasks scheduler.
You can run it on a SQL server agent as two new jobs.
The 1st task will update the SQL Table.
Task scheduler1: run UpdateSMSTable.vbs
The 2nd task will check and send the SMS according to the table reading value.
Task scheduler2: run SendSMSTable.vbs
Please configure the two scheduled tasks with a minimum value of about 1 minute time interval between the 1st task and the 2nd.
After having run the 2 scheduled task from the first time, please check the status of your PISmsQueueList Table table
The default value of SMS Status Column in PISmsQueueList table was not set and will return NULL after having started this application.
You then have to update this column for the first time and ONLY after having added new tags or new user list.
Please initialize the PISmsQueueList table with a value of "ready" on all SMS_Status column to allow the sms to start.
The next operation does not require any human action, the application will toggle between "ready" and "sent".
In normal operation, this process will run endlessly with no human action required.
To run this application, you just need to schedule and run the 2 vbs applications, they will invoke 2 main method in our .netframework application.
Please consider about 1 minute time interval between them.
Please Check your PIPointSnapshot table to see the latest value from your PI System.
Please Check your PISmsQueueList table to status of your notification.
That's it! good luck!