Retrieving audit data via console app

(0) Share

Report

Posted on by G.Vetere

I have written a console app to try and retrieve audit data from a filtered list of Cases. The audit data I want is specific, it's from the field Status Reason - I want the Change Date value of when it was last updated to the value Pending Closure

The result from my console app is an error:

This is the code I've got so far, but i just can't quite get it to show any audit data at all :(

string EntitySchemaName = "incident", AttributeSchemaName = "statuscode";

            foreach (var caseRecords in results.Entities)
            {
                string CaseName = caseRecords.Attributes["title"].ToString();
                string CaseNumber = caseRecords.Attributes["ticketnumber"].ToString();
                string CaseGUID = caseRecords.Attributes["incidentid"].ToString();
                _CaseId = new Guid(CaseGUID);

                try
                {
                    RetrieveAttributeChangeHistoryRequest attributeChangeHistoryRequest = new RetrieveAttributeChangeHistoryRequest
                    {
                        Target = new EntityReference(EntitySchemaName, _CaseId),
                        AttributeLogicalName = AttributeSchemaName
                    };

                    RetrieveAttributeChangeHistoryResponse attributeChangeHistoryResponse = (RetrieveAttributeChangeHistoryResponse)service.Execute(attributeChangeHistoryRequest);
                    
                    foreach (var EachEditRecord in attributeChangeHistoryResponse.AuditDetailCollection.AuditDetails)
                     {
                        AttributeAuditDetail attributeDetail = (AttributeAuditDetail)EachEditRecord;

                        System.Console.WriteLine(attributeDetail);
                        System.Console.WriteLine(EachEditRecord);
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine("Failed to fetch data. \nReason {0}", e.Message);
                }
                Console.ReadLine();

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (2)

a33ik 84,331 Most Valuable Professional on at

Like (1)

Report

Hello,

Check following article - docs.microsoft.com/.../gg309735(v=crm.8)

It should give you answers.

Was this reply helpful? Yes No
Suggested answer

Flydancer 1,332 on at

Like (0)

Report

Like the error message stated already: your cast is invalid.

Try using the relevant code snippet from https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/developers-guide/hh547448%28v%3dcrm.8%29 or andrew's link

Was this reply helpful? Yes No
Suggested answer

Seren S 402 on at

Like (0)

Report
Hi G.Vetere,

You need to check the type of Audits. Because it contains the Microsoft.Crm.Sdk.Messages.AttributeAuditDetail which is the one you want to use, and it can also contain the Microsoft.Crm.Sdk.Messages.AuditDetail which is used for messages of Audits itself. (e.g: Audit Enabled: System creates this when you first enable the audits)

So if you update your code with adding validation into your foreach loop, then you are all good to go.

string EntitySchemaName = "incident", AttributeSchemaName = "statuscode"; foreach (var caseRecords in results.Entities) { string CaseName = caseRecords.Attributes["title"].ToString(); string CaseNumber = caseRecords.Attributes["ticketnumber"].ToString(); string CaseGUID = caseRecords.Attributes["incidentid"].ToString(); _CaseId = new Guid(CaseGUID); try { RetrieveAttributeChangeHistoryRequest attributeChangeHistoryRequest = new RetrieveAttributeChangeHistoryRequest { Target = new EntityReference(EntitySchemaName, _CaseId), AttributeLogicalName = AttributeSchemaName }; RetrieveAttributeChangeHistoryResponse attributeChangeHistoryResponse = (RetrieveAttributeChangeHistoryResponse)service.Execute(attributeChangeHistoryRequest); foreach (var EachEditRecord in attributeChangeHistoryResponse.AuditDetailCollection.AuditDetails) { var detailType = EachEditRecord.GetType(); if (detailType == typeof(Microsoft.Crm.Sdk.Messages.AttributeAuditDetail)) { AttributeAuditDetail attributeDetail = (AttributeAuditDetail)EachEditRecord; System.Console.WriteLine(attributeDetail); System.Console.WriteLine(EachEditRecord); } } } catch (Exception e) { Console.WriteLine("Failed to fetch data. \nReason {0}", e.Message); } Console.ReadLine(); }

Hope it helps.

Cheers, Seren.

Was this reply helpful? Yes No
G.Vetere 8 on at

Like (0)

Report
Hi Seren,

Thanks for helping, it helped me get much further and i think im also there, but now I've encountered a new issue:

I get a message displayed saying: Microsoft.Xrm.Sdk.OptionSetValue where the value should be, i'm not sure what i'm missing other than converting it to a string?

string EntitySchemaName = "incident", AttributeSchemaName = "statuscode";

foreach (var caseRecords in results.Entities) { string CaseName = caseRecords.Attributes["title"].ToString(); string CaseNumber = caseRecords.Attributes["ticketnumber"].ToString(); string CaseGUID = caseRecords.Attributes["incidentid"].ToString(); String oldValue = "(no value)", newValue = "(no value)"; _CaseId = new Guid(CaseGUID); try { RetrieveAttributeChangeHistoryRequest attributeChangeHistoryRequest = new RetrieveAttributeChangeHistoryRequest { Target = new EntityReference(EntitySchemaName, _CaseId), AttributeLogicalName = AttributeSchemaName }; RetrieveAttributeChangeHistoryResponse attributeChangeHistoryResponse = (RetrieveAttributeChangeHistoryResponse)service.Execute(attributeChangeHistoryRequest); foreach (var EachEditRecord in attributeChangeHistoryResponse.AuditDetailCollection.AuditDetails) { var detailType = EachEditRecord.GetType(); if (detailType == typeof(AttributeAuditDetail)) { AttributeAuditDetail attributeDetail = (AttributeAuditDetail)EachEditRecord; foreach (KeyValuePair<String, object> attribute in attributeDetail.NewValue.Attributes) { if (attributeDetail.OldValue.Contains(attribute.Key)) oldValue = attributeDetail.OldValue[attribute.Key].ToString(); newValue = attributeDetail.NewValue[attribute.Key].ToString(); Console.WriteLine("Attribute: {0}, old value: {1}, new value: {2}", attribute.Key, oldValue, newValue); } } } } catch (Exception e) { Console.WriteLine("Failed to fetch data. \nReason {0}", e.Message); } Console.ReadLine(); System.Console.WriteLine(caseRecords.Attributes["title"]); System.Console.WriteLine(caseRecords.Attributes["ticketnumber"]);

Was this reply helpful? Yes No
Verified answer

Seren S 402 on at

Like (0)

Report
Hi G.Vetere,

You have to cascade your returned value into a compatible type of XRM classes then you are able to get the exact value.

e.g.

oldValue = ((OptionSetValue)attributeDetail.OldValue[attribute.Key]).Value.ToString();

It is also the same for the EntityReferences (lookup fields) by using (EntitiyReference) as a cascade member in the first.

Hope it helps.

Cheers, Seren.

Was this reply helpful? Yes No
G.Vetere 8 on at

Like (0)

Report

Thank you Seren!

One more thing, I can see you can pull newValue and oldValue, but i can't find the variable for the Change Date field?

Was this reply helpful? Yes No
Verified answer

Seren S 402 on at

Like (1)

Report
Hi G.Vetere,

To achieve this, retrieve function is not enough to get all the attributes of the audit record. You should use "RetrieveAuditDetailsRequest" for further details.

foreach (var EachEditRecord in attributeChangeHistoryResponse.AuditDetailCollection.AuditDetails) { var detailType = EachEditRecord.GetType(); if (detailType == typeof(Microsoft.Crm.Sdk.Messages.AttributeAuditDetail)) { Microsoft.Crm.Sdk.Messages.AttributeAuditDetail attributeDetail = (Microsoft.Crm.Sdk.Messages.AttributeAuditDetail)EachEditRecord; foreach (KeyValuePair<String, object> attribute in attributeDetail.NewValue.Attributes) { if (attributeDetail.OldValue.Contains(attribute.Key)) oldValue = ((OptionSetValue)attributeDetail.OldValue[attribute.Key]).Value.ToString(); if (attributeDetail.NewValue.Contains(attribute.Key)) newValue = ((OptionSetValue)attributeDetail.NewValue[attribute.Key]).Value.ToString(); Console.WriteLine("Attribute: {0}, old value: {1}, new value: {2}", attribute.Key, oldValue, newValue); //**************** //Details of Audit var auditDetailsRequest = new Microsoft.Crm.Sdk.Messages.RetrieveAuditDetailsRequest { AuditId = attributeDetail.AuditRecord.Id }; var auditDetailsResponse = (Microsoft.Crm.Sdk.Messages.RetrieveAuditDetailsResponse)_service._orgService.Execute(auditDetailsRequest); if (auditDetailsResponse != null && auditDetailsResponse.AuditDetail != null && auditDetailsResponse.AuditDetail.AuditRecord != null) { Microsoft.Xrm.Sdk.Entity auditEntity = auditDetailsResponse.AuditDetail.AuditRecord; if (auditEntity.Attributes.Contains("createdon")) { Console.WriteLine(string.Format("Audit CreatedOn {0}", auditDetailsResponse.AuditDetail.AuditRecord.Attributes["createdon"])); } } } System.Console.WriteLine(attributeDetail); System.Console.WriteLine(EachEditRecord); } }

I dropped useful links for you to extend your works.

https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/developers-guide/gg309735(v=crm.8)

https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/developers-guide/gg308134(v%3Dcrm.8)

Cheers, Seren.

Was this reply helpful? Yes No
G.Vetere 8 on at

Like (0)

Report
Hi Seren Serifoglu

I'm trying to now restrict the audit data to only the most recent one

I just want to know the most recent createdon date for statuscode changed

I've tried using an indexer and just showing the first one but it gives me an error:

Here's my code atm:

public class Case_List { private string[] address = new string[0]; public string this[int index] { get { return address[index]; } set { address[index] = value; } } } . . . . . . var auditDetailsResponse = (RetrieveAuditDetailsResponse)service.Execute(auditDetailsRequest); if (auditDetailsResponse != null && auditDetailsResponse.AuditDetail != null && auditDetailsResponse.AuditDetail.AuditRecord != null) { Entity auditEntity = auditDetailsResponse.AuditDetail.AuditRecord; if (auditEntity.Attributes.Contains("createdon")) { Case_List date = new Case_List(); date[0] = auditDetailsResponse.AuditDetail.AuditRecord.Attributes["createdon"].ToString(); //var dateValue = auditDetailsResponse.AuditDetail.AuditRecord.Attributes["createdon"].ToString(); Console.WriteLine(string.Format("Updated on {0}", date[0])); } }

Thanks!

Was this reply helpful? Yes No
Suggested answer

Seren S 402 on at

Like (0)

Report
Hi G. Vetere,

I think this is not related to CRM, only a logical error in indexers.

If you should update your class with defining the dimension of the array;

public class Case_List { private string[] address = new string[1]; public string this[int index] { get { return address[index]; } set { address[index] = value; } } }

Then, you can get&set your default indexer.

Case_List date = new Case_List(); date[0] = auditDetailsResponse.AuditDetail.AuditRecord.Attributes["createdon"].ToString(); Console.WriteLine(string.Format("Audit CreatedOn Value in Case_List{0} ", date[0].ToString()));

Cheers, Seren.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
This is how I pull the data - my Code is starting at the loop where you are getting the AuditDetails, you can remove the if statement that is looking for statuscode if you are looking at all/different attributes.

foreach (var myDetail in auditDetailCollection.AuditDetails) { var detailType = myDetail.GetType(); if (detailType == typeof(AttributeAuditDetail)) { var attributeDetail = (AttributeAuditDetail)myDetail; foreach (KeyValuePair<String, object> attribute in attributeDetail.NewValue.Attributes) { // get the optionset if it is a statuscode value if (attribute.Key == "statuscode") { OptionSetValue myVal = (OptionSetValue)attribute.Value; int myStatusCodeId = myVal.Value; // Do stuff with your New Value } } foreach (KeyValuePair<String, object> attribute in attributeDetail.OldValue.Attributes) { // get the optionset if it is a statuscode value if (attribute.Key == "statuscode") { OptionSetValue myVal = (OptionSetValue)attribute.Value; int myStatusCodeId = myVal.Value; // Do Stuff with your Old Value } } } }

Was this reply helpful? Yes No