system.tag.queryTagHistory not updating the data and bring duplicate or same value for last result

Hi Team,
We are using the Ignition 8.1.14 on VM and MSSQL is setup on other VM.

Currently there is gateway script which runs at mid night to query the data for that day and email the report to us in xml format.

We have notice on couple of instance where the duplicate value is populated for the time of the day.

If i look at the values in the power table using history tag by browsing for same day. The values has changed for that period where report is showing us the data as duplicate values.

I check the wrapper log file i am unable to pickup any error logs. I also looked at the gateway script log activity and nothing relevant showing there as well.

Is there anywhere to check what is going on with the system and how to identify what is causing this issue?

image

Added the issue highlighted in blue where value is repeated again and again.

Hi team,
Is there anyone available to discuss this?

Maybe you need to show us the script and the SQL query in particular. Post it using the </> code formatting button.

###############################################################################
monitorName = SiteWellsReport
sampleHeaders = [‘t_stamp’, ‘value’]
# getting data by query tag history
# Tag path is list of tags which needs to be quered from historian
TagPaths=[’[IGN]farms/flows/well4flow/tot/MeterTotal’,’[IGN]farms/flows/well9flow/tot/MeterTotal’,’[IGN]farms/flows/well12flow/tot/MeterTotal’,’[IGN]farms/flows/well15flow/tot/MeterTotal’,’[IGN]farms/flows/well17flow/tot/MeterTotal’] # Bore well 4A, 9, 12, 15,17
# naming the columns in the table
columnName=[‘Timestamp’,‘Well 4A m3’,‘Well 9 m3’,‘Well 12 m3’,‘Well 15 m3’,‘Well 17 m3’]
#End time of the query
endTime = system.date.now()
#Start time of the query
startTime = system.date.addHours(endTime, -24)
dataSet = system.tag.queryTagHistory(paths=TagPaths, startDate=startTime, endDate=endTime, returnSize=96, aggregationMode=LastValue, returnFormat=‘Wide’, ignoreBadQuality = ‘true’, columnNames=columnName, intervalMinutes =15 )
#print rows returned for record keeping
print (‘Get Dataset Rows’)
print dataSet
print (‘finish Dataset Rows’)
#coverting dataset to python dataset
pythonDataset = system.dataset.toPyDataSet(dataSet)
sampleData = pythonDataset
type or paste code here

Above is the code and the issue is when the system.tag.queryTagHistory is getting the data. It is not bring the data correctly sometime it is populating the data with duplicate values.

You messed up the code formatting. The code goes inside the triple backticks.
You can fix it by editing your question using the pencil icon. Preview before posting.

Please check if this is any better?

To put more details:
Following screen shot is showing the data for same day in power table. As you can see the data is changing.

So i am not sure why system.tag.queryTag History is duplicating the values. I can replicate this issue … and no error comes up on wrapper log file or under gateway script log files.

Still not formatted correctly, ill do it for you.

###############################################################################
monitorName = SiteWellsReport
sampleHeaders = [‘t_stamp’, ‘value’]
# getting data by query tag history
# Tag path is list of tags which needs to be quered from historian
TagPaths=[’[IGN]farms/flows/well4flow/tot/MeterTotal’,’[IGN]farms/flows/well9flow/tot/MeterTotal’,’[IGN]farms/flows/well12flow/tot/MeterTotal’,’[IGN]farms/flows/well15flow/tot/MeterTotal’,’[IGN]farms/flows/well17flow/tot/MeterTotal’] # Bore well 4A, 9, 12, 15,17
# naming the columns in the table
columnName=[‘Timestamp’,‘Well 4A m3’,‘Well 9 m3’,‘Well 12 m3’,‘Well 15 m3’,‘Well 17 m3’]
#End time of the query
endTime = system.date.now()
#Start time of the query
startTime = system.date.addHours(endTime, -24)
dataSet = system.tag.queryTagHistory(paths=TagPaths, startDate=startTime, endDate=endTime, returnSize=96, aggregationMode=LastValue, returnFormat=‘Wide’, ignoreBadQuality = ‘true’, columnNames=columnName, intervalMinutes =15 )
#print rows returned for record keeping
print (‘Get Dataset Rows’)
print dataSet
print (‘finish Dataset Rows’)
#coverting dataset to python dataset
pythonDataset = system.dataset.toPyDataSet(dataSet)
sampleData = pythonDataset

Try removing aggregationMode=LastValue ?

Hi Josborn,
I tried removing aggregationmode = lastvalue… it looks like there is lot of decimal places after the decimal point which got introduced and still having the same issue as shown below.

Not certain if this is contributing to what you are seeing or not, but it is definitely not correct.

  1. 'true' is not the boolean value True.
  2. You’re using both returnSize and intervalMinutes, yes there are 96 - 15min intervals in 24 hours, but you only need to use one of these. Personally I think intervalMinutes is more readable
  3. Your columnName list has to be a 1-1 match with the tagPaths, since 'Timestamp' isn’t one of the tagPaths that you are providing then it shouldn’t be included in the list.

Your definition of the columnName list should look like:

columnName = ['Well 4A m3','Well 9 m3','Well 12 m3','Well 15 m3','Well 17 m3']

Your call to system.tag.queryTagHistory() should look like:

dataSet = system.tag.queryTagHistory(paths=TagPaths, startDate=startTime, endDate=endTime, aggregationMode=LastValue, returnFormat='Wide', ignoreBadQuality = True, columnNames=columnName, intervalMinutes =15 ) 

As an aside, if you want to print a dataset and see the contents, then you can use this helper script provided by @JordanCClark . It is quite handy.

Hi Irose,
Thanks for your reply.
I have tried correcting the ignorebad value to “True”. this has made no difference.

i have tried deleting the return size and only have interval setting. this has made no difference.

If i tried to delete the time stamp from the columnNames. It is giving error. But if i look into the dataset Row0 of the dataset is the time stamp. that is by default. So if we are labeling the row 0 as "Well 4A m3 " the data under it is date time stamp.

Show the corrected script and also what the error you are getting is.

Result is still the same.

Script modified to delete the Timestamp:

Error after removing the timestamp. as it is used down the script.

I am seeing unusally send a packet ping and read a packet ping. What is this ?

This is the data which we are getting in the dataset



Is there any issue with gateway itself?
I am seeing following records . Is this could be causing factor for it?

DEBUG | wrapperp | 2022/06/09 14:20:42 | Send a packet PING : ping 000238f3
INFO | jvm 1 | 2022/06/09 14:20:42 | WrapperManager Debug: Received a packet PING : ping 000238f3
INFO | jvm 1 | 2022/06/09 14:20:42 | WrapperManager Debug: Send a packet PING : ping 000238f3
DEBUG | wrapperp | 2022/06/09 14:20:42 | read a packet PING : ping 000238f3
DEBUG | wrapperp | 2022/06/09 14:20:47 | Send a packet PING : ping 00023925
INFO | jvm 1 | 2022/06/09 14:20:47 | WrapperManager Debug: Received a packet PING : ping 00023925
INFO | jvm 1 | 2022/06/09 14:20:47 | WrapperManager Debug: Send a packet PING : ping 00023925
DEBUG | wrapperp | 2022/06/09 14:20:47 | read a packet PING : ping 00023925
DEBUG | wrapperp | 2022/06/09 14:20:52 | Send a packet PING : ping 00023957
INFO | jvm 1 | 2022/06/09 14:20:52 | WrapperManager Debug: Received a packet PING : ping 00023957
INFO | jvm 1 | 2022/06/09 14:20:52 | WrapperManager Debug: Send a packet PING : ping 00023957
DEBUG | wrapperp | 2022/06/09 14:20:52 | read a packet PING : ping 00023957
DEBUG | wrapper | 2022/06/09 14:20:54 | SERVICE_CONTROL_INTERROGATE
DEBUG | wrapper | 2022/06/09 14:20:54 | SERVICE_CONTROL_INTERROGATE
DEBUG | wrapperp | 2022/06/09 14:20:54 | Send a packet SERVICE_CONTROL_CODE : 4
DEBUG | wrapperp | 2022/06/09 14:20:54 | Send a packet SERVICE_CONTROL_CODE : 4
INFO | jvm 1 | 2022/06/09 14:20:54 | WrapperManager Debug: Received a packet SERVICE_CONTROL_CODE : 4
INFO | jvm 1 | 2022/06/09 14:20:54 | WrapperManager Debug: ServiceControlCode from Wrapper with code 4
INFO | jvm 1 | 2022/06/09 14:20:54 | WrapperManager Debug: Received a packet SERVICE_CONTROL_CODE : 4
INFO | jvm 1 | 2022/06/09 14:20:54 | WrapperManager Debug: ServiceControlCode from Wrapper with code 4
DEBUG | wrapperp | 2022/06/09 14:20:57 | Send a packet PING : ping 00023989
INFO | jvm 1 | 2022/06/09 14:20:57 | WrapperManager Debug: Received a packet PING : ping 00023989
INFO | jvm 1 | 2022/06/09 14:20:57 | WrapperManager Debug: Send a packet PING : ping 00023989
DEBUG | wrapperp | 2022/06/09 14:20:57 | read a packet PING : ping 00023989
DEBUG | wrapperp | 2022/06/09 14:21:02 | Send a packet PING : ping 000239bb
INFO | jvm 1 | 2022/06/09 14:21:02 | WrapperManager Debug: Received a packet PING : ping 000239bb
INFO | jvm 1 | 2022/06/09 14:21:02 | WrapperManager Debug: Send a packet PING : ping 000239bb
DEBUG | wrapperp | 2022/06/09 14:21:02 | read a packet PING : ping 000239bb
DEBUG | wrapperp | 2022/06/09 14:21:07 | Send a packet PING : ping 000239ed
INFO | jvm 1 | 2022/06/09 14:21:07 | WrapperManager Debug: Received a packet PING : ping 000239ed
INFO | jvm 1 | 2022/06/09 14:21:07 | WrapperManager Debug: Send a packet PING : ping 000239ed
DEBUG | wrapperp | 2022/06/09 14:21:07 | read a packet PING : ping 000239ed
DEBUG | wrapperp | 2022/06/09 14:21:12 | Send a packet PING : ping 00023a1f
INFO | jvm 1 | 2022/06/09 14:21:12 | WrapperManager Debug: Received a packet PING : ping 00023a1f
INFO | jvm 1 | 2022/06/09 14:21:12 | WrapperManager Debug: Send a packet PING : ping 00023a1f
DEBUG | wrapperp | 2022/06/09 14:21:12 | read a packet PING : ping 00023a1f
DEBUG | wrapperp | 2022/06/09 14:21:17 | Send a packet PING : ping 00023a51
INFO | jvm 1 | 2022/06/09 14:21:17 | WrapperManager Debug: Received a packet PING : ping 00023a51
INFO | jvm 1 | 2022/06/09 14:21:17 | WrapperManager Debug: Send a packet PING : ping 00023a51
DEBUG | wrapperp | 2022/06/09 14:21:17 | read a packet PING : ping 00023a51
DEBUG | wrapperp | 2022/06/09 14:21:22 | Send a packet PING : ping 00023a83
INFO | jvm 1 | 2022/06/09 14:21:22 | WrapperManager Debug: Received a packet PING : ping 00023a83
INFO | jvm 1 | 2022/06/09 14:21:22 | WrapperManager Debug: Send a packet PING : ping 00023a83
DEBUG | wrapperp | 2022/06/09 14:21:22 | read a packet PING : ping 00023a83
DEBUG | wrapperp | 2022/06/09 14:21:27 | Send a packet PING : ping 00023ab5
INFO | jvm 1 | 2022/06/09 14:21:27 | WrapperManager Debug: Received a packet PING : ping 00023ab5
INFO | jvm 1 | 2022/06/09 14:21:27 | WrapperManager Debug: Send a packet PING : ping 00023ab5
DEBUG | wrapperp | 2022/06/09 14:21:27 | read a packet PING : ping 00023ab5
DEBUG | wrapperp | 2022/06/09 14:21:32 | Send a packet PING : ping 00023ae7
INFO | jvm 1 | 2022/06/09 14:21:32 | WrapperManager Debug: Received a packet PING : ping 00023ae7
INFO | jvm 1 | 2022/06/09 14:21:32 | WrapperManager Debug: Send a packet PING : ping 00023ae7
DEBUG | wrapperp | 2022/06/09 14:21:32 | read a packet PING : ping 00023ae7
DEBUG | wrapperp | 2022/06/09 14:21:37 | Send a packet PING : ping 00023b19
INFO | jvm 1 | 2022/06/09 14:21:37 | WrapperManager Debug: Received a packet PING : ping 00023b19
INFO | jvm 1 | 2022/06/09 14:21:37 | WrapperManager Debug: Send a packet PING : ping 00023b19
DEBUG | wrapperp | 2022/06/09 14:21:37 | read a packet PING : ping 00023b19
DEBUG | wrapperp | 2022/06/09 14:21:42 | Send a packet PING : ping 00023b4b
INFO | jvm 1 | 2022/06/09 14:21:42 | WrapperManager Debug: Received a packet PING : ping 00023b4b
INFO | jvm 1 | 2022/06/09 14:21:42 | WrapperManager Debug: Send a packet PING : ping 00023b4b
DEBUG | wrapperp | 2022/06/09 14:21:42 | read a packet PING : ping 00023b4b
DEBUG | wrapperp | 2022/06/09 14:21:47 | Send a packet PING : ping 00023b7d
INFO | jvm 1 | 2022/06/09 14:21:47 | WrapperManager Debug: Received a packet PING : ping 00023b7d
INFO | jvm 1 | 2022/06/09 14:21:47 | WrapperManager Debug: Send a packet PING : ping 00023b7d
DEBUG | wrapperp | 2022/06/09 14:21:47 | read a packet PING : ping 00023b7d
DEBUG | wrapperp | 2022/06/09 14:21:52 | Send a packet PING : ping 00023baf
INFO | jvm 1 | 2022/06/09 14:21:52 | WrapperManager Debug: Received a packet PING : ping 00023baf
INFO | jvm 1 | 2022/06/09 14:21:52 | WrapperManager Debug: Send a packet PING : ping 00023baf
DEBUG | wrapperp | 2022/06/09 14:21:52 | read a packet PING : ping 00023baf
DEBUG | wrapper | 2022/06/09 14:21:54 | SERVICE_CONTROL_INTERROGATE
DEBUG | wrapper | 2022/06/09 14:21:54 | SERVICE_CONTROL_INTERROGATE
DEBUG | wrapperp | 2022/06/09 14:21:54 | Send a packet SERVICE_CONTROL_CODE : 4
DEBUG | wrapperp | 2022/06/09 14:21:54 | Send a packet SERVICE_CONTROL_CODE : 4
INFO | jvm 1 | 2022/06/09 14:21:54 | WrapperManager Debug: Received a packet SERVICE_CONTROL_CODE : 4
INFO | jvm 1 | 2022/06/09 14:21:54 | WrapperManager Debug: ServiceControlCode from Wrapper with code 4
INFO | jvm 1 | 2022/06/09 14:21:54 | WrapperManager Debug: Received a packet SERVICE_CONTROL_CODE : 4
INFO | jvm 1 | 2022/06/09 14:21:54 | WrapperManager Debug: ServiceControlCode from Wrapper with code 4
DEBUG | wrapperp | 2022/06/09 14:21:57 | Send a packet PING : ping 00023be1
INFO | jvm 1 | 2022/06/09 14:21:57 | WrapperManager Debug: Received a packet PING : ping 00023be1
INFO | jvm 1 | 2022/06/09 14:21:57 | WrapperManager Debug: Send a packet PING : ping 00023be1
DEBUG | wrapperp | 2022/06/09 14:21:57 | read a packet PING : ping 00023be1

The error is because elsewhere in the script you’re referencing the column Timestamp which doesn’t exist. The timestamp column will now be titled t_stamp.

Leaving Timestamp out of the columnName list is correct.

If you do include it, since presumably the script is not erroring, then I would guess that you’re dataset is now missing the Well 17 3m column header.

Like I said I’m not sure it is contributing to the problem you are trying to solve, but it is definitely causing issue that eventually will come to light.

You should probably contact support about what you’re seeing perhaps they can look over your shoulder and isolate the issue.

Sure do you know what is the process of contacting the support team?

You can find all of the information you need here

https://support.inductiveautomation.com/hc/en-us