Survey App customize

Roman_Nepomniashchii

Hi, friends! Who have experience and can help me?
We want customize page survey and we want know how opportunities we have. 

1. What database and what tables use for record results of survey? We can see relations ServiceRequest and survey template (result)? When I go to link from notification if this is Incident I see relation but if this is servicerequest I can’t see relation request.

2. How work role access model? All end user can leave feedback in all template? Who can see result of  survey?

I will be grateful to you for the answers.

Tom_Hendricks

1. The quick answer is that survey results are stored as configuration items in the ServiceManager DB, just like computers, software updates, people, etc.  You can actually create a view for them using the View Builder app from Cireson if you want to see them in the console and optionally promote those views to the portal.  You could also directly query the DB for this like you technically could with any other CI, but the SCSM DB has a steep learning curve.
2. As configuration items, access is handled just like scoping of any other configuration item, through the use of CI Groups and Security Roles.  This is touched on in the installation instructions to a small degree.
   

Roman_Nepomniashchii

Tom_Hendricks said:

1. The quick answer is that survey results are stored as configuration items in the ServiceManager DB, just like computers, software updates, people, etc.  You can actually create a view for them using the View Builder app from Cireson if you want to see them in the console and optionally promote those views to the portal.  You could also directly query the DB for this like you technically could with any other CI, but the SCSM DB has a steep learning curve.
2. As configuration items, access is handled just like scoping of any other configuration item, through the use of CI Groups and Security Roles.  This is touched on in the installation instructions to a small degree.
   

Thank you for answer, do you happen to know in what table is relation ServiceRequest to Cireson.Survey.Result (in this CI record result of survey as I understand)?

Tom_Hendricks

If you are querying the servicemanager DB directly, then you would be looking at [ServiceManager].[dbo].[MTV_Cireson$Survey$Result] (or ...MT_Cireson... if you prefer to pull the table directly instead of the view).  The last column is [WorkItemId_xxxxxxxx_xxxx_xxxx_xxxx_xxxxxxxxxxxx] where the x's are a GUID that may or may not be different on your system from mine. 

The value of this column is also a GUID, which you can match to the MTV_System$WorkItem$Incident or MTV_System$WorkItem$ServiceRequest views, or you can take a different approach, joining to the BaseManagedEntityId table, then joining that from the BaseManagedEntityTypeId to the table/view of the appropriate class, etc.  I do not have an example of that as I have only ever performed it manually, rather than as a single query.

Gerhard_Goossens

Here is my attempt to get some reporting.

I'm no SQL expert, so if you have a better way to write the code, please share :-)

SELECT

distinct SUBSTRING([Questions_23349217_0CDB_79DE_1C05_DC620D416737],

PATINDEX('%[0-9]%', [Questions_23349217_0CDB_79DE_1C05_DC620D416737]),

(CASE WHEN PATINDEX('%[^0-9]%', STUFF([Questions_23349217_0CDB_79DE_1C05_DC620D416737], 1, (PATINDEX('%[0-9]%', [Questions_23349217_0CDB_79DE_1C05_DC620D416737]) - 1), '')) = 0

THEN LEN([Questions_23349217_0CDB_79DE_1C05_DC620D416737]) ELSE (PATINDEX('%[^0-9]%', STUFF([Questions_23349217_0CDB_79DE_1C05_DC620D416737], 1, (PATINDEX('%[0-9]%', [Questions_23349217_0CDB_79DE_1C05_DC620D416737]) - 1), ''))) - 1

END )

) AS Rating,

u.DisplayName AS [AffectedUser],

[CompletedDate_3067B2D8_045A_B89E_0AB1_DD56BD8A995B] as CompletedDate,

 [ServiceManager].[dbo].[MT_Cireson$Survey$Result].[DisplayName] AS [Survey],

wi.WorkItemId,

SG.SupportGroup

 FROM [ServiceManager].[dbo].[MT_Cireson$Survey$Result]

 left join ServiceManagement.dbo.WorkItem wi on [ServiceManager].[dbo].[MT_Cireson$Survey$Result].WorkItemId_1D4D9347_30CA_70B6_4EC6_DDA1E745AE2F=wi.Id

 left join ServiceManagement.dbo.Workitem_SLA_vw SG on wi.WorkItemId=SG.WorkItems

 left join ServiceManagement.dbo.CI$User u on [ServiceManager].[dbo].[MT_Cireson$Survey$Result].UserId_3E644D76_3123_DA9B_2634_66B14F935E28=u.Id

 order by CompletedDate_3067B2D8_045A_B89E_0AB1_DD56BD8A995B

That will give you something like this...

Justin_Workman

@Gerhard_Goossens - Check this out:

https://community.cireson.com/discussion/4924/alternate-view-of-survey-app-results