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!
  • 0
    Please let me know if you're looking for a article writer for your weblog. You have some really good articles and I feel I would be a good asset. If you ever want to take some of the load off, I'd absolutely love to write some articles for your blog in exchange for a link back to mine. Please send me an email if interested. Cheers!
  • 0
    continuously i used to read smaller articles or reviews that also clear their motive, and that is also happening with this piece of writing which I am reading here.
  • 0
    Wow, wonderful blog layout! How lengthy have you been running a blog for? you made running a blog look easy. The total look of your website is excellent, let alone the content material!
  • 0
    If some one wishes expert view regarding running a blog afterward i suggest him/her to pay a quick visit this web site, Keep up the fastidious work.
  • 0
    Have you ever considered about adding a little bit more than just your articles? I mean, what you say is fundamental and everything. Nevertheless imagine if you added some great images or videos to give your posts more, "pop"! Your content is excellent but with images and videos, this blog could certainly be one of the very best in its niche. Awesome blog!
  • 0
    Flipbooks can be customized based on requirement.
  • 0
    You are so awesome! I do not suppose I've read through anything like that before. So great to discover another person with a few original thoughts on this issue. Seriously.. thanks for starting this up. This site is something that is needed on the internet, someone with a bit of originality!
  • 0
    hey there and thank you for your information – I've definitely picked up something new from right here. I did however expertise a few technical issues using this web site, since I experienced to reload the web site many times previous to I could get it to load properly. I had been wondering if your hosting is OK? Not that I'm complaining, but sluggish loading instances times will sometimes affect your placement in google and could damage your high-quality score if advertising and marketing with Adwords. Well I'm adding this RSS to my e-mail and can look out for much more of your respective fascinating content. Make sure you update this again soon.
  • 0
    My programmer is trying to persuade me to move to .net from PHP. I have always disliked the idea because of the costs. But he's tryiong none the less. I've been using WordPress on numerous websites for about a year and am worried about switching to another platform. I have heard great things about blogengine.net. Is there a way I can transfer all my wordpress content into it? Any help would be greatly appreciated!
  • 0
    Do you have a spam issue on this site; I also am a blogger, and I was wanting to know your situation; we have created some nice methods and we are looking to exchange strategies with other folks, please shoot me an e-mail if interested.
  • 0
    Normally I do not learn post on blogs, but I would like to say that this write-up very pressured me to check out and do it! Your writing taste has been amazed me. Thanks, very great article.
  • 0
    Howdy very nice website!! Guy .. Beautiful .. Superb .. I'll bookmark your web site and take the feeds additionally? I am happy to seek out a lot of helpful info right here in the post, we want work out more techniques on this regard, thanks for sharing. . . . . .
  • 0
    When someone writes an paragraph he/she retains the plan of a user in his/her mind that how a user can be aware of it. So that's why this piece of writing is perfect. Thanks!
  • 0
    Having read this I believed it was really enlightening. I appreciate you spending some time and effort to put this information together. I once again find myself personally spending a lot of time both reading and leaving comments. But so what, it was still worthwhile!
  • 0
    Somebody necessarily assist to make seriously articles I would state. This is the first time I frequented your web page and so far? I amazed with the analysis you made to create this particular submit amazing. Excellent process!
  • 0
    Hello! Do you know if they make any plugins to safeguard against hackers? I'm kinda paranoid about losing everything I've worked hard on. Any recommendations?
  • 0
    I know this web site gives quality dependent articles and other material, is there any other website which gives these stuff in quality?
  • 0
    Excellent beat ! I wish to apprentice at the same time as you amend your website, how can i subscribe for a weblog web site? The account aided me a acceptable deal. I have been tiny bit acquainted of this your broadcast offered vibrant clear concept
  • 0
    I know this web page provides quality based content and additional stuff, is there any other site which gives these kinds of information in quality?
  • 0
    I am regular visitor, how are you everybody? This article posted at this web page is genuinely good.
  • 0
    I was recommended this web site via my cousin. I am now not positive whether this publish is written by means of him as nobody else recognise such targeted about my problem. You are incredible! Thanks!
  • 0
    Hi there, You have done an incredible job. I'll definitely digg it and personally suggest to my friends. I am confident they'll be benefited from this site.
  • 0
    I added a new blog post yesterday. There’s a smaller list than usual in it. I’m currently working on a bigger list. It’s going to take some time to compile it. I hope all of you are having a good week. I’m planning on doing some design changes to my site. I’m also thinking about adding some new things too. I’ll talk more about that in the days and weeks to come.
  • 0
    My brother recommended I might like this website. He was totally right. This post actually made my day. You can not imagine simply how much time I had spent for this info! Thanks!
  • 0
    Heya i'm for the first time here. I came across this board and I to find It really helpful & it helped me out a lot. I hope to provide one thing back and aid others such as you helped me.
  • 0
    It is not my first time to pay a visit this website, i am visiting this web page dailly and take fastidious data from here every day.
  • 0
    Since the admin of this website is working, no doubt very rapidly it will be renowned, due to its quality contents.
  • 0
    At this time I am ready to do my breakfast, after having my breakfast coming yet again to read further news.
  • 0
    I’m working on a new list. I’m hopeful that this one will be much bigger. I made some announcements about my future site plans. I’m going to be adding some new stuff soon. You’ll definitely want to stay tuned for that. Thanks for your time and have a good weekend!
  • 0
    I’m working on a new list. I’m hopeful that this one will be much bigger. I made some announcements about my future site plans. I’m going to be adding some new stuff soon. You’ll definitely want to stay tuned for that. Thanks for your time and have a good weekend!
  • 0
    Howdy! I know this is kinda off topic but I was wondering if you knew where I could get a captcha plugin for my comment form? I'm using the same blog platform as yours and I'm having trouble finding one? Thanks a lot!
  • 0
    Howdy! I know this is kinda off topic but I was wondering if you knew where I could get a captcha plugin for my comment form? I'm using the same blog platform as yours and I'm having trouble finding one? Thanks a lot!
  • 0
    Howdy! I know this is kinda off topic but I was wondering if you knew where I could get a captcha plugin for my comment form? I'm using the same blog platform as yours and I'm having trouble finding one? Thanks a lot!
  • 0
    Howdy! I know this is kinda off topic but I was wondering if you knew where I could get a captcha plugin for my comment form? I'm using the same blog platform as yours and I'm having trouble finding one? Thanks a lot!
  • 0
    Thanks to my father who shared with me regarding this website, this blog is truly amazing.
  • 0
    Thanks to my father who shared with me regarding this website, this blog is truly amazing.
  • 0
    Thanks to my father who shared with me regarding this website, this blog is truly amazing.
  • 0
    Thanks to my father who shared with me regarding this website, this blog is truly amazing.
  • 0
    What's up, for all time i used to check webpage posts here in the early hours in the morning, because i love to learn more and more.
  • 0
    Great blog! Is your theme custom made or did you download it from somewhere? A design like yours with a few simple adjustements would really make my blog stand out. Please let me know where you got your theme. Appreciate it
  • 0
    Are you looking to see and talk to some really sexy girls? These girls are super dirty and it doesn’t cost a single cent to talk to them. It’s wall to wall babes at this site. Just visit https://t.irtyc.com/cvezzegjk?aff_id=29696&offer_id=3664&bo=2779,2778,2777,2776,3391 and sign up for your free account. You’ll be talking to these total babes in a matter of no time flat!
  • 0
    Are you looking to see and talk to some really sexy girls? These girls are super dirty and it doesn’t cost a single cent to talk to them. It’s wall to wall babes at this site. Just visit https://t.irtyc.com/cvezzegjk?aff_id=29696&offer_id=3664&bo=2779,2778,2777,2776,3391 and sign up for your free account. You’ll be talking to these total babes in a matter of no time flat!
  • 0
    Are you looking to see and talk to some really sexy girls? These girls are super dirty and it doesn’t cost a single cent to talk to them. It’s wall to wall babes at this site. Just visit https://t.irtyc.com/cvezzegjk?aff_id=29696&offer_id=3664&bo=2779,2778,2777,2776,3391 and sign up for your free account. You’ll be talking to these total babes in a matter of no time flat!
  • 0
    Are you looking to see and talk to some really sexy girls? These girls are super dirty and it doesn’t cost a single cent to talk to them. It’s wall to wall babes at this site. Just visit https://t.irtyc.com/cvezzegjk?aff_id=29696&offer_id=3664&bo=2779,2778,2777,2776,3391 and sign up for your free account. You’ll be talking to these total babes in a matter of no time flat!
  • 0
    There’s been a lot of changes since I last left a comment about my site. I’m starting to add the new features that I’ve wanted to. I hope in the next few weeks that all of it will be up and running. It’s going to take time and I haven’t had much of it to dedicate to this project. I thank you all for visiting my site and I hope you have a great weekend!
  • 0
    There’s been a lot of changes since I last left a comment about my site. I’m starting to add the new features that I’ve wanted to. I hope in the next few weeks that all of it will be up and running. It’s going to take time and I haven’t had much of it to dedicate to this project. I thank you all for visiting my site and I hope you have a great weekend!
  • 0
    Wonderful, what a weblog it is! This blog gives useful facts to us, keep it up.
  • 0
    There’s been a lot of changes since I last left a comment about my site. I’m starting to add the new features that I’ve wanted to. I hope in the next few weeks that all of it will be up and running. It’s going to take time and I haven’t had much of it to dedicate to this project. I thank you all for visiting my site and I hope you have a great weekend!
  • 0
    There’s been a lot of changes since I last left a comment about my site. I’m starting to add the new features that I’ve wanted to. I hope in the next few weeks that all of it will be up and running. It’s going to take time and I haven’t had much of it to dedicate to this project. I thank you all for visiting my site and I hope you have a great weekend!
  • 0
    There’s been a lot of changes since I last left a comment about my site. I’m starting to add the new features that I’ve wanted to. I hope in the next few weeks that all of it will be up and running. It’s going to take time and I haven’t had much of it to dedicate to this project. I thank you all for visiting my site and I hope you have a great weekend!
  • 0
    How does relaxing and having a good time with a pretty blonde sound? If it sounds like an exciting way to spend your time, then you need to check out http://www.camgirl.pw/lena___/ She is a real doll and this cam girl knows what you want to see. You’ll never ever be bored when the two of you are all alone. Want to have the best time possible? Take this girl directly into private and she’ll put a smile on your face.
  • 0
    How does relaxing and having a good time with a pretty blonde sound? If it sounds like an exciting way to spend your time, then you need to check out http://www.camgirl.pw/lena___/ She is a real doll and this cam girl knows what you want to see. You’ll never ever be bored when the two of you are all alone. Want to have the best time possible? Take this girl directly into private and she’ll put a smile on your face.
  • 0
    How does relaxing and having a good time with a pretty blonde sound? If it sounds like an exciting way to spend your time, then you need to check out http://www.camgirl.pw/lena___/ She is a real doll and this cam girl knows what you want to see. You’ll never ever be bored when the two of you are all alone. Want to have the best time possible? Take this girl directly into private and she’ll put a smile on your face.
  • 0
    Every guy needs to know a cam girl like http://www.camgirl.pw/wildtequilla/ She is a doll. You’ll have a whole lot of fun with any girl at this site. The type of fun that will put a smile on your face for days to come.
  • 0
    Every guy needs to know a cam girl like http://www.camgirl.pw/wildtequilla/ She is a doll. You’ll have a whole lot of fun with any girl at this site. The type of fun that will put a smile on your face for days to come.
  • 0
    Every guy needs to know a cam girl like http://www.camgirl.pw/wildtequilla/ She is a doll. You’ll have a whole lot of fun with any girl at this site. The type of fun that will put a smile on your face for days to come.
  • 0
    Does your blog have a contact page? I'm having problems locating it but, I'd like to shoot you an e-mail. I've got some ideas for your blog you might be interested in hearing. Either way, great site and I look forward to seeing it expand over time.
  • 0
    Wonderful content, I’m going to invest more time exploring this subject matter.
     
  • 0
    Link exchange is nothing else ᥱxcept itt iis ϳust placng the otҺer person’s web site link on yoour page аt suitable рlace ɑnd otherr person wilⅼ аlso do samje in suppport օf yoᥙ.
     
  • 0
    You really make it appear really easy with your presentation but I find this topic to be really one thing that I think I would never understand.It seems too complex and extremely wide for me. I’m having a look forward in your next put up, I will try to get the cling of it!
     
  • 0
    I like the helpful information you provide in your articles.I will bookmark your weblog and check again here regularly.
     
  • 0
    Hello there. I found your blog by the use of Google while searching for a comparable matter, your website came up. It appears good. I have bookmarked it in my google bookmarks to visit then.
     
Post a comment