- Access exclusive content
- Connect with peers
- Share your expertise
- Find support resources
12-22-2025 06:03 AM
I would like to build a SQL query that generates a similar output has we have on cyberark identity for the warning regarding users inactive for me than 60 days were we can have the following columns:
UserName, Role, LastLogin
I tried to generate the report building a new query but the I got so far was:
SELECT User.ID, User.UserName, User.LastLogin, Event.Role
FROM User
JOIN Event ON User.ID = Event.UserGUID
WHERE User.ID NOT IN
(SELECT UserGUID
FROM Event
WHERE EventType = 'Cloud.Core.Login'
AND WhenOccurred >= @startDate
AND WhenOccurred <= @endDate)
ORDER BY User.LastLogin DESC, User.UserName ASCThis query is has output does not show any privilege cloud user.
I used has starting point the code from the built-in report of "Inactive Users" but this one does not have the ROLE output.
Had anyone built a similar query, or knows how can I fix mine above?
Thank you!
01-21-2026 12:15 AM
Hello @John Wu ,
The addition of parameter above worked, I just needed to correct the value:
"Role Property" = ID, for me this variable return a empty report.
But:
"Role Property" = Name works perfectly.
Thank you very much for your help!
12-22-2025 07:58 AM
So, I see in the data dictionary when writing a custom report the Event table is listed with a "role" property. But if I select * the event table and look at the output looks like the output you are looking for shows under "roles" instead. If I alter your SQL by just appending the "s" (Event.Roles) and export a report I get role values (though in our case looks like most of the values are GUID like values rather than human readable names). I also observe that not every event that returns from your event query returns the users roles. I didn't have the time to dig deeper but perhaps that property is only populated on certain Cloud.Core.Login events\situations like successful logons? So I think you may have some more refining but I think your missing role info is simply the missing S.
01-05-2026 02:57 AM
Hello @Chris Kubiak
On my case when I change from "Event.Role" to "Event.Roles" returned a error.
In case I find a solution on this I will share it here.
Thanks for your support.
01-20-2026 05:46 AM
Hello @John Wu ,
Thanks for your reply.
The code above is returning the error:
"Query has failed: column "chosenrole" does not exist"
Maybe I am doing something wrong.
I will see if I can find out what it is.
01-20-2026 05:59 AM
Hello @JohnW (CyberArk) (CyberArk) ,
Probably I misunderstood when you wrote "@chosenrole" this was not a variable to be compiled but a variable for me to replace.
The code works when we insert a role, like in the example below for PCloud Users:
SELECT
u.username,
u.lastlogin
FROM User u
JOIN Role r
ON r.Name = "Privilege Cloud Users"
JOIN RoleMember rm
ON rm.ID = u.ID || '_' || r.ID
WHERE
u.LastLogin < Datefunc('now', -60) and ServiceUser is not 'True'
ORDER BY
u.LastLogin ASC, u.UserName ASCIt was also added an additional line to order the output.
01-20-2026 11:20 AM
Forgive me. I forgot to mention, you need to add the Parameter for "chosenrole" for the Role selector to work. In the script editor, click on "Parameters" on the left and follow the example in this screenshot.
01-21-2026 12:15 AM
Hello @John Wu ,
The addition of parameter above worked, I just needed to correct the value:
"Role Property" = ID, for me this variable return a empty report.
But:
"Role Property" = Name works perfectly.
Thank you very much for your help!
Click Accept as Solution to acknowledge that the answer to your question has been provided.
The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!
These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!
The LIVEcommunity thanks you for your participation!

