Query to list users who has access to SDP MSP via local authentication (Not working)
Doesnt work in builds from PGSQL (14300) or MSSQL (14306)
ERROR: missing FROM-clause entry for table "sdu"
Go to Reports-New Query Reports and execute the query.
PGSQL
select au.first_name "First Name", au.last_name "Last Name", aci.emailid "Email ID", al.name "Login Name", ad.org_name "Account", sdo.name "Site Name" 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 aaalogin al on au.user_id= al.user_id
where sdu.status='ACTIVE' and al.name is not null and al.domainname ='-'
Group by 1,2,3,4,5,6
Order by 5
List of Requesters with Local Authentication
select au.first_name "First Name", au.last_name "Last Name", aci.emailid "Email ID", al.name "Login Name", ad.org_name "Account", sdo.name "Site Name" from aaauser au
left join sduser sdu on au.user_id = sdu.userid
left join requester r on r.ciid = sdu.ciid
left 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 aaalogin al on au.user_id= al.user_id
left join aaaaccount aacc on aacc.login_id=al.login_id
where al.name is not null and al.domainname ='-' and r.ciid IS NOT NULL
List of Technicians with Local Authentication
select au.first_name "First Name", au.last_name "Last Name", aci.emailid "Email ID", al.name "Login Name", ad.org_name "Account", sdo.name "Site Name" from aaauser au
left join sduser sdu on au.user_id = sdu.userid
left join requester r on r.ciid = sdu.ciid
left 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 aaalogin al on au.user_id= al.user_id
left join aaaaccount aacc on aacc.login_id=al.login_id
where al.name is not null and al.domainname ='-' and r.ciid IS NULL
New to ADSelfService Plus?
Related Articles
Login diectly with SAML / Query to enable AD or Local Auth when there is an issue with SAML
Issue: When users have AD and/or local authentication enabled along with SAML, the login page is shown when a link from an email is clicked and users need to click "Login with SAML" again. Workaround 1: You can bookmark, <sdp_url>/SamlRequestServlet ...How do I host ServiceDesk Plus MSP on the internet?
In order to make ServiceDeskPlus – MSP available for users in Internet, let us assume the following scenarios. Scenario 1: ServiceDesk Plus – MSP is installed in LAN and should be available in LAN and WAN: Assume ServiceDesk Plus – MSP is installed ...Remote read only access to database for Postgres customers
Use case: Frequently customers want to connect some reporting / dashboard application like PowerBI or Tableau with our Postgres database server. By default, the bundled Postgres is configured to only listen to the local machine. We can configure to ...How to migrate data from SDP to SDP MSP.
Below are the only details which can be exported from SDP and imported to SDP MSP application. 1) You can export request data from ServiceDesk Plus (SDP) using XLS format. Requests -> Goto Admin -> Reports -> New custom report -> Tabular reports -> ...How Pass Through Authentication Works
NTLMV2 is a protocol supported by Microsoft in order to overcome the security issues of NTLMV1 and the same is implemented in ServiceDesk Plus. What's the protocol defines? When a service wants to initiate the Single-sign-on, first a secure channel ...