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

[rlao]

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?

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.

[geoffpatton]

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 · Accepted solution]

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).

[rlao]

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]

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]

@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]

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.