1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

<fetch aggregate="true">
  <entity name="audit">
    <attribute name="createdon" alias="LastLoginDate" aggregate="max" />
    <filter>
      <condition attribute="operation" operator="eq" value="4" />
    </filter>
    <link-entity name="systemuser" from="systemuserid" to="objectid" link-type="inner" alias="SystemUser">
      <attribute name="fullname" alias="FullName" groupby="true" />
      <attribute name="domainname" alias="DomainName" groupby="true" />
      <attribute name="isdisabled" alias="IsDisabled" groupby="true" />
      <attribute name="accessmode" alias="AccessMode" groupby="true" />
      <attribute name="userlicensetype" alias="UserLicenseType" groupby="true" />
    </link-entity>
  </entity>
</fetch>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

SELECT   MAX(audit.createdon) AS LastLoginDate,
         SystemUser.fullname AS FullName,
         SystemUser.domainname AS DomainName,
         SystemUser.isdisabled AS IsDisabled,
         SystemUser.accessmode AS AccessMode,
         SystemUser.userlicensetype AS UserLicenseType
FROM     audit
         INNER JOIN
         systemuser AS SystemUser
         ON audit.objectid = SystemUser.systemuserid
WHERE    audit.operation = 4
GROUP BY 
SystemUser.fullname, 
SystemUser.domainname, 
SystemUser.isdisabled, 
SystemUser.accessmode, 
SystemUser.userlicensetype;