none
AI Storing SQL Queries

    Question

  • Hi,

    Does anyone know how to prevent Application Insights Status monitor from storing SQL queries?

    We have a load of IIS sites which we are sending data to application insights using status monitor. The strange thing is, only one of the sites is sending SQL queries to app insights.

    We would like to turn this option off and on when we need to store the queries, is this possible?

    Thanks

    Shane


    • Edited by Shane W Wednesday, January 23, 2019 2:56 PM
    Wednesday, January 23, 2019 10:21 AM

Answers

  • Hi

    So the issues was that only one server was sending SQL queries to App Insights amd others were not. For the servers that were not, do the below.

    Turn the SQL query telemetry on by granting the IIS_USRS group modify rights to the below directories:

    %WINDIR%\Temp

    %WINDIR%\System32\config\systemprofile\AppData\Local

    SQL queries are then sent to App Insights, simply remove the permissions to stop them being sent.

    Thanks

    Shane

    • Marked as answer by Shane W Friday, February 22, 2019 9:31 AM
    Friday, February 22, 2019 9:31 AM

All replies

  • Hi Shane,

    Thanks for exploring Azure Application Insights.

    With the current product offering there isn’t any out of box option to turn on or off the SQL Query telemetry events being sent to Application Insights when using Application Insights Status Monitor.

    However please see if the below hack can help you. Make sure to try this out in any of your lower environments before trying it out in your Production environment and also make sure to take a backup of your Registry and IIS/Application configuration.  

    You can turn it off by deleting these three registry keys:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W3SVC\Environment

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\IISADMIN\Environment

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WAS\Environment

    The value of Environment will look like this:

    COR_ENABLE_PROFILING=1

    COR_PROFILER={324F817A-7420-4E6D-B3C1-143FBED6D855}

    MicrosoftInstrumentationEngine_Host={CA487940-57D2-10BF-11B2-A3AD5A13CBC0}

    Post the above step Telemetry will still be collected for all other apps and you will continue to collect dependency telemetry that you made SQL calls, but will not capture SQL Query text. But please be aware that this is a global registry key, so it’s either on for all apps or off. Also, re-installing of any app on this machine will restore those registry keys.

    Hope the above information helps.

    Friday, January 25, 2019 11:22 PM
    Owner
  • Hi

    So the issues was that only one server was sending SQL queries to App Insights amd others were not. For the servers that were not, do the below.

    Turn the SQL query telemetry on by granting the IIS_USRS group modify rights to the below directories:

    %WINDIR%\Temp

    %WINDIR%\System32\config\systemprofile\AppData\Local

    SQL queries are then sent to App Insights, simply remove the permissions to stop them being sent.

    Thanks

    Shane

    • Marked as answer by Shane W Friday, February 22, 2019 9:31 AM
    Friday, February 22, 2019 9:31 AM