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