Deleting a case from CCD Datastore Database
This is a 2-stage process that requires you delete the case from the database first and then ssh to the Elastic Search VM to delete the same. Please ensure you have a valid ServiceNow Change Request to do so.
1. Delete cases from CCD Data Store Database [ccd-data-store-api-postgres-db-v15-prod.postgres.database.azure.com]
Hop on to the bastion and run the script below.
The script takes a .txt file as an argument. The file should contain a list of valid CCD Data Store 16-digit unique reference numbers that need deleting. Each case needs deleting from a number of tables in DataStore. The first two tables may be empty, but the bottom two will most certainly contain data.
#!/bin/bash
# Variables
DB_HOST="ccd-data-store-api-postgres-db-v15-prod.postgres.database.azure.com"
DB_USER="DTS\ Platform\ Operations\ SC"
DB_NAME="ccd_data_store"
DB_PASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)
# Loop through reference IDs in file passed as argument
while IFS= read -r referenceid; do
# Construct query to delete case from appropriate tables
QUERY=$(cat << EOF
BEGIN;
DELETE FROM case_users_audit WHERE case_data_id = (SELECT id FROM case_data WHERE reference = '$referenceid');
DELETE FROM case_users WHERE case_data_id = (SELECT id FROM case_data WHERE reference = '$referenceid');
DELETE FROM case_event_significant_items WHERE case_event_id IN (SELECT id FROM case_event WHERE case_data_id IN (SELECT id FROM case_data WHERE reference = '$referenceid'));
DELETE FROM case_event WHERE case_data_id = (SELECT id FROM case_data WHERE reference = '$referenceid');
DELETE FROM case_data WHERE reference = '$referenceid';
COMMIT;
EOF
)
# Run above query
psql "sslmode=require host=${DB_HOST} dbname=${DB_NAME} user=${DB_USER} port=5432 password=${DB_PASSWORD}" -c "${QUERY}"
done < "$1"
e.g.
./delete_cases_ccd.sh list_of_cases.txt > output.txt
You can review the results in the output.txt file.
2. Delete cases from Elastic Search
From the bastion, ssh into the appropriate Elastic Search VM and run the script below.
The script takes a .txt file as an argument. The file should contain a list of valid CCD Data Store 16-digit unique reference numbers that need deleting. It will loop through the .txt file, using each reference number to search for and delete the associated case from Elastic Search.
#!/bin/bash
# Loop through case references in file passed as argument
while IFS= read -r case_ref; do
# Construct query for Elastic Search
QUERY=$(jq -n \
--arg case_ref "$case_ref" \
'
{
query:{
bool:{
filter:{
term:{
"reference.keyword":$case_ref
}
}
}
}
}
')
# Search for case in elastic search
RESULT=$(curl -s -X GET 'localhost:9200/_search?pretty' -H 'Content-Type: application/json' -d "${QUERY}")
CASE_ID=$(echo "$RESULT" | jq -r '.hits.hits[0]._id')
INDEX_ID=$(echo "$RESULT" | jq -r '.hits.hits[0]._source.index_id')
echo "For value: $case_ref"
echo "Case ID: $CASE_ID, Index ID: $INDEX_ID"
# If case exists - delete it, otherwise print message
if [ "$INDEX_ID" != "null" ]; then
CASE=$(curl -s -X GET "localhost:9200/$INDEX_ID/_doc/$CASE_ID" | jq .)
curl -s -X DELETE "localhost:9200/$INDEX_ID/_doc/$CASE_ID" | jq .
else
date
echo "No valid index was found."
fi
done < "$1"
e.g.
./delete_cases_es.sh list_of_cases.txt > output.txt
You can review the results in the output.txt file.