How to pull the attachment information of all the users from Servicedesk plus to Analytics plus ?.

If you are looking to import the information for the attachment of the user module as shown below,

Sample imported table in the Analytics plus UI:

Then, please follow the below instructions:

1. Login to your Analytics Plus and go-to your ServiceDesk Plus workspace.

2. Click on create new table / import data.

3. Select local & cloud databases.

4. Under connection name select local databases -> choose the database type of your ServiceDesk Plus and fill in the connection information. (NOTE: If postgres SQL is your ServiceDesk Plus database, then you can find the database related information under <ServiceDesk home>\conf\database_params.conf) else, you can choose the custom backend DB.

5. Choose custom query ( radio button) and paste the below query based on your backend DB and hit next.

PGSQL Query

select aaauser.first_name as "UserName", string_agg(distinct (case when sdeskattachment.attachmentname is null then 'No' else 'Yes' end), ','), string_agg(sdeskattachment.attachmentname, ',') from aaauser inner join sduser on aaauser.user_id = sduser.userid left join userattachments on aaauser.user_id = userattachments.userid left join sdeskattachment on userattachments.attachmentid = sdeskattachment.attachmentid where sduser.status='ACTIVE' group by aaauser.user_id

MSSQL Query

select aaauser.first_name as "UserName", "Attachments yes or no"=STUFF((select distinct case when sa.attachmentname is null then 'No' else 'Yes' end from aaauser as au inner join sduser as sd on au.user_id = sd.userid left join userattachments as ua on au.user_id = ua.userid left join sdeskattachment as sa on ua.attachmentid = sa.attachmentid where aaauser.user_id = au.user_id and sd.status='ACTIVE' for xml path ('')),1,0,''),"Attachments Name"=STUFF((select CHAR(10)+ sa.attachmentname from aaauser as au inner join sduser as sd on au.user_id = sd.userid left join userattachments as ua on au.user_id = ua.userid left join sdeskattachment as sa on ua.attachmentid = sa.attachmentid where aaauser.user_id = au.user_id and sd.status='ACTIVE' for xml path ('')),1,1,'') from aaauser inner join sduser on aaauser.user_id = sduser.userid where sduser.status='ACTIVE';