Query to show list of contacts that are assigned to an account but not have a login
The below query shows the list of contacts that are assigned to an account, but does not have a login.
SELECT org.NAME AS "Account Name" ,aaauser.FIRST_NAME AS " Contact with no login" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID left join aaalogin aaalogin on aaauser.user_id=aaalogin.user_id where aaauser.first_name not in (select name from aaalogin) and org.NAME not like '%Not Assigned%'
New to ADSelfService Plus?
Related Articles
Query to show roles assigned to a technician with created date, status and department name (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query: select auser.first_name as "TECHNICIAN", max(sd.employeeid) as "Employee ID", longtodate(auser.createdtime) as "Creation Date", STRING_AGG (arole.name, ',') "Current Assigned Role", ...Login Frequency Query Report
The below report would help us find the last logged in time of the users in SCP 11.0 SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP ...Query to show SLA assigned to tickets
Working on Builds: 14500 and above Database: PGSQL & MSSQL: SELECT slad.slaname "SLA", ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", ti.FIRST_NAME AS "Technician", rtdef.NAME AS "Request Type" ...Steps to Import accounts / contacts through schedule csv from SCP 11001 build
This is applicable only from 11001 builds. How to do? # Based on the Globalconfig table entry with category as 'CSVUserImportSchedule', this can be turned ON/OFF. By default this is turned OFF. Query to enable the feature - update Globalconfig set ...Query to retrieve Account details along with additional attributes, postal address and additional fields data
TESTED IN BUILDS: 14700 (Postgres) QUERY 1 - Without Account Additional Fields: select ad.ORG_NAME As "Account", org.description AS "DESCRIPTION", ad.LOGIN_WEBURI AS "LOGIN WEB URL", ad.LOGIN_URI AS "LOGIN URI", ad.SUPPORT_EMAIL AS "SUPPORT EMAIL", ...