CA Service Management

I am having the following issue when searching Change Orders:

1. From the scoreboard, click on the following folders "Change Orders > Assigned"
2. From the change order list area to the right, click on the "Show Filter" button
3. For the status box, enter "Review in Progress"
4. Click the "Search" button
5. The change orders show properly in descending order of the change order #
   • 
6. Click on the column "Change Order #" to sort in ascending order ... the change orders no longer show properly in ascending order of the change order #
   •  

Just for reference, I clicked on the "Show Filter" button to be sure there was no other setting done for sorting

Is this a bug or something else going on when I manually click on the change order # column to sort it in ascending order instead of the the default descending order that happens after you click on the search button?? I am only noticing this when I search Change Orders, not when searching Incidents or Requests.

thank you

Katherine

Hello Katherine,

It looks like ITSM 14.1 is sorting the "Change Order #" by Ref Num from the Scoreboard, and by "id" from within the Search.

However you're right, the Call Requests sort by Ref Num in both locations.

This normally wouldn't matter, as the ID and the Ref Num are in the same order, and the id is a primary key.

Somehow - I'm guessing here - your Ref Num and IDs are out of order compared to each other. 

So this is an issue for CA Support. (I vaguely recall something like this being reported a long time ago, but don't recall what happened to it. )

I've logged issue 00661404 for this against a generic site id.

Please send me a personal message with your Site ID, and I'll update the issue so that you have access to it, and we can get your patches <server name>.HIS file in and look into this.

(Note - there is probably a quicker customisation solution if someone wants to provide that here.)

Below I've mocked up a quick test where I knobbled the ID to be out of order directly from the database, which shows what is going on.

Thanks, Kyle_R.

Kyle,

I emailed you my information.

thanks

Katherine

Thanks. I'm engaging CA Customer Voice Team.

I'll continue all further technical correspondence in issue 00661404, until we get a result.

Kyle_R.

I double-checked my test. 
Requests, Incidents and Change Orders all in fact behave the same - they all exist as a string and are subject to the same constraints, and sort off id.

Hello Katherine,

There may be a simple reason for this behaviour.

The ticket "ref_num" is stored as a string.

This is done to allow prefixes and suffixes. Such as "EUR:108542" or "USA:152."

This is done in particular when two different systems are merged, to prevent ref_num ticket number collisions.

However, because ref_num is a string, it is not a good field for sorting on, as it is subject to sort issues.

For example, as sort on this list produces the "wrong" result even though being a correct sort of a string:

1
13
151
2
22
2078
3
4
45
5
etc

To get around this, the ref_num is sorted in lists by the "id" instead, which is an integer. Integers are always sorted correctly.

This typically works, because data entry and most integration points with CA SDM will keep the ref_num and id in the same sequential order.
It should be impossible to get a ref_num into a difference sequence than the id.

The only way this should be able to happen is via a data load of a table not sorted sequentially by ref_num or database manipulation.

If there is a way to reproduce an out of order alignment between the two, then we can look into that as an issue, because that should not happen. But it is correct behaviour that the ref_num column looks to the id for its sort order, due to the possibilities of strings in that column which do not lend themselves well to straight-forward sorts.

This could be changed for the product by logging an Idea - but it is best to address the root cause of why the ref_num and id are misaligned if possible.

Thanks, Kyle_R.

Hello Katherine,

Sorry - it took a while to get to the bottom of this. 

RESOLUTION SUMMARY

There are two issues here, which combined give the behaviour seen.

ISSUE 1 - Change Order List number sort is by "id" and not "ref_num."
This issue is by design to handle the string field sorting.

ISSUE 2 - Multi-server environments can get "id" and "ref_num" alignment different
This requires a low-level design change which cannot be accommodated in the current production release.
Defect DE37910 is remaining in a Deferred state for correction in a future release.

RESOLUTION DETAILS

ISSUE 1 - Change Order List number sort is by "id" and not "ref_num."

This issue is by design to handle the string field sorting.

The Change Order List Search uses "id" as the key sort field instead of the Change Order Number.
The reason for this choice is that the ticket "ref_num" is stored as a string.
This is done to allow prefixes and suffixes. Such as "EUR:108542" or "USA:152."

In particular it prevents ref_num ticket number collisions when two different systems are merged.

However, because ref_num is a string, it is not a good field for sorting on, as it is subject to sort issues.

For example, a sort on this string list produces the "wrong" numeric sort result, even though it is a "correct" sort of the string:

1
13
151
2
22
2078
3
4
45
5
etc

To get around this, the ref_num is sorted in lists by the "id" instead, which is an integer. Integers are always sorted correctly. This typically works, because data entry and most integration points with CA SDM will keep the ref_num and id in the same sequential order.

The expected behaviour is that it should be impossible to get a ref_num into a difference sequence than the id.

The only exception should be via a direct data load of a table not sorted sequentially by ref_num or database manipulation. However, best practice should keep this in-line also.

The above functionality is correct as per the design of the product, and is not under consideration for change at this point.

ISSUE 2 - Multi-server environments can get "id" and "ref_num" alignment different

Under some circumstances in multi-server environments, it is possible for the "id" and "ref_num" sequential alignment to differ. This occurs due to the number distribution and caching mechanism. The focus of the current design is primarily around preventing dual-allocation of ticket numbers, in an environment where servers are distributed.

This requires a low-level design change to correct. This cannot be accommodated in the current production release.
Defect DE37910 is remaining in a Deferred state for correction in a future release.

POTENTIAL WORKAROUNDS

There are no ideal workarounds. The best advice may be to treat this as a known issue and cover with user training.

If only reports or dashboards are needed, then they could be run off the ref_num field.

A non-viable workaround is to periodically extract all of the data, rearrange into correct order and reimport. This is not tenable in practice due to the effort involved and data risk.

A customisation may address this issue, but would require further research. A colleague suggested the following. Please note though that it has not been tested and would need thorough review. Not only to confirm that it works, but that it has no other adverse impacts.

- - - -
Change the sort order of some domsets in Majic with a .mod file in site\mods\majic. Pay attention to the string "chg_ref_num DESC".

MODIFY FACTORY chg DOMSET chg_list_web "chg_ref_num DESC, sched_start_date=sched_start_date DESC+sched_end_date DESC, id DESC, risk DESC, mintime=min(attached_slas.time_to_violation), mintgt=min(target_times.target_time), status=status.sym, priority=priority.sym DESC, category=category.sym, chg_ref_num" "" STATIC;

MODIFY FACTORY chg DOMSET chg_list_web_active_only "chg_ref_num DESC, sched_start_date=sched_start_date DESC+sched_end_date DESC, id DESC, risk DESC, mintime=min(attached_slas.time_to_violation), mintgt=min(target_times.target_time), status=status.sym, priority=priority.sym DESC, category=category.sym, chg_ref_num" "" STATIC { WHERE "active=1"; };
- - - -

This changes falls outside of scope for CA Support, but a paid engagement via CA Services or a CA Business Partner could review this for you. Or the CA Community for Service Management has many users experienced with SPL who may be able to comment further.

WARNING: The change would also leave a site exposed to the string sorting raised in "Issue 1" above, and so is only valid on sites where pure numeric sorts are used, which currently have similar numeric lengths, such as most being six digits.

CLOSING COMMENTS

Apologies for the length of time in getting to root cause.
An apparently simple question required low-level analysis within the product to understand the behaviour.

If you do decide to go through a customisation approach to attempt further resolution in the meantime, I recommend looking into that code fragment, and reaching out here to the CA Communities in the first instance in a new post.

Otherwise, the Defect has been marked as "Deferred" - not "Closed" - and so is still valid for consideration in a future release.

Thanks, Kyle_R.