Hi, how do I use PI Notification to send alerts when SQL connectivity between SQL server linked servers and PI RDBMS?
You need to do the logic of checking the connectivity outside of PI Notifications, so PI AF is a good starting point.
You could map a linked AF Table to your database, with a query that does 'nothing'.
For SQL Server you could do something like SELECT N'test' or SELECT getdate() and for Oracle you could do select count(*) from dual
select count(*) from dual
Then you map the contents of the AF Table to attributes, and check for badval('your attribute') in an analysis, or directly in PI Notifications.
Another option would be to use any of the interfaces included in the MCN Health Monitor, for example the TCP or Ping interfaces. I don't remember exactly whether there's one specific to databases but somebody else probably knows that.
Hi, not really well-versed with SQL. Can you give more details? Thanks
in RDBMS interfacem dsn source is AF server. In AF server, SQL linked server is setup to connect to SQL server data source. From the SQL server in AF server, I am able to see database table from the SQL data source. How do I map a tag from the SQL data source based on the result of a query to a PI tag or AF element so that I can define condition in PI Notification?
To map a tag you need to create an attribute in your element (or in your element template) with the Data Reference set to Table Lookup.
Follow the details specified in this video: OSIsoft: Configure the table lookup data reference. v2010 - YouTube
The video is a bit dated, but the basics are the same. You have the possibility to specify a TimeColumn (TC) now, providing you with the option to collect a data stream (not just single lookups) from a SQL source. This is useful if you have slow-updating data sources you may want to trend (such as a limit, being updates a few times a year) but don't want to "spend" a PI tag out of your license to import.
If you already have the RDBMS interface configured to replicate/transfer the data from RDBMS data sources to PI Tags. Then I recommend having frequently updating configured in the interface by using either one of the options below.
1. We can have PI Tag mapped to store the timestamp from source database by executing a query like Select getdata(), we can store this in one of the PI Tag. Scan frequency can be configured based on how frequently we need to test the SQL connection.
2. Create a Job/Configure source application to frequently update value in one of the table, Configure the PI Tag to read the data.
Once the frequently updating tag is configured then you can configure the PI notification by using the PI tag with all condition. This way we will get the alert for below failures.
1. Source database is not accessible.
2. An interface is not sending data to PI for some reason (lost access, buffer sub system issues).
In addition to the other answers, the PI RDBMS Interface can be configured with a Device Status tag. You can create this from the UniInt > Health Points tab in the ICU.
The Device Status tag will report "0 | Good" when the interface is functioning well and connected to the data source, and will have a value such as "2 | Connected / No Data" when it loses connection to the data source.
Usually we use the device status tag to monitor for issues with interfaces. For example, you could set up a notification to trigger when the interface device status is not "Good" for a certain period of time.
is it "3 | 1 device(s) in error | " ODBC data source communication failure?
That's one of the potential non-Good statuses, yes!
Retrieving data ...