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.
Configure the replace literals feature
Open the Spotlight Cloud Diagnostic Server application. You will find the application on the desktop of the computer where Spotlight Cloud is installed. Double click on the desktop icon.
Sign in with your Quest account.
The configuration panel opens. Click Diagnostic server.
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. |