<?xml version="1.0" encoding="UTF-8"?>
<rss xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns:taxo="http://purl.org/rss/1.0/modules/taxonomy/" version="2.0">
  <channel>
    <title>topic Re: How to generate a SQL query for a Report with Inactive Users with Role in Workforce Identity Discussions</title>
    <link>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302597#M70</link>
    <description>&lt;P&gt;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. &lt;span class="lia-inline-image-display-wrapper" image-alt="Field_Content_versions_oct20__068Vy000018qDSHIA2.png"&gt;&lt;img src="https://live.paloaltonetworks.com/t5/image/serverpage/image-id/94765iEA3D738D2174F160/image-size/large?v=v2&amp;amp;px=999" role="button" title="Field_Content_versions_oct20__068Vy000018qDSHIA2.png" alt="Field_Content_versions_oct20__068Vy000018qDSHIA2.png" /&gt;&lt;/span&gt;&amp;nbsp;&lt;/P&gt;</description>
    <pubDate>Tue, 20 Jan 2026 19:20:34 GMT</pubDate>
    <dc:creator>CyberArkAlumni</dc:creator>
    <dc:date>2026-01-20T19:20:34Z</dc:date>
    <item>
      <title>How to generate a SQL query for a Report with Inactive Users with Role</title>
      <link>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302591#M64</link>
      <description>&lt;P&gt;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:&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;UserName, Role, LastLogin &lt;/P&gt;&lt;P&gt; &lt;/P&gt;&lt;P&gt;&lt;span class="lia-inline-image-display-wrapper" image-alt="Field_Content_versions_oct20__068Vy000014stw0IAA.png"&gt;&lt;img src="https://live.paloaltonetworks.com/t5/image/serverpage/image-id/94589iDAD28EC6CBF10939/image-size/large?v=v2&amp;amp;px=999" role="button" title="Field_Content_versions_oct20__068Vy000014stw0IAA.png" alt="Field_Content_versions_oct20__068Vy000014stw0IAA.png" /&gt;&lt;/span&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;I tried to generate the report building a new query but the I got so far was:&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;CODE&gt;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 &amp;gt;= @startDate
    AND WhenOccurred &amp;lt;= @endDate)
ORDER BY User.LastLogin DESC, User.UserName ASC&lt;/CODE&gt;&lt;P&gt;This query is has output does not show any privilege cloud user.&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;I used has starting point the code from the built-in report of "Inactive Users" but this one does not have the ROLE output.&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;Had anyone built a similar query, or knows how can I fix mine above?&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;Thank you! &lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;</description>
      <pubDate>Mon, 22 Dec 2025 14:03:25 GMT</pubDate>
      <guid>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302591#M64</guid>
      <dc:creator>MPais</dc:creator>
      <dc:date>2025-12-22T14:03:25Z</dc:date>
    </item>
    <item>
      <title>Re: How to generate a SQL query for a Report with Inactive Users with Role</title>
      <link>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302592#M65</link>
      <description>&lt;P&gt;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.  &lt;/P&gt;</description>
      <pubDate>Mon, 22 Dec 2025 15:58:34 GMT</pubDate>
      <guid>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302592#M65</guid>
      <dc:creator>CKubiak</dc:creator>
      <dc:date>2025-12-22T15:58:34Z</dc:date>
    </item>
    <item>
      <title>Re: How to generate a SQL query for a Report with Inactive Users with Role</title>
      <link>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302593#M66</link>
      <description>&lt;P&gt;Hello @Chris Kubiak​&amp;nbsp;&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;On my case when I change from "Event.Role" to "Event.Roles" returned a error.&lt;/P&gt;&lt;P&gt;In case I find a solution on this I will share it here.&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;Thanks for your support.&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;</description>
      <pubDate>Mon, 05 Jan 2026 10:57:11 GMT</pubDate>
      <guid>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302593#M66</guid>
      <dc:creator>MPais</dc:creator>
      <dc:date>2026-01-05T10:57:11Z</dc:date>
    </item>
    <item>
      <title>Re: How to generate a SQL query for a Report with Inactive Users with Role</title>
      <link>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302594#M67</link>
      <description>&lt;P&gt;@Marcos Pais​&amp;nbsp;&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;SELECT&lt;/P&gt;&lt;P&gt;&amp;nbsp;&amp;nbsp;u.username,&lt;/P&gt;&lt;P&gt;&amp;nbsp;&amp;nbsp;u.lastlogin&lt;/P&gt;&lt;P&gt;FROM User u&lt;/P&gt;&lt;P&gt;JOIN Role r&lt;/P&gt;&lt;P&gt;&amp;nbsp;ON &lt;A alt="https://r.Name" href="https://r.Name" target="_blank"&gt;r.Name&lt;/A&gt; = @chosenrole&lt;/P&gt;&lt;P&gt;JOIN RoleMember rm&lt;/P&gt;&lt;P&gt;&amp;nbsp;ON &lt;A alt="https://rm.ID" href="https://rm.ID" target="_blank"&gt;rm.ID&lt;/A&gt; = &lt;A alt="https://u.ID" href="https://u.ID" target="_blank"&gt;u.ID&lt;/A&gt; || '_' || &lt;A alt="https://r.ID" href="https://r.ID" target="_blank"&gt;r.ID&lt;/A&gt;&lt;/P&gt;&lt;P&gt;WHERE&lt;/P&gt;&lt;P&gt;u.LastLogin &amp;lt; Datefunc('now', -60) and ServiceUser is not 'True'&lt;/P&gt;</description>
      <pubDate>Wed, 07 Jan 2026 00:32:08 GMT</pubDate>
      <guid>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302594#M67</guid>
      <dc:creator>CyberArkAlumni</dc:creator>
      <dc:date>2026-01-07T00:32:08Z</dc:date>
    </item>
    <item>
      <title>Re: How to generate a SQL query for a Report with Inactive Users with Role</title>
      <link>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302595#M68</link>
      <description>&lt;P&gt;Hello @John Wu​&amp;nbsp;,&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;Thanks for your reply.&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;The code above is returning the error:&lt;/P&gt;&lt;P&gt;&lt;B&gt;"Query has failed: column "chosenrole" does not exist"&lt;/B&gt;&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;Maybe I am doing something wrong.&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;I will see if I can find out what it is.&lt;/P&gt;</description>
      <pubDate>Tue, 20 Jan 2026 13:46:26 GMT</pubDate>
      <guid>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302595#M68</guid>
      <dc:creator>MPais</dc:creator>
      <dc:date>2026-01-20T13:46:26Z</dc:date>
    </item>
    <item>
      <title>Re: How to generate a SQL query for a Report with Inactive Users with Role</title>
      <link>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302596#M69</link>
      <description>&lt;P&gt;Hello &lt;A alt="https://community.cyberark.com/s/profile/0052J0000091gGyQAI" href="https://community.cyberark.com/s/profile/0052J0000091gGyQAI" target="_blank"&gt;@JohnW (CyberArk)&lt;/A&gt; (CyberArk)​&amp;nbsp;,&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;Probably I misunderstood when you wrote "@chosenrole" this was not a variable to be compiled but a variable for me to replace. &lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;The code works when we insert a role, like in the example below for PCloud Users:&lt;/P&gt;&lt;CODE&gt;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 &amp;lt; Datefunc('now', -60) and ServiceUser is not 'True'
ORDER BY
    u.LastLogin ASC, u.UserName ASC&lt;/CODE&gt;&lt;P&gt;It was also added an additional line to order the output.&lt;/P&gt;</description>
      <pubDate>Tue, 20 Jan 2026 13:59:51 GMT</pubDate>
      <guid>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302596#M69</guid>
      <dc:creator>MPais</dc:creator>
      <dc:date>2026-01-20T13:59:51Z</dc:date>
    </item>
    <item>
      <title>Re: How to generate a SQL query for a Report with Inactive Users with Role</title>
      <link>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302597#M70</link>
      <description>&lt;P&gt;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. &lt;span class="lia-inline-image-display-wrapper" image-alt="Field_Content_versions_oct20__068Vy000018qDSHIA2.png"&gt;&lt;img src="https://live.paloaltonetworks.com/t5/image/serverpage/image-id/94765iEA3D738D2174F160/image-size/large?v=v2&amp;amp;px=999" role="button" title="Field_Content_versions_oct20__068Vy000018qDSHIA2.png" alt="Field_Content_versions_oct20__068Vy000018qDSHIA2.png" /&gt;&lt;/span&gt;&amp;nbsp;&lt;/P&gt;</description>
      <pubDate>Tue, 20 Jan 2026 19:20:34 GMT</pubDate>
      <guid>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302597#M70</guid>
      <dc:creator>CyberArkAlumni</dc:creator>
      <dc:date>2026-01-20T19:20:34Z</dc:date>
    </item>
    <item>
      <title>Re: How to generate a SQL query for a Report with Inactive Users with Role</title>
      <link>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302598#M71</link>
      <description>&lt;P&gt;Hello @John Wu​&amp;nbsp;,&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;The addition of parameter above worked, I just needed to correct the value:&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;"Role Property" = ID, for me this variable return a empty report.&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;But:&lt;/P&gt;&lt;P&gt;&lt;B&gt;"Role Property" = Name &lt;/B&gt;works perfectly.&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;Thank you very much for your help!&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;</description>
      <pubDate>Wed, 21 Jan 2026 08:15:27 GMT</pubDate>
      <guid>https://live.paloaltonetworks.com/t5/workforce-identity-discussions/how-to-generate-a-sql-query-for-a-report-with-inactive-users/m-p/1302598#M71</guid>
      <dc:creator>MPais</dc:creator>
      <dc:date>2026-01-21T08:15:27Z</dc:date>
    </item>
  </channel>
</rss>

