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';
New to ADSelfService Plus?
Related Articles
How to import description field details from ServiceDesk Plus to Analytics Plus?
Description details from the Requests' module are not available out-of-the box in Analytics Plus as it might contain HTML contents and cannot be displayed properly for reporting. However, if you wish to import description details into Analytics Plus, ...Move Analytics Plus from one Server/folder
Go to Start --> Services.msc and stop the ManageEngine Analytics Plus service. Take a backup of the entire <Analytics Plus_Home> directory and then move it to the new server. On the new server, navigate to (similar path) <Analytics Plus_Home>\bin ...Analytics Plus Inventory
Aim: To have the detailed list of all the Reports, Databases created in Analytics Open Local & Cloud Databases and fill the entries password = Postgres and if any error occurs stating something like "check the DB connection" change the Port to ...Unable to integrate ServiceDesk Plus with Analytics Plus after applying SSL
Note: This problem occurs only with self signed certificates in Analytics Plus. Issue: [10:23:16:526]|[09-05-2016]|[SYSERR]|[INFO]|[68]|: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: ...How to import description and resolution fields from ServiceDesk Plus to Analytics Plus?
Description details from the Requests' module are not available out-of-the box in Analytics Plus as it might contain HTML contents and cannot be displayed properly for reporting. However, if you wish to import description details into Analytics Plus, ...