General Discussions
VTScada Default Historian Data fetch in MSSQL

VTScada stored all history data in its default Historian.
I have used that default historian only in my application and I have logged some values with the default Historian.

How Can I read this store's historian tag values in MS SQL Server?

I have installed the VTScada ODBC Driver and also add SQL Server Driver in User DSN on ODBC Administrator.

This SQL Server Driver I have configured with one of DB to store all VTScada Default Tables under that DB.

My SQL Driver got connected successfully.

Now, How can I query the VTScada Default Historian that has all Tables with values stored in it in MS SQL Database?

Can you help me with this?

VTScada stored all history data in its default Historian. I have used that default historian only in my application and I have logged some values with the default Historian. How Can I read this store's historian tag values in MS SQL Server? I have installed the VTScada ODBC Driver and also add SQL Server Driver in User DSN on ODBC Administrator. This SQL Server Driver I have configured with one of DB to store all VTScada Default Tables under that DB. My SQL Driver got connected successfully. Now, How can I query the VTScada Default Historian that has all Tables with values stored in it in MS SQL Database? Can you help me with this?

If you stat here, you can create a single VTScada server which will store to a SQL historian in a server list, this should (I believe) synchronize all of your previous data to the SQL historian.

https://www.vtscada.com/help/Content/D_LogAndReport/Dev_HistorianDataStorage.htm

Note that doing this will significantly reduce the application responsiveness when querying history. VTScada's integrated historian is much much (many times) faster than a SQL database for the types of data used in SCADA applications.

If you stat here, you can create a single VTScada server which will store to a SQL historian in a server list, this should (I believe) synchronize all of your previous data to the SQL historian. https://www.vtscada.com/help/Content/D_LogAndReport/Dev_HistorianDataStorage.htm Note that doing this will significantly reduce the application responsiveness when querying history. VTScada's integrated historian is much much (many times) faster than a SQL database for the types of data used in SCADA applications.

Trihedral Engineering Ltd.

Yes, Okay.
And what if I want to use VTScada default historian to get data from it?

How can I make a query to SystemHistorian of VTScada history to fetch the data from it?

Is there any tool that I can use or something else?

Yes, Okay. And what if I want to use VTScada default historian to get data from it? How can I make a query to SystemHistorian of VTScada history to fetch the data from it? Is there any tool that I can use or something else?

I tried to access the Default System Historian of VTScada into MS SQL by adding the DSN name to the SystemHistorian tag & after restarting the application I got tables in my MS SQL database but the tables name is not much familiar to look in.

62a1b324aac41
62a1b3291239a
62a1b32e3347a

The table names & information inside are not appropriate.

How can I query the VTScada default System Historian data?

I tried to access the Default System Historian of VTScada into MS SQL by adding the DSN name to the SystemHistorian tag & after restarting the application I got tables in my MS SQL database but the tables name is not much familiar to look in. ![62a1b324aac41](serve/attachment&path=62a1b324aac41) ![62a1b3291239a](serve/attachment&path=62a1b3291239a) ![62a1b32e3347a](serve/attachment&path=62a1b32e3347a) The table names & information inside are not appropriate. How can I query the VTScada default System Historian data?

To query the default Historian, you will probably want to use either the reporting tools to generate reports or the REST interface to make SQL queries.

You can learn about the REST interface here:

https://www.vtscada.com/help/Content/D_LogAndReport/D_RESTQueries.htm

And see some information on available SQL queries here:

https://www.vtscada.com/help/Content/D_LogAndReport/Dev_SQLQueryExamples.htm?tocpath=Use%20VTScada%7CLog%252C%20Note%252C%20and%20Report%7CSQL%20Queries%7C_____3

Note that the interface uses pagination to protect the system resources if a large query is made. I believe the default max block size is something like ten thousand records but can be adjusted if you do not want to work with pagination.

To query the default Historian, you will probably want to use either the reporting tools to generate reports or the REST interface to make SQL queries. You can learn about the REST interface here: https://www.vtscada.com/help/Content/D_LogAndReport/D_RESTQueries.htm And see some information on available SQL queries here: https://www.vtscada.com/help/Content/D_LogAndReport/Dev_SQLQueryExamples.htm?tocpath=Use%20VTScada%7CLog%252C%20Note%252C%20and%20Report%7CSQL%20Queries%7C_____3 Note that the interface uses pagination to protect the system resources if a large query is made. I believe the default max block size is something like ten thousand records but can be adjusted if you do not want to work with pagination.

Trihedral Engineering Ltd.

Here are a couple example queries:

Get specific tag properties of all tags:
https://ServerName/Realm/REST/SQLQuery?query=SELECT%20Name,%20ShortName,%20Description,%20Area,%20Value%20FROM%20Parms_AllTypes

Query specific tags for the most recent value in the historian:
https://vtsdemo.trihedral.com/exceldemo/REST/SQLQuery?query=SELECT Timestamp, Liftstations\LS1\Pumps\Pump1\Flow:Value:Average AS 'Liftstation 1 - Pump 1 - Flow', Liftstations\LS1\Pumps\Pump1\Running:Value:Average AS 'Liftstation 1 - Pump 1 - Running' FROM History_1S LIMIT 1 ORDER BY Timestamp DESC

Here is a history query for two tags you can test against a running server (ExcelDemo/demo) :
https://vtsdemo.trihedral.com/exceldemo/REST/SQLQuery?query=SELECT Timestamp, Liftstations\LS1\Pumps\Pump1\Flow:Value:Average AS 'Liftstation 1 - Pump 1 - Flow', Liftstations\LS1\Pumps\Pump1\Running:Value:Average AS 'Liftstation 1 - Pump 1 - Running' FROM history_15M where timestamp > '2022-06-08%2020:00:00' limit 100

Here are a couple example queries: Get specific tag properties of all tags: https://ServerName/Realm/REST/SQLQuery?query=SELECT%20Name,%20ShortName,%20Description,%20Area,%20Value%20FROM%20Parms_AllTypes Query specific tags for the most recent value in the historian: https://vtsdemo.trihedral.com/exceldemo/REST/SQLQuery?query=SELECT Timestamp, Liftstations\LS1\Pumps\Pump1\Flow:Value:Average AS 'Liftstation 1 - Pump 1 - Flow', Liftstations\LS1\Pumps\Pump1\Running:Value:Average AS 'Liftstation 1 - Pump 1 - Running' FROM History_1S LIMIT 1 ORDER BY Timestamp DESC Here is a history query for two tags you can test against a running server (ExcelDemo/demo) : https://vtsdemo.trihedral.com/exceldemo/REST/SQLQuery?query=SELECT Timestamp, Liftstations\LS1\Pumps\Pump1\Flow:Value:Average AS 'Liftstation 1 - Pump 1 - Flow', Liftstations\LS1\Pumps\Pump1\Running:Value:Average AS 'Liftstation 1 - Pump 1 - Running' FROM history_15M where timestamp > '2022-06-08%2020:00:00' limit 100

Trihedral Engineering Ltd.

edited Jun 9 at 3:02 pm

As an additional comment, your SQL tables look like they are set up. I believe you just need to find or create a reference between the tag's unique ID and the tag name. Note that this can easily be done using the REST interface or by exporting the tags to excel (you'll find this in settings).

As an additional comment, your SQL tables look like they are set up. I believe you just need to find or create a reference between the tag's unique ID and the tag name. Note that this can easily be done using the REST interface or by exporting the tags to excel (you'll find this in settings).

Trihedral Engineering Ltd.

Thanks for the reply, Dave.

I have tried to query the default system historian of VTScada by REST Query with SQL syntax.

I had tried to get data of all my context tags within the application by GET method by "http://INBDQ2LT8V9YHR2:81/KEGOHIL/REST/SQLQuery?query=SELECT * FROM Parms_ContextTag" and I got all the context tags details from the Parms_contextTag table.

Now for the example,
I have 2 users on my application both have privileges to modify tags and create tags.
User1 created Context1 tag & User2 created Context2 tag with their own credentials.

How can I get details like the Context1 tag created by User1 in the REST Query JSON result?
For the alarms history, we can query the history by Operator. How can I get the details of the User history for the tags?

I want to get data from users who have created the tags/stations within an application.

How can I get this data?

Thanks for the reply, Dave. I have tried to query the default system historian of VTScada by REST Query with SQL syntax. I had tried to get data of all my context tags within the application by GET method by **"http://INBDQ2LT8V9YHR2:81/KEGOHIL/REST/SQLQuery?query=SELECT * FROM Parms_ContextTag"** and I got all the context tags details from the Parms_contextTag table. Now for the example, I have 2 users on my application both have privileges to modify tags and create tags. User1 created Context1 tag & User2 created Context2 tag with their own credentials. How can I get details like the Context1 tag created by User1 in the REST Query JSON result? For the alarms history, we can query the history by Operator. How can I get the details of the User history for the tags? I want to get data from users who have created the tags/stations within an application. How can I get this data?
55
7
2
live preview
enter atleast 10 characters
WARNING: You mentioned %MENTIONS%, but they cannot see this message and will not be notified
Saving...
Saved
With selected deselect posts show selected posts
All posts under this topic will be deleted ?
Pending draft ... Click to resume editing
Discard draft