How to generate a SQL query for a Report with Inactive Users with Role

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

How to generate a SQL query for a Report with Inactive Users with Role

L3 Networker

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

Field_Content_versions_oct20__068Vy000014stw0IAA.png 

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 ASC

This 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!

 

 

 

 

1 accepted solution

Accepted Solutions

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!

 

View solution in original post

7 REPLIES 7

L3 Networker

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.

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.

 

 

L4 Transporter

@Marcos Pais​ 

 

SELECT

  u.username,

  u.lastlogin

FROM User u

JOIN Role r

 ON r.Name = @chosenrole

JOIN RoleMember rm

 ON rm.ID = u.ID || '_' || r.ID

WHERE

u.LastLogin < Datefunc('now', -60) and ServiceUser is not 'True'

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.

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 ASC

It was also added an additional line to order the output.

L4 Transporter

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. Field_Content_versions_oct20__068Vy000018qDSHIA2.png 

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!

 

  • 1 accepted solution
  • 168 Views
  • 7 replies
  • 0 Likes
Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

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!