In this post, we will discuss about how AuditHistory data can be used in the reports; AttributeMask, ChangeData columns of AuditHistory table, and how it should be used inside a function and a Sql query.

Recently, we ran into a requirement to access Audit history data for one of the reports.

The Audit History data can't be accessed using filtered views, hence you would need to access the data using the AuditHistory database table itself.

Few things you should note about the Audit History database table, and columns.

https://community.dynamics.com/crm/b/mscrmcustomization/archive/2015/02/09/ms-crm-audit-database-table-details

In this post, we will concentrate on two such columns, AttributeMask and ChangeData

AttributeMask: Attribute field code of the entity. The type is nvarchar, and is separated by commas.

Example: ,10015,7, 21,

ChangeData: The data that has been changed. This is always the old value of the attribute that has been changed. The new data can be accessed from the entity record.

The type of this field is nvarchar, and the changed data is seperated by a tilde (~).

Example: systemuser,0000000-0000-0000-0000-000000000000~11~False

Our requirement was to see what data has been changed on the Account entity's RelationshipType attribute. More specifically, If the relationship type (a picklist) attribute value has been changed from a Prospect (11) to a Customer-Active (1).

We had used a function to Split the attribute mask and a another funtion to extract only the old values of the data for the RealtionshipType field (the Attribute mask code of this field is 7).

CREATE FUNCTION
dbo.SplitAttributeValue
(
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
    WHILE @start < LEN(@string) + 1
    BEGIN
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END
    RETURN
END
GO
select * from dbo.SplitAttributeValue('systemuser,0000000-0000-0000-0000-000000000000~11','~')

CREATE FUNCTION
dbo.GetAttributeMaskOldValue
(
    @maskstring NVARCHAR(MAX),    
    @maskdelimiter NVARCHAR(MAX),
    @valuestring NVARCHAR(MAX),    
    @valuedelimiter NVARCHAR(MAX)
)
RETURNS @output TABLE(mask NVARCHAR(MAX), oldvalue NVARCHAR(MAX)
)
BEGIN

IF((@maskstring IS NOT NULL) AND (@valuestring IS NOT NULL))

BEGIN
IF @maskstring like (@maskdelimiter + '%')
BEGIN
select @maskstring = SUBSTRING(@maskstring,2, LEN(@maskstring)+1)
END
DECLARE @maskstart INT, @maskend INT
    SELECT @maskstart = 1, @maskend = CHARINDEX(@maskdelimiter, @maskstring)
    DECLARE @valuestart INT, @valueend INT
    SELECT @valuestart = 1, @valueend = CHARINDEX(@valuedelimiter, @valuestring)
    WHILE @maskstart < LEN(@maskstring) + 1
    BEGIN
       IF @maskend = 0  
            SET @maskend = LEN(@maskstring) + 1
       IF @valueend = 0  
            SET @valueend = LEN(@valuestring) + 1
        INSERT INTO @output (mask, oldvalue)  
        VALUES(SUBSTRING(@maskstring, @maskstart, @maskend - @maskstart),
        SUBSTRING(@valuestring, @valuestart, @valueend - @valuestart))
        SET @maskstart = @maskend + 1
        SET @maskend = CHARINDEX(@maskdelimiter, @maskstring, @maskstart)
        SET @valuestart = @valueend + 1
        SET @valueend = CHARINDEX(@valuedelimiter, @valuestring, @valuestart)
    END
    END
     
    RETURN
END

GO

select oldvalue from dbo.GetAttributeMaskOldValue(',10015,7,21,',',','systemuser,0000000-0000-0000-0000-000000000000~11~False','~')
where mask=7

We use these functions in our following query:

Declare @AuditTemp Table
(
    SystemUserId UniqueIdentifier,
    AccountId UniqueIdentifier,
    AttributeMask nvarchar(max),
    ChangeData nvarchar(max)
)

INSERT INTO @AuditTemp
    select a.UserId, a.ObjectId, a.AttributeMask,a.ChangeData
    from dbo.Audit a
        INNER JOIN dbo.SystemUser su on (su.SystemUserId = a.UserId)
        INNER join dbo.Account ac on a.ObjectId = ac.AccountId
        where 7 IN (select * from dbo.SplitAttributeMask(a.AttributeMask,','))
        AND ac.CustomerTypeCode = 1

    Update @AuditTemp set ChangeData = NULL  
    from @AuditTemp
    where ChangeData='' OR AttributeMask = ''

    Declare @QualifyingAccountTable Table
    (        
        AccountId UniqueIdentifier        
    )
    INSERT INTO @QualifyingAccountTable
    select distinct at.AccountId
    from @AuditTemp at      
    where (select oldvalue from dbo.GetAttributeMaskOldValue(at.AttributeMask,',',at.ChangeData,'~')
    where mask=7) = 11

Note: The update statement above is to avoid the following error:

Invalid length parameter passed to the LEFT or SUBSTRING function.

You will get this error when, for some AttributeMask columns in the AuditHistory table, there were no values (empty column) in the ChangeData.

The update statement will replace such values with null in the temporary table, and the condition is checked in the function, dbo.GetAttributeMaskOldValue, to filter out NULL values and allow the function to run only for NOT NULL values.

Comments
  • 0
    osiuxoq http://www.canticismo.it/ray-ban-oculos-wayfarer-ray-ban-oculos-wikipedia.html http://www.weather-rn.co.uk/ray-ban-predator-2-cheap-ray-ban-cat-3-uv400-ce.html http://www.abovenet.co.uk/oakley-vr28-lens-xanh-oakley-vr28-lens-x-change.html http://www.abovenet.co.uk/oakley-vault-gilroy-bjj-oakley-vault-gilroy-bowl.html http://www.abovenet.co.uk/oakley-vault-nj-retina-oakley-vault-nj-restaurants.html [url=http://www.abovenet.co.uk/oakley-ca-zillow-property-oakley-ca-zillow-phone.html]oakley ca zillow property[/url] [url=http://www.abovenet.co.uk/oakley-watches-pic-investments-oakley-watches-pic-inventory.html]oakley watches pic inventory[/url] [url=http://www.canticismo.it/ray-ban-vintage-sunglasses-ray-ban-vintage-frame.html]ray ban vintage frame[/url] [url=http://www.abovenet.co.uk/oakley-marshall-glasses-parts-replacement-parts-for-oakley-glasses.html]replacement parts for oakley glasses[/url] [url=http://www.abovenet.co.uk/oakley-si-nsn-nike-sfb-vs-oakley-si.html]nike sfb vs oakley si[/url]
  • 0
    I have checked your blog and i've found some duplicate content, that's why you don't rank high in google's search results, but there is a tool that can help you to create 100% unique articles, search for: boorfe's tips unlimited content
  • 0
    After research a number of of the blog posts in your website now, and I really like your manner of blogging. I bookmarked it to my bookmark website checklist and shall be checking back soon. Pls check out my website online as effectively and let me know what you think.
  • 0
    I impressed, I must say. Actually hardly ever do I encounter a weblog thateach educative and entertaining, and let me let you know, you've hit the nail on the head. Your idea is excellent; the issue is something that not enough persons are talking intelligently about. I am very blissful that I stumbled throughout this in my search for something regarding this.
  • 0
    Hi there, this weekend is nice designed for me, as this time i am reading this enormous educational post here at my residence.
  • 0
    Oh my goodness! Amazing article dude! Thanks, However I am encountering problems with your RSS. I don't know the reason why I can't subscribe to it. Is there anyone else having the same RSS problems? Anyone that knows the answer will you kindly respond? Thanx!!
  • 0
    Good day! Would you mind if I share your blog with my facebook group? There's a lot of folks that I think would really appreciate your content. Please let me know. Thanks
  • 0
    Hello there! Do you know if they make any plugins to help with SEO? I'm trying to get my blog to rank for some targeted keywords but I'm not seeing very good results. If you know of any please share. Thank you!
  • 0
    I got what you mean,saved to bookmarks, very dcent interrnet site.
  • 0
    Quality posts is the crucial to attract the viewers to go to see the web site, that's what this site is providing.
  • 0
    Spot on with this write-up, I honestly believe that this amazing site needs far more attention. I'll probably be back again to read through more, thanks for the info!
  • 0
    Greetings! This is my 1st comment here so I just wanted to give a quick shout out and tell you I really enjoy reading your blog posts. Can you recommend any other blogs/websites/forums that go over the same subjects? Thank you so much!
  • 0
    Hey I know this is off topic but I was wondering if you knew of any widgets I could add to my blog that automatically tweet my newest twitter updates. I've been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something like this. Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.
  • 0
    continuously i used to read smaller content that also clear their motive, and that is also happening with this paragraph which I am reading at this time.
  • 0
    Hey! Do you know if they make any plugins to help with Search Engine Optimization? I'm trying to get my blog to rank for some targeted keywords but I'm not seeing very good success. If you know of any please share. Kudos!
  • 0
    Heya! I'm at work surfing around your blog from my new iphone 3gs! Just wanted to say I love reading through your blog and look forward to all your posts! Keep up the great work!
  • 0
    Hi there, after reading this awesome piece of writing i am as well happy to share my experience here with friends.
  • 0
    At this time I am ready to do my breakfast, afterward having my breakfast coming yet again to read further news.
  • 0
    Great goods from you, man. I have understand your stuff previous to and you're just extremely excellent. I actually like what you have acquired here, really like what you're stating and the way in which by which you are saying it. You're making it entertaining and you still take care of to stay it wise. I can not wait to learn far more from you. This is really a tremendous site.
  • 0
    Hi to every one, it's actually a pleasant for me to go to see this website, it contains useful Information.
  • 0
    We stumbled over here coming from a different page and thought I should check things out. I like what I see so now i am following you. Look forward to looking at your web page again.
  • 0
    Hello There. I found your blog using msn. This is an extremely well written article. I'll be sure to bookmark it and return to read more of your useful info. Thanks for the post. I will definitely comeback.
  • 0
    Ahaa, its fastidious dialogue concerning this article here at this website, I have read all that, so at this time me also commenting here.
  • 0
    I constantly spent my half an hour to read this blog's articles daily along with a mug of coffee.
  • 0
    Wonderful beat ! I would like to apprentice while you amend your site, how could i subscribe for a blog web site? The account helped me a acceptable deal. I had been a little bit acquainted of this your broadcast provided bright clear idea
  • 0
    Hey there! I've been reading your website for a long time now and finally got the bravery to go ahead and give you a shout out from Huffman Tx! Just wanted to tell you keep up the great job!
  • 0
    Hello, just wanted to tell you, I loved this blog post. It was helpful. Keep on posting!
  • 0
    Very nice article, exactly what I wanted to find.
  • 0
    Nice blog! Is your theme custom made or did you download it from somewhere? A theme like yours with a few simple adjustements would really make my blog shine. Please let me know where you got your theme. Thank you
  • 0
    With havin so much content do you ever run into any issues of plagorism or copyright infringement? My blog has a lot of exclusive content I've either created myself or outsourced but it seems a lot of it is popping it up all over the web without my authorization. Do you know any solutions to help protect against content from being ripped off? I'd definitely appreciate it.
  • 0
    You actually make it appear so easy together with your presentation however I find this topic to be actually one thing which I think I might never understand. It sort of feels too complex and very vast for me. I am having a look forward on your next post, I will attempt to get the hold of it!
  • 0
    Thanks on your marvelous posting! I definitely enjoyed reading it, you will be a great author.I will make sure to bookmark your blog and will often come back later in life. I want to encourage one to continue your great job, have a nice afternoon!
    <