Skip to main content
InSource Solutions

TN AppSvr179 Useful MSSQL Queries In AppServer

  1. Last updated
  2. Save as PDF

InSource_Logo_Transparent.png

 

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.