DataStoreTips

From SMT-X
Jump to: navigation, search

Query to get all Members of a certain Group in a Datastore

  SELECT [Person].UniqueID FROM [PersonGroupPerson], [Person] WHERE [PersonGroupPerson].[PersonId] = [Person].[Id] AND [PersonGroupPerson].[PersonGroupId] = 3

Query to get all Members from multiple Groups in a Datastore (this is done by name of the group, not the IDs)

  SELECT "Email" FROM "PersonGroupPerson", "Person" WHERE "PersonGroupId" IN 
  (SELECT "Id" FROM "PersonGroup" WHERE "Name" = 'Corp group for Security Exception' OR "Name" = 'Tire group for Security Exception' OR "Name" = 'Tech group for Security Exception' OR "Name" = 'Infosec Group for Security Exception')
  AND "PersonId" = "Person"."Id"

Working around the limit of 1000 characters in datastore columns

By default the columns in datastore have a limit of 1000 characters. In case you need to store more data, try to use the field mapping of the fields 26, 27, 28, 29 or 30. In the SSP database these fields are defined as CLOB (unlimited content). When defining your parameter fields, you can select in which table column the data should be stored. For large text fields, select any of the mentioned fields.

Field sizes:

Field1-25: 1000 Char
Field26: Unlimited
Field27: Unlimited
Field28: Unlimited
Field29: Unlimited
Field30: Unlimited
Field31-50: 1000 Char