Query to get Requesters details for each account (Not Working)
Doesnt work in builds from PGSQL (14300) or MSSQL (14306)
ERROR: missing FROM-clause entry for table "sdu"
PGSQL & MSSQL:
Execute the query under Reports->New Query Report and export it to the desired format.
select au.user_id "User Id", au.first_name "First Name", au.last_name "Last Name", sdu.isvipuser "VIP User", sdu.employeeid "Employee ID",sdu.jobtitle "Job Title",aci.emailid "Email ID",aci.landline "Telephone",aci.mobile "Mobile", sdo.name "Site Name", ad.org_name "Account Name",timedef.displayname "TimeZone" from aaauser au
inner join sduser sdu on au.user_id = sdu.userid
inner join requester r on r.ciid = sdu.ciid
inner join ci ci on ci.ciid = r.ciid
left join sdorganization sdo on ci.siteid = sdo.org_id
left join accountsitemapping asm on sdo.org_id=asm.siteid
left join accountdefinition ad on ad.org_id=asm.accountid
left join aaausercontactinfo auci on auci.user_id = sdu.userid
left join aaacontactinfo aci on aci.contactinfo_id = auci.contactinfo_id
left join sduserprofile sduprof on sduprof.userid=sdu.userid
left join timezonedefinition timedef on timedef.timezoneid=sduprof.timezoneid where sdu.status='ACTIVE' and asm.accountid in ($Account);
Note: To get account specific results, please select the account from the drop down above in the query reports page.
New to ADSelfService Plus?
Related Articles
API -Listing Account Specific Requesters
1. Encode (Used for GET calls alone) the below values thereby replacing the Account Name as per yours : { "operation":{ "details":{ "accountname": "Sample Account", "sitename": "0" } } } 2. The site name value is set as "0", which will list the ...Query to export All Requesters from All Accounts (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Execute the below query under Reports > New Query Reports: SELECT AaaUser.USER_ID,AaaUser.FIRST_NAME "FullName",AaaLogin.NAME "LoginName",AaaLogin.DOMAINNAME "Domain",AaaContactInfo.EMAILID ...SQL Query to export request details
Supported only for SQL DB(14201 build). Executed in SQL DB directly to export the requests. Below query to export requests for one account / site. SELECT "wo"."WORKORDERID" AS "Request ID", "wo"."TITLE" AS "Subject", "wotodesc"."FULLDESCRIPTION" AS ...Restricting Account Specific Notifications for Certain Requesters
Steps are not application in latest builds due to behavior change. In some rare cases where you would like to be able to close a Request without notifying the customer, please follow the below mentioned: 1. Go to Admin > Notification Rules > Choose ...Query to get the user details (both technicians and requesters) account-wise with User ID (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Tested in builds : 14000, 14201, 14301 1) Query to get the First Name, Last Name, Display Name, Login User ID, Login Name, Email and Phone Number of all users in the application, account-wise, with the ...