Visual Basic Script to read measure value data from a ProLeiT Plant iT System.

ReadPlantiTMva.vbs

' This visual basic script is a very basic example
' how to retrieve measure values from a ProLeiT plant it system.
'
' It will create a csv style output for all measure
' datapoints with the latest timestamp of data and value.
'
' Before running this script you need to change the username,
' password, database server/instance and database to
' point at your system.
'
' Usage: cscript /Nologo ReadPlantiTMva.vbs
'
' Author: [email protected]
' Copyright (c) 2013 by Andreas Schaefer

' ***************************************
' CHANGE SETTINGS BELOW TO POINT AT YOUR SYSTEM!
CONST username   = "<Sql Username>"
CONST password   = "<SQL Password>"
CONST dbInstance = "<Hostname or IP>\PLANTIT"
CONST database   = "dbIdc"
' ***************************************

CONST DATE_19700101 = 25569.0 ' == CDbl(DateSerial(1970,1,1))

CONST adUseClient = 3 ' See: http://msdn.microsoft.com/en-us/library/ee252442%28v=bts.10%29.aspx
CONST adOpenStatic = 3 ' See: adovbs.inc
CONST adLockBatchOptimistic = 4 ' See: adovbs.inc

Dim dbConnection, dbRecordset, sqlCmd, mvaItems, itemIndex

Set dbConnection = CreateObject( "ADODB.Connection" )
Set dbRecordset  = CreateObject( "ADODB.Recordset" )

With dbConnection
    .ConnectionTimeout = 20
    .CommandTimeout = 120
    .CursorLocation = adUseClient
End With

With dbRecordset
    .CursorLocation = adUseClient
End With

dbConnection.Open "DRIVER={SQL Server};" & _
                  "SERVER=" & dbInstance & _
                  ";DATABASE=" & database & _
                  ";UID=" & username & _
                  ";PWD=" & password & _
                  ";APP=" & Wscript.ScriptName & _
                  ";Trusted_Connection=no"

' Table tblCPDataX contains generic information for all objects. nDataType=2080 means measure values objects.
' Table tblIdcServer hold information about the computer running this plant it system.
' Table tabLogbuch contains information about all measure data files that have been recorded. strBinFilename holds the
' path and filename of recorded data.
'
' The sql query will find all measure data objects and retrieves the last recorded filename for each object.
' The filename may contain a 'LOCAL' prefix to show that it was recorded "local" to the current computer
' so we need to replace the LOCAL prefix with the real name of the computer of this plant it system
' to generate a valid filesystem path and filename.
sqlCmd = "SELECT RTRIM(DX.szName), REPLACE( RTRIM(LOG.strBinFilename), '<LOCAL>',  RTRIM(IDS.szComputerName) ) " & _
         "  FROM tblCPDataX DX, tblIdcServer IDS, tabLogbuch LOG " & _
         " WHERE DX.nDataType=2080 " & _
         "   AND IDS.nKey = DX.nServerLink " & _
         "   AND LOG.nDataXLink = DX.nKey " & _
         "   AND LOG.lRecordNo = (SELECT TOP 1 LOG2.lRecordNo " & _
         "                          FROM tabLogbuch LOG2" & _
         "                          WHERE LOG2.nDataXLink = DX.nKey " & _
         "                          ORDER BY LOG2.lStartTime DESC" & _
         "                       ) "

dbRecordset.Open sqlCmd, dbConnection, adOpenStatic, adLockBatchOptimistic

If Not dbRecordset.Eof And Not dbRecordset.Bof Then
    mvaItems = dbRecordset.GetRows
End If

dbRecordset.Close

If IsArray(mvaItems) Then

    Dim itemName, latestMvaFile, latestGmtDateTime, latestValue, mvaValues, maxValueIndex

    WScript.Echo "#, Name, Filename, GMT DateTime, Value"

    For itemIndex = LBound(mvaItems,2) To UBound(mvaItems,2)

        itemName = mvaItems(0,itemIndex)
        latestMvaFile = mvaItems(1,itemIndex)
        latestUtcDateTime = Empty
        latestValue = Empty
        mvaValues = Empty

        sqlCmd = "EXECUTE master..xp_IdcGetMeasuredValues N'" & Replace( latestMvaFile, "'", "''" )  & "', 0, 0, 0, 0"
        dbRecordset.Open sqlCmd, dbConnection, adOpenStatic, adLockBatchOptimistic

        If Not dbRecordset.Eof And Not dbRecordset.Bof Then
            ' The Resultset goes: tTimestamp, dblValue, dblMinValue, dblMaxValue
            mvaValues = dbRecordset.GetRows
        End If

        dbRecordset.Close

        If IsArray(mvaValues) Then
           maxValueIndex = UBound(mvaValues,2)
           latestUtcDateTime = DateAdd( "s", mvaValues( 0, maxValueIndex ), DATE_19700101 )
           latestValue = mvaValues( 1, maxValueIndex )
        End If

        WScript.Echo "" & itemIndex & ",'" & itemName& "', '" & latestMvaFile & "', '" & latestUtcDateTime & "', '" & Round(latestValue,2) & "'"

    Next
End If

Set dbRecordset = Nothing
Set dbConnection = Nothing

Very nice program. Maybe you could give a hint on how to modify this script to get a single device measurement for a specified timeframe? And also i would like to ask if i understand it correctly: the database holds only path information, and the actual measurement data is stored somewhere else in the plant computer? I'm asking this because i'm trying to analyze a copy of database and i was wondering how to retrieve a meaningful data (like device name, timestamp and value or something like that) so i could then use it with powerBI or other platform. If database stores only links to some files in external storage then i'm lost :)