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
Guery to get all members from a group (GROUPNAMEHERE) by Fullname & Email
SELECT "Email", "FirstName" + ' ' + "LastName" AS FullName FROM "PersonGroupPerson", "Person" WHERE "PersonGroupId" IN (SELECT "Id" FROM "PersonGroup" WHERE "Name" = 'GROUPNAMEHERE') AND "PersonId" = "Person"."Id"
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"
Query to get all Members by first name & last name from ALLGroups in a Datastore
SELECT Name as GroupName, (SELECT Stuff( (SELECT N', ' + FirstName + ' ' + LastName
FROM PersonGroupPerson, Person WHERE Person.Id = PersonGroupPerson.PersonId AND PersonGroupId = PG.Id
FOR XML PATH(),TYPE) .value('text()','nvarchar(max)'),1,2,N)) AS Members FROM [dbo].[PersonGroup] PG WHERE [Active] = 1
NOTE! when there are too many members, you will need to use field 26, as it can hold more data (see below).
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.
Field1-25: 1000 Char Field26: Unlimited Field27: Unlimited Field28: Unlimited Field29: Unlimited Field30: Unlimited Field31-50: 1000 Char
merge data from fields into another datastore
Sometimes you get the data in from an automated source, and you can't change the structure. But if you want to merge 2 fiels into 1, you'll need a new datastore, and make something like this:
SELECT field1 + ' / ' + field2 as [Search criteria], field3, field4, field5 FROM parameterdata WHERE parameter_id=375
SELECT field1 + ' ( ' + field2 + ' [ ' + field3 + '])' as [all data], field4, field5 as [FilterField] FROM parameterdata WHERE parameter_id=375
Another example, to create a link to a ticket (via the search page). The ticketNr is in Field1
Select '<a href="https://ssp.siemens.be/Common/Search.aspx?q=' + Field1 + '" target="_blank">' + Field1 + '</a>' as