Active gebruikers ophalen

Gemaakt door Mark Duijkers, Gewijzigd op Di, 4 Jul, 2023 om 2:17 PM op Mark Duijkers

Resultaat voorbeeld

Fullname	Emailaddress	Profiel	Domein	LastAttempt
(systeem)		Administrator,Administrator Burgerzaken	System	Tuesday, July 4, 2023
Frontoffice	NULL	Melder	System	Wednesday, February 22, 2023
KodisionBeheer	NULL	Administrator,Behandelaar Vragen,Administrator Openbare Ruimte	System	Thursday, June 29, 2023

Script

select top 10000 p.Fullname, r.Emailaddress,(select STUFF((select top 150 ',' + rtrim(replace( pr.Description ,',','')) from kim.UsersProfiles UP inner join kim.Profiles Pr on UP.ProfileId = pr.ProfileId where up.UserId=u.UserId for xml path('')),1,1,'') )as 'Profiel' , d.Description as Domein, u.LastAttempt
from kim.Users u
inner join kim.Persons p  on u.PersonId=p.PersonId
inner join kim.Relations r on r.RelationId=p.PersonId
inner join kim.Descriptions d on u.DomainId=d.DescriptionId
where u.Active =1