Skip to main content

Galaxy Server SQL Tips

Galaxy Information

Get total number of active users on a repository
SELECT * FROM SKVirtualActor WHERE IsActiveUser = 1;

Templates

Find template gobject ID
SELECT gobject_ID FROM GalaxyName.gobject WHERE tag_name LIKE 'TemplateName';
Unprotect template
DELETE FROM GalaxyName.gobject_protected WHERE gobject_id = #
Protect template
INSERT INTO GalaxyName.gobject_protected (gobject_id) VALUES (#);
Get list of protected templates
SELECT g.gobject_id, g.tag_name
FROM gobject as g
INNER JOIN gobject_protected as p on p.gobject_id = g.gobject_id
ORDER BY g.gobject_id
Get list of all objects and their areas derived from a specific template
DECLARE @tempName VARCHAR(50);
SET @tempName = '$TemplateName';

SELECT g.tag_name as TagName,
		(SELECT tag_name FROM gobject WHERE (gobject_id = g.hosted_by_gobject_id)) AS Area
FROM gobject AS g
WHERE g.is_template = 0
	AND g.derived_from_gobject_id = (
	SELECT gobject_id FROM gobject WHERE tag_name = @tempName
	)
ORDER BY Area, g.tag_name

Get count of objects derived from a specific template
DECLARE @tempName VARCHAR(50);
SET @tempName = '$TemplateName';

SELECT COUNT(gobject_id)
FROM gobject 
WHERE is_template = 0
	AND derived_from_gobject_id = (
	SELECT gobject_id FROM gobject WHERE tag_name = @tempName
	)

Alarms

Get list of all Alarmed attributes
SELECT g.tag_name + '.' + p.primitive_name AS name, p.gobject_id, p.package_id, p.mx_primitive_id, g.hosted_by_gobject_id,
(SELECT tag_name FROM gobject WHERE (gobject_id = g.hosted_by_gobject_id)) AS Area
FROM gobject AS g INNER JOIN
primitive_instance AS p ON p.gobject_id = g.gobject_id AND p.execution_group = 19 INNER JOIN
primitive_definition AS pd ON p.primitive_definition_id = pd.primitive_definition_id AND pd.primitive_name <> '' INNER JOIN
package AS pkg ON p.gobject_id = pkg.gobject_id AND p.package_id = pkg.package_id AND pkg.package_id = g.checked_in_package_id
WHERE (g.namespace_id = 1) AND (g.is_template = 0)
ORDER BY name
Get list of all alarmed attributes defined in templates
SELECT g.tag_name AS Template, p.primitive_name AS Alarm
FROM gobject AS g INNER JOIN
primitive_instance AS p ON p.gobject_id = g.gobject_id AND p.execution_group = 19 INNER JOIN
primitive_definition AS pd ON p.primitive_definition_id = pd.primitive_definition_id AND pd.primitive_name <> '' INNER JOIN
package AS pkg ON p.gobject_id = pkg.gobject_id AND p.package_id = pkg.package_id AND pkg.package_id = g.checked_in_package_id
WHERE (g.namespace_id = 1) AND (g.is_template = 1)
ORDER BY Template, Alarm
Get alarms from wwALMDB
SELECT EventStamp, AlarmState, TagName, Description, Operator, Priority, UserFullName
FROM v_AlarmHistory
where EventStamp > '1/1/2013' and EventStamp < '1/2/2013';
Back to top