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 .
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 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.