CA Service Management

As a general rule, whenever an activity log is added to a ticket, if there was no user provided description, the action_desc field has the same content as the description field.  However, in tickets with lots of transfers, field updates or workflow manipulation the activity log is filled with rarely important info.

I'd like to be able to filter out all the system generated stuff to only see the comments that were added by a human, regardless of what activity type it is.   In simplest terms I want to add a filter that says   action_desc != description  but that fails to work.  So does using <>.   For that matter, so does using =.

If I compare either of those fields to a string, it works fine if I use like or not like.  Using = I get a SQL error that ntext and varchar fields can't use =.

I'd be ok with action_description not like description except that doesn't work either.  Just get syntax error.

So...

Part 1) How would do it in addition search arguments

Part 2) More importantly, I'd rather have a check mark in the filter that says something like 'Comments Only' that would perform the same filter criteria on Search.

Brett,

SQL does not allow you to do a direct comparison between ntext data type fields.  So what you have to do is first CAST it to an NVARCHAR(MAX) field, then compare it.  So you can use a query like this in SQL:

select * from act_log where CAST ([description] AS NVARCHAR(MAX)) != CAST ([action_desc] AS NVARCHAR(MAX))

I am not sure if that would work in the add'l search arguments but it might be worth a shot.  If that doesnt work for you then I dont have a solution for this at the moment.   Eventually we know we will have to get away from ntext fields as MS claims to be phasing it out in the near future for SQL. 

Thanks,

Jon I.

Jon,

   Yes, I'm aware of the SQL level issue and have done the same thing when I'm writing queries direct but the cast commands don't work in the SD GUI.  I was hoping that with the application layer sitting on top of SQL direct maybe there was some other option.

   You'd think this wouldn't be the first time someone said "I want an easy way to see the comment history."

-Brett

Hi Brett - i agree with you, however in all of my years here working on this product I have not had anyone ask the question.  Unfortunately I dont think there is a way to do it via the object layer.  That would need to be posted as an idea.  I think your use case is good, and many customers may benefit from the ability to use compare statements like that, so I think there is value in post the idea. 

Jon

That's me.  The pioneer.

Idea posted.   Ability to filter activity logs by user comment 

It got my vote    It has a good number of votes already!!    Can you do me a favor Brett and just mark this one as answered - you can mark either my, or your, post as an answer.  This way the loop is closed on this one and we work from the idea!

Thanks man!

Jon

Hi Brett.

Unfortunately, I need to agree to Jon's Statements. I think your question is related to a non supported majic to SQL translation when it comes to comparisons between two attribute values.

You might want to take a  look at the following idea : https://communities.ca.com/ideas/235733474-reworkimprove-majiq-to-sql-query-language-capability

especially the point : attribute support of right hand side of condition

I encourage you to vote and leave a comment for this idea if you like, and if not already done so.

Kind regards

..........Michael

Upvoted but will probably get around to adding a specific idea targetted towards my use case in the event that an alternate solution is proposed.