When viewing all of the stored queries in Administration/Service Desk/Application Data/Stored Queries, I was hoping to find a way to identify the stored queries that people are now saving with the new 'saved searches' functionality.  It looks like they are all being given a random code of numbers and letters (example:  82F5D9CRj5Hm), so I'm not seeing anything unique about them to search on (like they all start with the letter A).  There also doesn't seem to be any new field on the stored query search screen to allow me to search on only the stored queries created from the saved searches.  Trying to look ahead, in case someone creates a saved search that causes system problems, is there a way that I'm just not seeing to identify the stored queries that analysts are now creating and saving?

I do see that there is a last modified by field on the query that will tell me who created it, so that's good.  Maybe the only way to identify these is to add the last modified by field to the list_crsq.htmpl form so that I can sort on that column to see the analyst names creating them?

Thanks.

Tammy

Hello Tammy,

I had a look at the data stored in the Cr_Stored_Queries (crsq) and User_Query (usq) tables when a Saved Search is done.

It looks like the field in Cr_Stored Queries called "uf_saved_search" is set to "1" when a user creates a Saved Search.

It's an undocumented field, so I have no idea if this is the only thing that it does, but the following query seems to return all Saved Searches. Please try it out and let us know.

From the "Stored Query List" under Administration, Service Desk, Application Data, Stored queries, enter the following Additional Search Argument:

• uf_saved_search = 1

Or if you wish to extract these from a command prompt:

• pdm_extract -f "select * from Cr_Stored_Queries where uf_saved_search ='1'"

Note: Run these with Administrator access, as the web client and pdm_extract obey security. Otherwise you'll only see what you are restricted to.

Of course, if you wish to add an Idea about enhancing this area, and cross link back here, I also think it would be useful to be able to more easily identify Saved Searches through the interface. I'd add my vote.

Thanks, Kyle_R.

Thanks, Kyle.  That worked great.  We upgraded to 14.1 on April 16, and there's 113 saved searches so far.  I guess that's not too bad for having over 1000 analysts.  So I assume that if I did identify one of these as causing a system performance issue, I could just inactivate it and it wouldn't work anymore -- right?

Thanks.

Tammy

One more question... Do you know if the saved search query name would be referenced in the log file if it were causing issues? 

Thanks,

Tammy

Hello Tammy,

Question: Is the Saved Search query name referenced in the log file if it were causing issues?

• No.
  The Saved Search  is not mentioned by name (Whether field label "code", "label" or "description") in the stdlog if the query runs for a long time.
• But . . .
  Any long running (over two second) query will write the SQL query used to the stdlog.

There has always been an issue though with easily tying back the SQL query you see in the stdlog back to a specific user's Scoreboard query.

It can be done, but "bleargh" may be an appropriate expression.

Note - if anyone has some good tips on this, it would be much appreciated by anyone who has faced performance tuning issues.

Example: How to identify who ran a poor performance query

Here is what the Saved Search, Stored Query Detail "Where Clause" looks like:

active = 1 AND priority = 3 AND (assignee IS NOT NULL OR group IS NOT NULL) AND (type = \'I\') and description LIKE \'QUOKKA\'

Here's what the Saved Search data  looks like:

TABLE Cr_Stored_Queries

code count_url criteria crsq_owner del description id label last_mod_by last_mod_dt obj_type persid tenant uf_saved_search usage_flag

{ "C4BDFCCR5Nwq" ,"", "active = 1 AND priority = 3 AND (assignee IS NOT NULL OR group IS NOT NULL) AND (type = \\0134'I\\0134') and description LIKE \\0134'QUOKKA\\0134'", "48F567C80F28EB4BA91567D03B4AFEE4" ,"0" ,"SavedSearch_Anna1" ,"400052", "SavedSearch_Anna1" ,"48F567C80F28EB4BA91567D03B4AFEE4", "05/24/2016 10:18:50" ,"cr" ,"crsq:400052" ,"" ,"1" ,"0" }

Here's what the stdlog query looks like:

(This is at TRACE level, but the ERROR level would look similar and run longer).

05/24 10:19:16.93 MY_SERVER  sqlagt:select0       4076 MILESTONE    sqlclass.c            1057 The following statement took 71 milliseconds: Clause (SELECT call_req.open_date, call_req.id, call_req.ref_num, call_req.id FROM call_req WHERE ( call_req.active_flag = 1 AND call_req.priority = 3 AND ( call_req.assignee IS NOT NULL OR call_req.group_id IS NOT NULL ) AND ( call_req.type = ? ) AND call_req.description LIKE ? ) AND ( ( call_req.group_id IN (SELECT grpmem.group_id FROM grpmem WHERE grpmem.member = ?) ) ) ORDER BY call_req.open_date DESC) Input (<string>I|<string>QUOKKA|<uuid>C4BDFCD53AA2D84EA04F74ECAE8B4965)

I know that this is the right query, because I deliberately added in this unique "QUOKKA" text to be sure that I was looking at the right entries:

"description LIKE \'QUOKKA\'"

The point is that the poorly performing query is written to the stdlog, but going back from the SQL that is generated to the Saved Search that gave it is a bit of a stretch. Due to the way that the query is built out, on first glance it does not look the same as the original query.

The Trouble-shooting

We know the stdlog entry, and wish to work back to the Stored Query that generated it.

The saving grace is that "uuid" at the end of the string.

Input (<string>I|<string>QUOKKA|<uuid>C4BDFCD53AA2D84EA04F74ECAE8B4965)
You can find out who was running the query, and thus with other techniques have a good chance to identify if this was a Saved Search or Scoreboard Query.

Add "U" to the front of the string.

Through the interface:

• id = U'C4BDFCD53AA2D84EA04F74ECAE8B4965'

Through pdm_extract:

• pdm_extract -f "select id, first_name, middle_name, last_name from ca_contact where id LIKE U'C4BDFCD53AA2D84EA04F74ECAE8B4965'"
  TABLE ca_contact
  id first_name middle_name last_name
  { "C4BDFCD53AA2D84EA04F74ECAE8B4965" ,"Anna" ,"" ,"Lyst" }

If you don't find a Stored Query which looks like it generated the result, then the user may have run the query "free form" by doing a custom Search, or performing any other number of actions.

However, if it was a Saved Search, then you will be able to identify it by the above method - eventually.

I hope that helps.

Thanks, Kyle_R.

Hi Kyle,

I was afraid you'd say that.. But thanks for the VERY helpful information on trying to figure out if the long running query is due to a saved search.

The only problem I see with all of this is that there can normally be many of these '1057 The following statement took 71 milliseconds:' messages in the log for queries that aren't really impacting performance (or at least don't seem to be).  So just identifying the one that is impacting performance seems to be pretty difficult.  Is the clue that you would find several of these for the same query in the log over and over again?

Thanks.

Tammy

Hello Tammy,

I just used 71 milliseconds because that was the message I had on hand. Do not worry about anything this small.

The actual ERROR message only writes at default logging levels at 2,000 milliseconds or above.

But I would recommend that you just use Notepad++ (or other tool) to extract all messages with "milliseconds" and only look at "the big ones."

Begin with your Top 5 or any that run longer than a minute for example, and if none of those, then those over 30 seconds (30000 milliseconds) and so on.

A useful Service Desk Manager tool: Notepad++

Note also that you're making an implicit assumption that it is long running queries that are "impacting performance." That may or may not be true, and depends very much on the symptoms of the poor performance.

In general, one off long queries will impact that specific user a lot - but not impact others that much.

We started off by talking about analysing Saved Searches - but troubleshooting performance is a whole other topic. Useful resources:

Troubleshooting CA Service Desk Manager - CA Service Management - 14.1 - CA Technologies Documentation

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec1848373.aspx

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec1017745.aspx

etc

Thanks, Kyle_R.

EDIT

I've also created two knowledge documents, based off this thread.

TEC1484879. How to list all "Saved Searches" in ITSM

TEC1892134. How to identify the real name of a Contact based off "id" in CA Service Desk Manager/ITSM.

Hi Kyle,

Oh, I knew that the 71 milliseconds one was just an example.  But I do appreciate the additional information on what might be considered 'too long'.  And I totally understand that long running queries aren't always the cause of a performance issue.  This thread started out with questions about saved searches for the purpose of being prepared for what I can do if we ever suspect one of these might be causing performance problems.  And I'm definitely referring to a performance issue that would be impacting everyone, not just the one doing the query.  Luckily, we aren't experiencing any right now, but I wanted to be a little prepared in case it does happen.

Thanks for all of the great information, as usual!

Tammy

Hi Kyle (or anyone else)...

I had asked earlier in this thread about 'saved searches' if inactivating a saved search would stop it from working -- But I'm not seeing that question answered.  So I'm asking it again....

We may need to disable the saved search functionality using the below commands to revise the nx.env files --

pdm_options_mgr -c -s DISABLE_SAVED_SEARCHES -v 1 -a pdm_option.inst

pdm_options_mgr -c -s DISABLE_SAVED_SEARCHES -v 1 -a pdm_option.inst -t

But I will also need any searches that were previously saved to stop working, because there may be one out there that is causing a problem (nothing in the log files though).  I tried inactivating a 'saved search' that I have, but it still worked on my scoreboard -- even after logging off/on and after clearing my cache.  How can an administrator make these saved searches no longer work?  Even if it produces an error for the analyst when they click on it, there should be a way for an admin to stop them from working.

Thanks.

Tammy

Hello Tammy,

The question "How can an administrator make these saved searches no longer work?" is functionally the same as "How do I remove a bad Stored Query from a User's Scoreboard?"  when talking about a specific Saved Search.

This is because once the Saved Search is done, it is to all intents and purposes just another Stored Query - so you may treat it as one.

See Most Common Issues When Working with Scoreboard for removing the query from one User's Scoreboard, which at the User_Query Table level, probably point (2).

Additionally, go nuclear on it at at the Cr_Stored_Queries Table if you wish.

Hopefully (you  may laugh now or later), your users won't get too far out of line, because they can only use Saved Searches to run Searches that they can do through the interface. Not only that, but they wouldn't go and Save and reuse a Search that doesn't run well, would they?  (I'll see myself out.)

Thanks, Kyle_R.

Hi Kyle,

First, I love an appreciate your humor... (I'll see myself out). 

Now onto the more serious stuff.  Before the new 'saved searches' functionality was introduced in 14.1, Administrators had control over what queries were on their scoreboards by setting them up initially for each role, and making sure that only queries that worked well were on the scoreboards.  Yes, analysts could customize their scoreboard and add other queries, but they were either OOTB, or created by an administrator (because only admins could create stored queries) -- and therefore should have been fully tested and working fine.  So there was definitely a control in place for helping to make sure they only had 'approved' queries on their scoreboard.  I have never needed to 'remove a bad query from a user's scoreboard' because we had those controls in place.

But now that the new 'saved searches' functionality allows any analyst to create any query they want through the interface (and yes, some of those can be performance impacting, especially now that they can add them to their scoreboard), why in the world would you not make a stored query stop working if it were inactivated by an administrator?  That just seems like a totally logical way to me for halting a problematic query in it's tracks -- or for totally remove all saved searches at one time if you are faced with needing to disable this new functionality after people have saved queries.  If not inactivating them, then there should be some mechanism provided for admins to just remove or 'shut down' all of the saved searches from the scoreboard (without trying to get over 1000 analysts who love their saved searches to agree to do a 'reset tree').

We have been using this system for over 4 years now, and trying to get over 1000 analysts do a 'reset tree' (or having me reset everyone's scoreboard back to the default), and then making those analysts have to manually re-add any legitimate additional queries to their scoreboard they previously had just isn't a practical solution.

Had I known when we upgraded to 14.1 that removing everyone's saved searches and disabling the functionality later would be this difficult, I would never have enabled it in the first place.  We are faced with needing to disable this functionality and remove these saved searches from the picture to try to resolve an issue that started happening all of a sudden where the scoreboard counts are not refreshing when you click on 'update counts'.  We upgraded to 14.1 in April, and this issue started on June 9, out of the blue, with no changes/patches being made on the system for over 2 weeks.  So the only thing we can think of (me and support) that might be causing this is a screwed up 'saved search' that someone has on their scoreboard.

Making the analysts lose this great new functionality would be hard enough to do, but now you're saying they would need to re-add all of their additional legitimate queries back.    Forgive me, but wow.  This is just difficult to accept.

(By the way, if you or anyone else has any other ideas what might be causing the scoreboard count refresh issue, please let me know!  We've already tried implementing the brel_cache_refresh nx.env setting (along with the monitor_joins option) and we increased the max db agents being used and increased the CPUs on the db server from 2 to 4 to try to minimize any performance impact.  But the warning that this might cause performance issues definitely happened -- 14 seconds to edit a ticket and 14 seconds to save a ticket.  So that fix just isn't going to work for us.

(and now I'll see myself out!   Haha...)

Thanks for listening....

Tammy

Thanks Tammy.

Sounds like an Idea is needed to log the request to have an Administrator to be able to deactivate a Saved Search via the interface. If you log it, I'll vote it up.

In the meantime, they would have to be removed from the User_Query Table.

You've been running a tighter shop than most - which is a good thing - but many sites allow Analysts to create and build their own Stored Queries. So having the potential for poor Stored Queries on ITSM 14.1 is not a "new" thing - it is just more apparent for your site because they can now come from another source.

Send me a Private Message with your issue number. I'll see if there is anything that I can do to assist. 

Thanks, Kyle_R.

Hi Kyle,

I can't seem to message you since you're not following me....

Hi Tammy,

Not sure why you can't message me - I thought this was opened up to avoid using that restriction. I'll check into that. In the meantime, I've Followed you.

The CA Support engineer also spotted this thread and provided me with the case number. I haven't had a chance to get a good look at it yet, but I'll be available as needed. It seems to be performance orientated.  If you're not seeing long running queries in the stdlogs related to the Stored Queries used by the Saved Searches, I'd say the problem lies elsewhere.

I've put a process to remove them below. I forgot to mention, but obviously if you want to hit ALL Saved Search Scoreboard Queries, then combine it with the "uf_saved_search = 1" advice earlier to find them.

Thanks, Kyle.

Idea has been posted --- Please vote up!

Disabling new 14.4 'saved search' functionality and deactivate existing saved searches

Done.

Everyone - go vote!

Thanks, Kyle_R.

tzadell and Kyle_R I want to have a beer (or more) with both of you! Sorry. Carry on!  

Hello Tammy,

This is a good time to do a recap on the Stored Query functionality in CA Service Desk Manager/ITSM.

Then I'll relate this back to your question of "How to deactivate a Stored Query on a User's Scoreboard?"

We'll look at this only from the point of view of Stored Queries that appear on a User's Scoreboard.

Stored Queries vs Scoreboard in CA Service Desk Manager

Stored Queries are the "SQL code" that goes onto Users' Scoreboards' "Nodes."

Stored Queries - Are the SQL strings that "do the work."

• Are stored in the Table Cr_Stored_Queries (crsq).
• Have an "In/Active" flag (del).
• One Stored Query can be referenced by multiple sources.
• If the Stored Query is changed, then this flows through to all users of that Stored Query.
• Key field that links to the User_Query table is "code".
• Name of the Stored Query is in "label".

Scoreboard - Displays the results of Stored Queries per User or Role

• Are stored in the Table User_Query (usq).
• Nodes does NOT have an In/Active flag.
• Is persistent to that User or Role until removed.
• If the underlying Stored Query is changed, this is reflected in the counts. (May need to log out/in.)
• Key field that links to the Cr_Stored_Queries table is "query".
• Name of the Node is in field "label".
• The Node name is user determined. It starts as the same as the Cr_Stored_Queries  "label" but may be user edited.

Example:

A User finds or locates a Stored Query.

Either:

• A user performs a "Saved Search" and creates a Stored Query called "SavedSearch_Administrator1".

Or:

• A user creates a Stored Query called "SavedSearch_Administrator1".

Or:

• This Stored Query is created by another, and they find it.

Or:

• The Stored Query is created by another and they get it from a Role.

They then:

• Edit their User Scoreboard to change the Node name to "SavedSearch_Administrator1_EDITED".

This generates the following Table entries:

TABLE Cr_Stored_Queries

code count_url criteria crsq_owner del description id label last_mod_by last_mod_dt obj_type persid tenant uf_saved_search usage_flag

{ "48F567CN03K1" ,"", "delete_flag = 0 AND last_name LIKE \\0134'Con%\\0134'", "48F567C80F28EB4BA91567D03B4AFEE2" ,"0" ,"SavedSearch_Administrator1", "400053" ,"SavedSearch_Administrator1" ,"48F567C80F28EB4BA91567D03B4AFEE2", "07/15/2016 13:59:02" ,"cnt" ,"crsq:400053" ,"" ,"1" ,"0" }

TABLE User_Query

expanded factory id label last_mod_by last_mod_dt obj_persid parent persid query query_set query_type role_persid sequence tenant

{ "0" ,"cnt" ,"401790" ,"SavedSearch_Administrator1_EDITED", "48F567C80F28EB4BA91567D03B4AFEE2" ,"07/15/2016 14:23:10", "cnt:48F567C80F28EB4BA91567D03B4AFEE2" ,"401679" ,"usq:401790", "48F567CN03K1" ,"0" ,"0" ,"role:10002" ,"62" ,"" }

The Cr_Stored_Queries Table (crsq) field "code" links to the User_Query Table (usq) field "query."

Key Points regarding Active vs Inactive

• Only Stored Queries can be made Active or Inactive.
• Nodes on the Scoreboard are either there, or not. They don't care whether the Scoreboard Query is Active or Inactive. They don't even have an Active flag.
• Making a Stored Query Inactive PREVENTS it from being ADDED to the Scoreboard, but it does NOT DISABLE EXISTING Scoreboard Nodes from working.

To repeat, making a Stored Query Inactive will prevent people from adding it to their Scoreboard. But it won't take away that Node from anyone who has it on their Scoreboard already.

Impact of the New Saved Search Functionality

Good or bad, this has been how the Scoreboard/Stored Query functionality has behaved since the beginning.

The new "Saved Search" functionality did not change the basic Scoreboard/Stored Query behaviour.

What it did do is to more easily allow Users to create and save their own Stored Queries and attach them to their personal Scoreboard.

They have always been able to do this (if permitted), but would previously have needed to write the Queries under the Administration tab.

So it is fair to say that access has been opened up.

I agree that it is a reasonable request that some additional Role or Access Type restrictions around this would be a good Idea to log. Rather than the "all or nothing" affair of the DISABLE_SAVED_SEARCHES variable.

Tammy's Idea is here. Please vote on this:

Disabling new 14.4 'saved search' functionality and deactivate existing saved searches

You can find other Scoreboard Ideas here.

Examples:

Make access to "Saved Searches" depend on Role/Access Type security, in SDM

Inactive Scoreboards

Force\Push\Insert Scoreboard Query

How to Remove a Stored Query from a User Scoreboard

Aside from Resetting the Tree or pushing out the Role Scoreboard again, or simply asking the user to remove the Node, an Administrator has the following backend options.

• Edit the existing Node and replace it either with a harmless query and/or a change to the Node Name such as "Node invalid - please see Administrator."
• Remove the whole Node completely.

As both processes are similar, I'll give just one set of steps.

CAUTION: This process involves direct database manipulation and can be destructive if care is not taken.

NOTE: The pdm_extract/pdm_load scenario is cross platform.

You may instead modify the  commands to work directly in the database if you wish.

Steps.

1) Identify the Stored Query which is the source of the problem.

See Example: How to identify who ran a poor performance query above in this thread.

The key item that you will need is the Cr_Stored_Queries Table (crsq) field "code".

The following extract shows both the code and the label.

pdm_extract -f "select code, label from Cr_Stored_Queries where code = '48F567CN03K1'"

TABLE Cr_Stored_Queries

        code label

        { "48F567CN03K1" ,"SavedSearch_Administrator1" }

Cr_Stored_Queries

        rows:1

Note: Instead of "where code" you may use "where label" if that is known instead. Eg:

pdm_extract -f "select code, label from Cr_Stored_Queries where label = 'SavedSearch_Administrator1'"

Note: If you are only interested in "Saved Searches", then remember that the Cr_Stored_Queries field of uf_saved_search = 1 for these.

2) Identify all Scoreboard Nodes that have this Stored Query and so need to be removed/edited.

In this example, it is just one user. To easily check that it is the right information, just a few fields can be extracted:

pdm_extract -f "Select query, label, persid, last_mod_by from User_Query where query  = '48F567CN03K1'"

TABLE User_Query

        query label persid last_mod_by

        { "48F567CN03K1" ,"SavedSearch_Administrator1_EDITED" ,"usq:401790", "48F567C80F28EB4BA91567D03B4AFEE2" }

User_Query

        rows:1

But if you want to Delete/Edit in Step (3), then also take all fields like so:

pdm_extract -f "Select * from User_Query where query  = '48F567CN03K1'" > usq_48F567CN03K1.txt

3a) Edit. If you want to leave the Node on the User's Scoreboard with a message.

• Modify the file. Save it to a copy  eg. usq_48F567CN03K1_MOD.txt
• Change the "label" eg ""Node inactivated - please see Administrator."
• Point the "query" to a known harmless query. For example, one which can only have zero results. Or one that points to a Closed Incident which documents more information about why the Node was made Inactive.
• Load the file:
  pdm_load -v -f usq_48F567CN03K1_MOD.txt
• Send the user an email to advise of the change.

3b) Remove. If you want the Node gone from the User's Scoreboard.

• Load the file with the "-r" option to "remove" it.

pdm_load -v -r -f usq_48F567CN03K1.txt

command = C:\PROGRA~2/CA/SERVIC~1\bin\dbload -v -r  usq_48F567CN03K1.txt

Processing usq_48F567CN03K1.txt

.
. <snip>

.

User_Query :

Rows   :1

Inserts:0

Updates:0

Errors :0

MaxKey :401790

Ending Totals:

Total tables :1

Total rows   :1

Total inserts:0

Total updates:0

Total errors :0

Highest Table keys processed     :User_Query(401790)

Files processed: 1

• Send the user an email to advise of the change.

NOTE: The user may need to log out and in of Service Desk Manager for the changes to take effect.

Any issues with the pdm_extract or pdm_load will write to the /log/stdlog when the "-v" switch is used.

I hope that this helps everyone to administer the Scoreboard to edit or remove unwanted Scoreboard queries.

Thanks, Kyle_R.

Thanks for all of the great information, Kyle.  Just to clarify, there are 2 support tickets.  One is for the scoreboard count refresh issue, and the other is for the performance issues we had after implementing the fix that was provided for the scoreboard count refresh issue (monitor_joins option and BREL_CACHE_REFRESH setting).  That fix comes with the warning that it might impact performance.  So, we were not having any performance issues in the system at all when the scoreboard count refresh issue started.  It was only after the fix was applied that we saw the performance issues that it warned about.

The reason we're looking at the saved searches functionality as a possible cause is only due to the fact that there were no recent changes to the system when the scoreboard count refresh issue started (June), and this new functionality that came with 14.1 (we upgraded in April) is related to the scoreboard.

If we do proceed with disabling the functionality, I don't think we'll be going the drastic route of setting everyone's scoreboard back to the defaults.  There are 130 saved searches that have been created by 33 analysts.  So I'm leaning towards just disabling the functionality, inactivating all of the saved searches, and then contacting those 33 analysts, pleading my case about why we need to remove them, and walk them through customizing their scoreboard to delete their 'My Saved Searches' folder.  Part of my 'pleading' might be to tell them that if we find this is not causing the issue, we will turn the functionality back on and they can re-create and re-add their saved searches (but the verdict is still out on that decision....)

I'm just very puzzled why this scoreboard issue would suddenly occur out of the blue...

Thanks.
Tammy