TN AppSvr179 Useful MSSQL Queries In AppServer

Description

From time to time it is necessary to get bulk information about a deployed galaxy. You may want to be able to query for things such as:

• How many scripts are there for each object?
• What are the names of each script in an object?
• How many tags are Historized in each object?
• What are the names of the Historized attributes?
• How many graphics are on an object?

Without having to count things manually or export objects and count in excel.

• Author: Lewis Talley
• Published: 12/18/2015
• Applies to: Application Server 2014 R2 (applies to other versions as well)

Details

The fundamental way this works is by querying the GR DB and joining 2 tables on the object ID's. The gobject table and the Primitive_Instance table. The Primitive_Instance table holds in its type column the following possible values:

• alarmextension
• historyextension
• inputextension
• inputoutputextension
• outputextension
• ScriptExtension
• SymbolExtension

The basic format of the query is as follows

To get the names of the scripts configured in an object

Select Distinct g.Tag_Name as 'Object Name',p.Primitive_Name as 'Script Name' 

from Primitive_Instance p Join gobject g

on g.gobject_id = p.gobject_id 

Where p.Extension_Type = 'ScriptExtension'

and g.Is_template <> 1

Order By g.Tag_name

To get the counts of scripts configured in an object

Select Distinct g.Tag_Name as 'Object Name', Count (p.Primitive_Name) as 'Script Count'

from Primitive_Instance p Join gobject g

on g.gobject_id = p.gobject_id 

Where p.Extension_Type = 'ScriptExtension'

and g.Is_template <> 1

Group by g.Tag_name

If you wanted to alter the 2 queries above to produce information about graphic counts and names, you would alter the p.Extension_Type = 'ScriptExtension' and select something more appropriate such as 'SymbolExtension' or 'HistoryExtension' if you wanted information about attributes being Historized.