Spotlight Cloud replaces string and numeric literals in uploaded SQL statements and queries by default as they may contain sensitive or Personally Identifiable Information (PII). The placeholder data has no reference to the original data, cannot be reversed into the original data, and has no association with the original data.

Replace Literals

Configure the replace literals feature

  1. Open the Spotlight Cloud Diagnostic Server App. You will find the app on the desktop of the computer where Spotlight Cloud is installed. Double click on the desktop icon. Spotlight Cloud Diagnostic Server App

  2. Sign in with your Quest account, and then from the Configuration panel, click Diagnostic server. Diagnostic Server

  3. Select Replace literals.

These feature settings apply to all connections monitored through this Spotlight Cloud Diagnostic Server.

Fill in the replace string literals option

Select the replace string literals option

This is the default setting. Spotlight Cloud replaces string literals found in SQL statements and queries with placeholder data. This placeholder data has no reference to the original data which may contain sensitive or Personally Identifiable Information (PII). This placeholder data cannot be reversed into the original data and has no association with that data.

De-select the replace string literals option

Spotlight Cloud will no longer replace string literals in SQL statements and queries with placeholder data. String literals inside uploaded SQL statements and queries may contain sensitive or Personally Identifiable Information (PII).

Fill in the replace numeric literals option

Select the replace numeric literals option

This is the default setting. Spotlight Cloud replaces numeric literals found in SQL statements and queries with placeholder data. This placeholder data has no reference to the original data which may contain sensitive or Personally Identifiable Information (PII). This placeholder data cannot be reversed into the original data and has no association with that data.

De-select the replace numeric literals option

Spotlight Cloud will no longer replace numeric literals in SQL statements and queries with placeholder data. Numeric literals inside uploaded SQL statements and queries may contain sensitive or Personally Identifiable Information (PII).

Expected output replacing string literals

Spotlight Cloud replaces string literals with placeholder data. The placeholder data reads: ** Removed by Spotlight **. Following are some examples.

Note that literals are always surrounded with single quotes (‘) unless SET QUOTED_IDENTIFIER is used to change the behavior.

Before uploading to Spotlight Cloud On upload to Spotlight Cloud string literals are replaced
select @dog select @dog
select ‘dog’ select ‘** Removed by Spotlight **’
select ‘dog’ and ‘TIGER select ‘** Removed by Spotlight **’ and ‘** Removed by Spotlight **
select ‘dog’ SET QUOTED_IDENTIFIER OFF; select “tiger” select ‘** Removed by Spotlight **’ SET QUOTED_IDENTIFIER OFF; select “** Removed by Spotlight **”
select ‘dog’ SET QUOTED_IDENTIFIER OFF; select “tiger” SET QUOTED_IDENTIFIER ON select “wolf” select ‘** Removed by Spotlight **’ SET QUOTED_IDENTIFIER OFF; select “** Removed by Spotlight **” SET QUOTED_IDENTIFIER ON select “wolf”
select ‘dog’ /* this is a comment ‘Next’ line */ ‘frog’ select ‘** Removed by Spotlight **’ /* this is a comment ‘Next’ line */ ‘** Removed by Spotlight **’
select payment where ccn = ‘12345678901234’; select payment where ccn = ‘** Removed by Spotlight **‘;
‘select top(526) from aaa’ ‘** Removed by Spotlight **’
begin – get the data select target_data from sys.dm_xe_sessions s join sys.dm_xe_session_targets t on t.event_session_address = s.address where s.name = N’SpotlightWorkloadAnalysis_MEL602102_3843_mel602102_sql2008r2_sqlserver’ and t.target_name = N’ring_buffer’ – stop session begin – get the data select target_data from sys.dm_xe_sessions s join sys.dm_xe_session_targets t on t.event_session_address = s.address where s.name = N’** Removed by Spotlight **’ and t.target_name = N’** Removed by Spotlight **’ – stop session
select ‘dog’ ‘hare’ select ‘** Removed by Spotlight **’ ‘** Removed by Spotlight **’
select ‘dog’ – ‘hare’ select ‘** Removed by Spotlight **’ – ‘hare’
select ‘dog’ – ‘hare’ – select ‘** Removed by Spotlight **’ – ‘hare’ –
select ‘dog’ and “hare” select ‘** Removed by Spotlight **’ and “hare”
select ‘dog’ and “hare” and ‘dog’ select ‘** Removed by Spotlight **’ and “hare” and ‘** Removed by Spotlight **’
select ’d”og’ and ‘hare’ and ’d”og’ select ‘** Removed by Spotlight **’ and ‘** Removed by Spotlight **’ and ‘** Removed by Spotlight **’

Expected output replacing numeric literals

Spotlight Cloud replaces numeric literals with placeholder data. The placeholder data is a series of digits where the first digit is 1, the remaining digits are 0, the number of digits in the placeholder data is the same as in the original data. Following are some examples.

Spotlight Cloud comments at the end of each SQL statement where numeric literals have been replaced: /* Spotlight replaces numeric literals with placeholder data prior to uploading to the cloud. Refer to the Spotlight Cloud help for more information. */

Before uploading to Spotlight Cloud On upload to Spotlight Cloud numeric literals are replaced
select payment where ccn = 12345678901234; select payment where ccn = 10000000000000;
select payment where other = 0; select payment where other = 1;
declare \@Cmd nvarchar(255); declare \@Cmd nvarchar(100);
if @tmp=0.0; if @tmp=1.0;
select top(526) from aaa select top(100) from aaa
select payment where other = +12345; select payment where other = +10000;
select payment where other = -12345; select payment where other = -10000;

Not all numbers are replaced

Sample Data not replaced
/* You are number 66666. */ Numbers inside comments are not replaced
SELECT col1, col2 FROM AdventureWorks2014.dbo.sometable Numbers in variable names are not replaced.
declare \@123 int Numbers in variable names are not replaced.