Hello, I’m an automation analyst and I’m looking for the plugin to connect odbc with GE’s ihistorian. Is there a connector that is compatible with the historian, to query this data?
I got it! Using linked server MSSQL!
1 Like
Hello,
can you send me details about this setup with linked server MSSQL!
I also want to get data from GE Historian into Grafana.
Kind regards.
Same question here, please tell howto do this.
are you asking for a way to connect to ms sql server?
Not exactly, the historian server not a sql db, but a special data collection from proceses. However it should be possible to connect to the db as linked server.
1 Like
in ssms can you run a query against the linked server?
<linked_server_name>.<database>.<schema>.<table>
1 Like
The Historian provide A IhOLEDB wich can not be installed in grafana.
Now i have created a temporary MSSQL server with the historian as linked server.
In the management is can query the server with
SELECT value ,TimeStamp FROM OPENQUERY (HISTORIAN, ‘SELECT * FROM ihRawData where tagname like SERVER1Utiltiesload and TimeStamp >= 2023/12/25 and TimeStamp <= 2023/12/26’)
This gives the result.
Now excuting this query in grafana shows error :
db query error: mssql: Cannot execute the query “SELECT * FROM ihRawData where tagname likeSERVER1Utiltiesload and TimeStamp >= 2023/12/25 and TimeStamp <= 2023/12/26” against OLE DB provider “IhOLEDB.iHistorian” for linked server “HISTORIAN”.
So is the syntax different ?
Also ik cannot attach the HISTORIAN in the datasource setting
In the MSSQL managemnt it looks like below
I also found on github a project
I managed to copy this to the plugins folder but i wil not work in my docker grafana
Also this for a older version, today for the REST api you need to insert a token.
UPDATE 19:26
I have now a connection to the linked server, the solution picture below
shows that you have to change the login to the linked server
The next things is to find howto use the timeslieder from grafana
For the readers here q query that works:
SELECT value as value,TimeStamp as time FROM OPENQUERY
(HISTORIAN, ‘SET starttime=now-2d, endtime= now, samplingmode=Calculated CalculationMode = average IntervalMilliseconds = 900000 SELECT * FROM ihRawData where tagname like SERER01Utiltiesload’)
This is the part to find a solution with $__TimeStart ??
Update about server -datasource grafana
The linked server
If i run the following query in SSMS SELECT * FROM [HISTORIAN]…[ihTags] i get all data
this also known as te 3 dot system
Make a new data source in grafana
The error mssql: login error: Cannot open database “[HISTORIAN]…[ihTags]” that was requested by the login. Using the user default database “master” instead.
running the query with default master works
so below as database name do not work
[HISTORIAN]…[ihTags]
[HISTORIAN]…
[HISTORIAN]…
[HISTORIAN].
[HISTORIAN]
HISTORIAN
anyone ?
This query looks odd
SELECT *
FROM ihRawData
where tagname likeSERVER1Utiltiesload
and TimeStamp >= 2023/12/25 and TimeStamp <= 2023/12/26”
This query is posible due the linked server iholedb provider.
The good news is after a lot of try and error en google i have found a "working"solution.
It still need some work the result is good.
The picture
The problem / challenge is below
I need to substract 1 hr to get the right time
the part after stoptime works but looks not nice
( using the `$__timeFrom() and $__timeTo() give errors
SELECT value as value,TimeStampSeconds as time FROM OPENQUERY
(HISTORIAN, 'SET starttime=${__from:date:YYYY-MM-DDTHH:mm:ss}-1h, endtime={$__to:date:YYYY-MM-DDTHH:mm:ss}-1h,
samplingmode=Calculated CalculationMode = average, IntervalMilliseconds = 900000
SELECT * FROM ihRawData where tagname like [YOUR TAG here] ')
Small update got a tip add extra quotes
SELECT value as kw ,TimeStampSeconds as time FROM OPENQUERY
(HISTORIAN, ‘set starttime=‘’${__from:date:DD-MM-YYYY HH:mm:SS}‘’,endtime=’'${__to:date:DD-MM-YYYY HH:mm:ss} ‘’,
SamplingMode=lab , CalculationMode=Average, IntervalMilliseconds = 900000,
SELECT * FROM ihRawData where tagname like yourtag ')
Now my only problem is that the first hour is missing and starts @01:00
1 Like
Is this posible in the sql query editor ?
declare ${tod} date
declare ${tostr}
${tod} = $__timeTo(),
${tostr} = replace($__timeTo(),“T”," “),
${tostr} = replace(${tostr},“Z”,”"),
Input ${tostr} = replace(‘2023-12-28T22:59:59Z’,“T”," "),
wanted result
2023-12-28 22:59:59’
Only one way to find out 
try it out
This works in SMSS
declare @tod AS NVARCHAR(50)
declare @tostr AS NVARCHAR(50)
set @tod = ‘2023-12-28T22:59:59Z’
set @tostr = replace(@tod,‘T’,’ ‘)
set @tostr = replace(@tostr,‘Z’,’')
Print @tostr
Result : 2023-12-28 22:59:59
Now copy the SQL to grafana and change all the @ for ${ }
The message appears b query error: mssql: Incorrect syntax near ‘$’.
Still trying…
Finally, I found a working solution. I think it could use some tweaking, but it works!
The working code
declare @frstr AS VARCHAR(50)
declare @tostr AS VARCHAR(50)
declare @sqlstr as NVARCHAR(4000)
SELECT @frstr = replace(replace($__timeFrom(),‘T’,’ ‘),‘Z’, ‘’)
select @tostr = replace(replace($__timeTo(),‘T’,’ '),‘Z’, ‘’)select @sqlstr = ‘SELECT value as P15m ,TimeStamp AS time FROM OPENQUERY
(HISTORIAN, ‘‘SELECT value , TimeStamp FROM ihRawData WHERE tagname = WINCCSERVER010.HM1::Utilties_Enexis_load AND
TimeStamp >= ‘’’’’+ @frstr +‘’‘’’ AND
TimeStamp <= ‘’‘’’ + @tostr +‘’‘’’ AND
samplingmode = interpolated and
IntervalMilliseconds = 15m ‘’)’exec (@sqlstr)
As you can see, you need a large bag of single quotes 
Or use a stored procedure