Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84549members
353810posts

Using SQL to find all objects of a class that aren't part of a template instance

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

Solved
rlao
Lieutenant
Lieutenant
0 Likes
6
1151

Using SQL to find all objects of a class that aren't part of a template instance

Hi all,

 

Is there a way to build an SQL query to find all objects of a particular class that aren't part of a template instance?

 

 

Capture.PNG

 

 

For example, in the image below I would only be interested in logic objects like 'Custom Logic' and not 'LogicLevelSimulation' or 'Initialization' which are part of a parent instance.

 

I have tried using ParentInstanceID but that field only tells me if the parent group object itself is a CGroup or CTemplateInstance type, I'm more interested in the actual child objects.

 

If it's not possible in SQL, I'm still interested to hear of any alternate approaches to this.

 

 

Accepted Solutions
BevanWeiss
Spock
Spock
0 Likes
2
1126

Re: Using SQL to find all objects of a class that aren't part of a template instance

Yeah, in line with @geoffpatton idea, here's a query that "will work"..

but it's not very efficient, so I wouldn't execute it across lots of objects.

 

 

 

SELECT
  OBJECT.ID,
  OBJECT.FULLNAME,
  (
    SELECT WITH TEMPLATES
      CASE WHEN COUNT(*) > 0 THEN TRUE ELSE FALSE END 
    FROM CDBOBJECT AS TEMPLATEOBJECTS
    WHERE FULLNAME = 
      Replace( OBJECT.FULLNAME, 
        OBJECT.PARENTINSTANCEID->FULLNAME,
        OBJECT.PARENTINSTANCEID->TEMPLATEID->FULLNAME )
   ) AS INSTANCEITEM
FROM
  CDBOBJECT AS OBJECT
WHERE
  FULLNAME LIKE 'TEMPLATE INSTANCE TO INSPECT.%'

 

 

It's also an O(n^2) algorithm (if we ignore any possible index magic on FullName), so it won't scale well.

 

PS: It appears trying to sort / filter this query in Geo SCADA Expert 2020 results in even bigger problems.  Please DO NOT run this query against a production system.

 

PPS: Just re-checked my original query.. and it still had a filter applied, so only looked at 34 objects.  On ~24k object database it died... so definitely can only be run on very small sections of an OFFLINE database (if run on a production database, this may really ruin your week).


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..

See Answer In Context

6 Replies 6
geoffpatton
Commander
Commander
0 Likes
2
1136

Re: Using SQL to find all objects of a class that aren't part of a template instance

You probably could check if a object is in a instance and then check if that object name exist in the template, but that'll be an inefficient mess.

 

If you add a Metadata search field to you system and whenever you add a point that is not in a instance set that search field to something the query will be quick and efficient. if you set the field to things like logic, alarm, or analog. Then you could filter it further easily.

BevanWeiss
Spock
Spock
0 Likes
2
1127

Re: Using SQL to find all objects of a class that aren't part of a template instance

Yeah, in line with @geoffpatton idea, here's a query that "will work"..

but it's not very efficient, so I wouldn't execute it across lots of objects.

 

 

 

SELECT
  OBJECT.ID,
  OBJECT.FULLNAME,
  (
    SELECT WITH TEMPLATES
      CASE WHEN COUNT(*) > 0 THEN TRUE ELSE FALSE END 
    FROM CDBOBJECT AS TEMPLATEOBJECTS
    WHERE FULLNAME = 
      Replace( OBJECT.FULLNAME, 
        OBJECT.PARENTINSTANCEID->FULLNAME,
        OBJECT.PARENTINSTANCEID->TEMPLATEID->FULLNAME )
   ) AS INSTANCEITEM
FROM
  CDBOBJECT AS OBJECT
WHERE
  FULLNAME LIKE 'TEMPLATE INSTANCE TO INSPECT.%'

 

 

It's also an O(n^2) algorithm (if we ignore any possible index magic on FullName), so it won't scale well.

 

PS: It appears trying to sort / filter this query in Geo SCADA Expert 2020 results in even bigger problems.  Please DO NOT run this query against a production system.

 

PPS: Just re-checked my original query.. and it still had a filter applied, so only looked at 34 objects.  On ~24k object database it died... so definitely can only be run on very small sections of an OFFLINE database (if run on a production database, this may really ruin your week).


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
rlao
Lieutenant
Lieutenant
0 Likes
1
1111

Re: Using SQL to find all objects of a class that aren't part of a template instance

Hi Geoff,

 

Yeah I agree that would be ideal moving forward to have that procedure in place for any custom additions. Unfortunately the database I'm working with has a lot of custom logic added over the years without any form of proper tagging via metadata and I'm trying to find a way to keep track of what's already been added before I can implement any improvements.

rlao
Lieutenant
Lieutenant
0 Likes
1
1103

Re: Using SQL to find all objects of a class that aren't part of a template instance

Hi Bevan,

 

I tried your query on an offline backup of our production database and even when I filtered it for Logic objects only, it still could not complete until I restricted it to a small section as you had mentioned. I also tried restructuring the query as an inner join but unfortunately it keeps returning an empty result set. I think the issue is that the 'WITH TEMPLATES' clause does not apply to the second TEMPLATEOBJECTS table in the join (but not 100% certain).

 

I've marked your response as the 'solution' for now, as I don't think there's going to be a more efficient way to achieve this.

geoffpatton
Commander
Commander
0 Likes
0
1092

Re: Using SQL to find all objects of a class that aren't part of a template instance

@rlao 

For what you are doing, it maybe quicker and easier to take an offline database copy and delete everything, but the group folders in the templates. then query what's left in the database.

 

Once you have a list you'll know what to add metadata to on the live database. Which you can probably do pretty easy with the Bulk Edit Tool, or something similar.

BevanWeiss
Spock
Spock
0 Likes
0
1087

Re: Using SQL to find all objects of a class that aren't part of a template instance

You could always use the .NET API and just iterate through all DBObjects and find those that don't have the TemplateObject property set to a valid reference (i.e. are null)

 

public DBObject TemplateObject { get; }

Property Value
Type: DBObject
A DBObject representing the corresponding template object of the object; or, null if the object is not in an instance or does not have a corresponding object in the instance's template. 

 

There might also be a way to do this in the .NET API by looking at the IconIndex property... but that probably requires knowing what all the various IconIndexes may be (or finding a rule to them).  Of course this falls apart if this property is only the base underlying Icon... I wouldn't know.  

 


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..