Query Audit Data in Microsoft Dynamics CRM 2011 to Determine When A User Last Logged In

This example will show you how to Query audit data in .NET (C#) and Jscript to determine when a user last logged in to the system.

Important Notes: The way access auditing currently works in CRM it will only record access to the system every 4 hours for someone.  So you can tell when someone logged in, but you won't be able to tell when they logged in again unless the subsequent login was more than four hours later.  Also, access auditing wasn't added to CRM 2011 until Update Rollup 5.  So you need to have CRM Online or On-Premise with at least Update Rollup 5 installed to use access auditing.

C# Example: 

RetrieveMultipleRequest rmr = new RetrieveMultipleRequest();
RetrieveMultipleResponse resp = new RetrieveMultipleResponse();
SystemUser wr = new SystemUser();

QueryExpression query = new QueryExpression()
{
    EntityName = "audit",
    ColumnSet = new ColumnSet(true),
    Criteria = new FilterExpression
    {
        FilterOperator = LogicalOperator.And,
        Conditions = 
          {
              new ConditionExpression
              {
                  AttributeName = "operation",
                  Operator = ConditionOperator.Equal,
                  Values = { 4 }  //access
              },
              new ConditionExpression
              {
                  AttributeName = "objectid",
                  Operator = ConditionOperator.Equal,
                  Values = { "6e219f51-0310-4c4d-8c60-1c524e2ba7b3" }  //my user id
              }
          
          //
          }


    },
    Orders = 
    {
        new OrderExpression
        {
            AttributeName = "createdon",
            OrderType = OrderType.Descending
        }
    }
};

rmr.Query = query;
resp = (RetrieveMultipleResponse)slos.Execute(rmr);

Now in J-SCRIPT :

if (typeof (SDK) == "undefined")
   { SDK = { __namespace: true }; }
       //This will establish a more unique namespace for functions in this library. This will reduce the 
       // potential for functions to be overwritten due to a duplicate name when the library is loaded.
       SDK.SAMPLES = {
           _getServerUrl: function () {
               ///<summary>
               /// Returns the URL for the SOAP endpoint using the context information available in the form
               /// or HTML Web resource.
               ///</summary>
               var ServicePath = "/XRMServices/2011/Organization.svc/web";
               var serverUrl = "";
               if (typeof GetGlobalContext == "function") {
                   var context = GetGlobalContext();
                   serverUrl = context.getServerUrl();
               }
               else {
                   if (typeof Xrm.Page.context == "object") {
                         serverUrl = Xrm.Page.context.getServerUrl();
                   }
                   else
                   { throw new Error("Unable to access the server URL"); }
                   }
                  if (serverUrl.match(/\/$/)) {
                       serverUrl = serverUrl.substring(0, serverUrl.length - 1);
                   } 
                   return serverUrl + ServicePath;
               }, 
           GetUserLastLoggedInRequest: function () {
               var requestMain = ""
               requestMain += "<s:Envelope xmlns:s=\"http://schemas.xmlsoap.org/soap/envelope/\">";
               requestMain += "  <s:Body>";
               requestMain += "    <Execute xmlns=\"http://schemas.microsoft.com/xrm/2011/Contracts/Services\" xmlns:i=\"http://www.w3.org/2001/XMLSchema-instance\">";
               requestMain += "      <request i:type=\"a:RetrieveMultipleRequest\" xmlns:a=\"http://schemas.microsoft.com/xrm/2011/Contracts\">";
               requestMain += "        <a:Parameters xmlns:b=\"http://schemas.datacontract.org/2004/07/System.Collections.Generic\">";
               requestMain += "          <a:KeyValuePairOfstringanyType>";
               requestMain += "            <b:key>Query</b:key>";
               requestMain += "            <b:value i:type=\"a:QueryExpression\">";
               requestMain += "              <a:ColumnSet>";
               requestMain += "                <a:AllColumns>true</a:AllColumns>";
               requestMain += "                <a:Columns xmlns:c=\"http://schemas.microsoft.com/2003/10/Serialization/Arrays\" />";
               requestMain += "              </a:ColumnSet>";
               requestMain += "              <a:Criteria>";
               requestMain += "                <a:Conditions>";
               requestMain += "                  <a:ConditionExpression>";
               requestMain += "                    <a:AttributeName>operation</a:AttributeName>";
               requestMain += "                    <a:Operator>Equal</a:Operator>";
               requestMain += "                    <a:Values xmlns:c=\"http://schemas.microsoft.com/2003/10/Serialization/Arrays\">";
               requestMain += "                      <c:anyType i:type=\"d:int\" xmlns:d=\"http://www.w3.org/2001/XMLSchema\">4</c:anyType>";
               requestMain += "                    </a:Values>";
               requestMain += "                  </a:ConditionExpression>";
               requestMain += "                  <a:ConditionExpression>";
               requestMain += "                    <a:AttributeName>objectid</a:AttributeName>";
               requestMain += "                    <a:Operator>Equal</a:Operator>";
               requestMain += "                    <a:Values xmlns:c=\"http://schemas.microsoft.com/2003/10/Serialization/Arrays\">";
               requestMain += "                      <c:anyType i:type=\"d:string\" xmlns:d=\"http://www.w3.org/2001/XMLSchema\">6e219f51-0310-4c4d-8c60-1c524e2ba7b3</c:anyType>";
               requestMain += "                    </a:Values>";
               requestMain += "                  </a:ConditionExpression>";
               requestMain += "                </a:Conditions>";
               requestMain += "                <a:FilterOperator>And</a:FilterOperator>";
               requestMain += "                <a:Filters />";
               requestMain += "              </a:Criteria>";
               requestMain += "              <a:Distinct>false</a:Distinct>";
               requestMain += "              <a:EntityName>audit</a:EntityName>";
               requestMain += "              <a:LinkEntities />";
               requestMain += "              <a:Orders>";
               requestMain += "                <a:OrderExpression>";
               requestMain += "                  <a:AttributeName>createdon</a:AttributeName>";
               requestMain += "                  <a:OrderType>Descending</a:OrderType>";
               requestMain += "                </a:OrderExpression>";
               requestMain += "              </a:Orders>";
               requestMain += "              <a:PageInfo>";
               requestMain += "                <a:Count>0</a:Count>";
               requestMain += "                <a:PageNumber>0</a:PageNumber>";
               requestMain += "                <a:PagingCookie i:nil=\"true\" />";
               requestMain += "                <a:ReturnTotalRecordCount>false</a:ReturnTotalRecordCount>";
               requestMain += "              </a:PageInfo>";
               requestMain += "              <a:NoLock>false</a:NoLock>";
               requestMain += "            </b:value>";
               requestMain += "          </a:KeyValuePairOfstringanyType>";
               requestMain += "        </a:Parameters>";
               requestMain += "        <a:RequestId i:nil=\"true\" />";
               requestMain += "        <a:RequestName>RetrieveMultiple</a:RequestName>";
               requestMain += "      </request>";
               requestMain += "    </Execute>";
               requestMain += "  </s:Body>";
               requestMain += "</s:Envelope>";
               var req = new XMLHttpRequest();
               req.open("POST", SDK.SAMPLES._getServerUrl(), false)
               req.setRequestHeader("Accept", "application/xml, text/xml, */*");
               req.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
               req.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute");
               req.send(requestMain);
               //work with the response here
               //var strResponse = req.responseXML.xml;
               //alert(strResponse.toString());
           },
 __namespace: true
};

Now you can call this jscript from a webresource by calling SDK.SAMPLES.GetUserLastLoggedIn()  All you should need to change (pass in) with this particular call would be the guid of the user you want to get the last access record for.

- I hope this helps

-