Export All the points from EWS Restful Gateway of SBO

[sesa145087_brid]

Dear,

   I am trying to expose all the SBO data through EWS Restful gateway to a 3rd party cloud platform. Since there are more than 10,000 points, it there any quickly export all the points name and path to a spreadsheet for them to do the mapping? Or anyway for them to scan all the points in one time? I know probably AET can help, but no sure whether it can really do. Thank you very much!

Andrew Ding

[sesa180908_brid]

Andrew,

While there is nothing native in SmartConnector that does this it shouldn't be to difficult to write a Processor that "walks the tree" using the EwsClient class.  The information obtained could easily be written to a CSV file for import into Excel.

If anyone on the forum has done this perhaps they could share some code?

[sesa145087_brid]

Hi Mark,

Thanks for your reply. If it is not native, would you please give me some clues, like what function is used/called so that I can involve a programmer to get in? Thanks!

[sesa180908_brid · Accepted solution]

I created the Processor in a new "samples" project which you can find here: https://github.com/BuildingsLabs/SmartConnectorSamples.

Specifically the "SmartConnector.UtilityExtensions.EwsDiscoveryProcessor".  When I have time, I'll add more to it but for now it navigates the EWS tree and exports all ValueItem (not Value and State for now) to a CSV file from a given starting ContainerItem ID.

[sesa145087_brid]

Hi Mark,

Really appreciate for this! But I didn't find the exact name. Is it SmartConnector.UtilityExtensions? Thanks!

Andrew

[sesa145087_brid]

Hi Mark,

I found it in the code. It should be this. Thanks! I will look into this and see what I can do.

Thanks!

[sesa145087_brid]

Hi Mark,

I am sorry but is it possible to tell me how to use this project? Does it create a new function beyond what is written in EWS RESTful gateway manual? Or is it possible to make a simple HMI so that we can use it? Since scan function is not available for our gateway, the 3rd party still needs us to provide the spreasheet.

And also, by this way, is it possible to also see the value within function block/script program? Or I have to create virtual points for data sharing?

Thank you very much!

Andrew

[sesa180908_brid]

The project is the source code for a SmartConnector Extension assembly.  I did this to jump start your developer.  Your developer will need to compile the code, deploy the assembly to your SmartConnector installation, add a configuration in SmartConnector and then run the processor.  The last two steps are done with the SmartConnector Portal.

This is all documented in SmartConnector Developers Guide.pdf and

[sesa145087_brid]

Hi Mark,

Thanks for this. I will make it out. Appreciate.

Andrew

[sesa145087_brid]

Hi Mark,

Today I tried it out. But how to fill Output File Path field? I have tried different file path, or even IIS website, but the log always says access to the path denied. Any special setting needs to be modified?

Thanks!

Andrew Ding

[sesa180908_brid]

I'm not sure exactly what you are asking but here is what you should have done.  Please indicate which steps you did that are not behaving as expected.

1. Compile the sample code and copy the SmartConnector.UtilityExtensions.dll file to the installation of SmartConnector (e.g.C:\Program Files (x86)\Schneider Electric\SmartConnector).
2. Open a browser on the SmartConnector machine and browse to the portal (e.g. http://localhost:8082/ by default).
3. Log in with your credentials
4. Select Configurations-Processor menu option
5. Click Add New
6. From the "Step 1 - Pick an assembly", click on "SmartConnector.UtilityExtensions.dll".
7. Click Next.
8. From "Step 2 - Choose a Class", click on "EwsDiscoveryProcessor".
9. Click Next.
10. Click Finish
11. After the configuration is created and the page refreshes automatically, click the "Details" tab

At this point, you should see all the configurable properties and this is where you would enter in your values and save the updated configuration.  When you run the Processor Configuration (it can be started from this page or the listing page), you will should see your file generated.

Since everything runs under the "Local System Account", you shouldn't have file access issues.  However if you do, you might need to give access to that account. 

If you are using a "Desktop" destination, you may need to give the SmartConnectorService the ability to "interact with the desktop".  To do that,

1. Open the "Services" control panel.
2. Find "SmartConnectorService" in the list.
3. Right click and select "Properties"
4. Click the Log On tab.
5. Ensure the "Allow service to interact with desktop" option.
6. Click "Ok"

[sesa145087_brid]

Hi Mark,

Thanks for your reply. I finally made it by filling a path with a file name xx.txt instead of just a folder. Now when the processor is started, it is started and then stopped. I can see the output file, with it is with column name but no value. I checked the log, there are several lines here:

2017-08-04 11:44:43.6916,Debug,Service,<no principal>,EwsClientPool.CheckOutConnection for http://localhost:88/EcoStruxure/DataExchange,

2017-08-04 11:44:43.6997,Debug,Service,<no principal>,EwsClientPoolCheckOutConnection created connection 1 for http://localhost:88/EcoStruxure/DataExchange,

2017-08-04 11:44:43.7399,Debug,Ews.Common,<no principal>,Digest username="admin", realm="MyRealm", nonce="FD8CB056BC3546D3857D5FC037E7866C", uri="http://localhost:88/EcoStruxure/DataExchange", algorithm=SHA-256, response="3f570fa76dad9c13feae88d88a34ae3cc6d8801d17a754747d49e9c4f05d5202", qop=auth, nc=00000001, cnonce="MTAzRDc1MDdCNzlGNEVCM0I3MzlCQ0M5NzkzRUJEMjc", opaque="0000000000000000",[0,2],MyRealm,POST,

2017-08-04 11:44:43.7702,Debug,Ews.Common,admin,Digest username="admin", realm="MyRealm", nonce="D143E3EBDD824CE5BC1132910B7E9385", uri="http://localhost:88/EcoStruxure/DataExchange", algorithm=SHA-256, response="01f5790115e11807d1d6a80ea0005fbf4a2aa28bd0c73b10f66a342f512b2995", qop=auth, nc=00000001, cnonce="OEFBMDBBNDg4NzNCNDM0QjkxRjU5N0MxQjcxNjU0OEM", opaque="0000000000000000",[0,2],MyRealm,POST,

2017-08-04 11:44:43.8018,Error,Processor,<no principal>,Object reference not set to an instance of an object.

   at SmartConnector.UtilityExtensions.EwsDiscoveryProcessor.ExportData(String containerItemId, IManagedEwsClient client, Int32 currentDepth)

   at SmartConnector.UtilityExtensions.EwsDiscoveryProcessor.Execute_Subclass()

   at Mongoose.Process.Processor.Execute(CancellationToken token, ProcessorExecutionMode mode)

,["EWS Discovery Processor"],

2017-08-04 11:44:43.8109,Debug,Processor,<no principal>,Processor EWS Discovery Processor Execute completed,

2017-08-04 11:44:43.8109,Debug,WorkerThread,<no principal>,EWS Discovery Processor,Execute (end),2017-08-04T03:44:43,ET=202 mSec,

2017-08-04 11:44:43.8308,Info,WorkerThread,<no principal>,EWS Discovery Processor,Ended unsuccessfully,

2017-08-04 11:44:43.8423,Info,WorkerThread,<no principal>,EWS Discovery Processor,Next run when commanded,

So what would be the cause of the error?

Thanks!

Andrew

[sesa145087_brid]

Here is my setting. Hope it can help.

Andrew

[sesa180908_brid]

Andrew,

I thought I put enough defensive code in the "ExportData" method but my guess is something from the server is coming back empty or null and I wasn't defensive enough.

Can you turn on "EwsClient" logging and retry?  You can do that from the SmartConnector Portal "Setup-Logging Filters" page.  Set "Ews Consume" to "True" and save.  Then retry your test and send me the log file.

Mark

[sesa145087_brid]

Hi Mark,

Turning on the consume log helps a lot. It turns out there is something wrong with my ES. After turning of the ES again, it works. The only thing here is all the points is in text file. Is it possible to change a little bit so that it can be opened in Excel like csv so that it can be more convenient to edit and map? Really appreciate for this!

Andrew

[sesa180908_brid]

It is a CSV file.  Just change the extension in your configuration to .csv or just rename the file after it's created.

Glad I could help.

[sesa145087_brid]

Hi Mark,

Everything works now. Really appreciate for all of these, which also made me understand SmartConnector better.

Have a great day!

Andrew

[sesa145087_brid]

Hi Mark,

I found out that the exported csv has an issue on Chinese characters reorganization. Do you know where to set like Unicode I guess to make it work? Thanks!

Andrew Ding

[sesa180908_brid]

Andrew,

You should be able to explicitly control the encoding of the output file.  You would need to change line 111 of the EwsDiscoreyProcessor as follows:

File.AppendAllText(ResolvedOutputFilePath, $"{vi.Id},{vi.Name},{vi.Description},{vi.Type},,{vi.Forceable},{vi.Writeable},,{vi.Unit}{Environment.NewLine}", Encoding.Unicode);

[sesa145087_brid]

Hi Mark,

Today I tried this on a real project and met an issue. Some points have the same path name, with different property in Description. I think some of them are points from Menta program, and other objects. I have to combine the value and Description together for the full path. Is it possible to improve this? Thanks!

Andrew Ding

[sesa180908_brid]

Andrew,

I'm not following what you are reporting here.  If you are referring to the EwsDiscoveryProcessor; this is sample code.  If it's not doing exactly what you need, you are more then welcome to update it to meet your purposes and issue a Pull Request if you think your changes should be included in the sample.

[sesa145087_brid]

OK I got it. Thanks!

Andrew

[JessieSu]

!

Mark Stemmler​

Hi Mark

I use this sample code in project, it stopped automatically after 10 minutes after start EWS Discovery Processor, but actually only around 1/3 points be exported (Total points more than 10,000). And the log included this message:" Info,WorkerManager,<no principal>,Worker is being aborted because the processor is not completing in a timely manner.  Total elapsed runtime is 601.1011619...", I attached log here for your reference.

I go through the code, and not sure which code is for this timeout setting, so can you identify which code I should change to fix this problem? Thank you!

Best Regards

Jessie

[JeffBowman]

Hi Jie,

This can be fixed by adding the interface 'ILongRunningProcessor' to your processor class. By default a processor will be aborted if it takes over 60 seconds (although this is technically configurable in the settings on the portal page, but generally we don't advise changing this limit.) If a processor is known to take a long time to run, just add 'ILongRunningProcessor' to it, and it won't be aborted.

Regards,

-Jeff

[sesa180908_brid]

As Jeff mentioned, the Processor is taking longer than a conforming Processor should take to complete (this is documented in the Developers Guide).  These type of Processors should be given the ILongRunningProcess interface which instructs the runtime to not kill the thread after the default timeout.

I've pushed the code update to GitHub.

[JessieSu]

Thank you Mark and Jeff!

For the version reason, I finally use "StartingContainer Item ID" as filter to export AS data path one by one. Thanks!