Mixing
Who created this Extended Events session?
I have been asked to find out who created a certain Extended Events session on an Azure SQL Database. However, looking through the DMVs, there are plenty of attributes, but nothing to indicate when it was created, or by whom.
Is there a way to determine this?
Thank you.
azure-sql-database extended-events dmv asked Nov 21 '18 at 21:49
Pittsburgh DBAPittsburgh DBA
4,74022752
add a comment |
I have been asked to find out who created a certain Extended Events session on an Azure SQL Database. However, looking through the DMVs, there are plenty of attributes, but nothing to indicate when it was created, or by whom.
Is there a way to determine this?
Thank you.
azure-sql-database extended-events dmv asked Nov 21 '18 at 21:49
Pittsburgh DBAPittsburgh DBA
4,74022752
add a comment |
I have been asked to find out who created a certain Extended Events session on an Azure SQL Database. However, looking through the DMVs, there are plenty of attributes, but nothing to indicate when it was created, or by whom.
Is there a way to determine this?
Thank you.
azure-sql-database extended-events dmv asked Nov 21 '18 at 21:49
Pittsburgh DBAPittsburgh DBA
4,74022752
I have been asked to find out who created a certain Extended Events session on an Azure SQL Database. However, looking through the DMVs, there are plenty of attributes, but nothing to indicate when it was created, or by whom.
Is there a way to determine this?
Thank you.
azure-sql-database extended-events dmv azure-sql-database extended-events dmv asked Nov 21 '18 at 21:49
Pittsburgh DBAPittsburgh DBA
4,74022752
asked Nov 21 '18 at 21:49
Pittsburgh DBAPittsburgh DBA
4,74022752
asked Nov 21 '18 at 21:49
Pittsburgh DBAPittsburgh DBA
4,74022752
asked Nov 21 '18 at 21:49
Pittsburgh DBAPittsburgh DBA
4,74022752
asked Nov 21 '18 at 21:49
Pittsburgh DBAPittsburgh DBA
4,74022752
4,74022752
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
In Azure this can be done but you have to have SQL Auditing enabled either on the database or server level. Then you have to execute the following using the sys.fn_get_audit_file function:
SET NOCOUNT ON;
SELECT
server_principal_id
, database_principal_id
, target_server_principal_id
, target_database_principal_id
, session_server_principal_name
, server_principal_name
, server_principal_sid
, database_principal_name
, target_server_principal_name
, target_server_principal_sid
, target_database_principal_name
, server_instance_name
, database_name
, schema_name
, object_name
, statement
, additional_information
FROM sys.fn_get_audit_file(
'https://blob_storage_name.blob.core.windows.net/sqldbauditlogs/SERVER_NAME/DATABASE_NAME/SqlDbAuditing_ServerAudit/2018-11-27' -- INSERT date here
, DEFAULT
, DEFAULT
)
WHERE statement LIKE '%CREATE EVENT SESSION%';
This should give you back the information you need. Keep in mind that SQL Auditing can generate A LOT of data, so you may need to query the audit files per day or even per hour (you can read how date patterns are used with sys.fn_get_audit_file here).
If you find the amount of data too big to query you can always download the audit files (.xel files, SQL Auditing is implemented via Extended Events) and write a custom tool to do that (Microsoft is offering a library to parse Extended Event files via LINQ. See details here).
answered Nov 27 '18 at 17:18
S.KarrasS.Karras
1,1871017
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420943%2fwho-created-this-extended-events-session%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
In Azure this can be done but you have to have SQL Auditing enabled either on the database or server level. Then you have to execute the following using the sys.fn_get_audit_file function:
SET NOCOUNT ON;
SELECT
server_principal_id
, database_principal_id
, target_server_principal_id
, target_database_principal_id
, session_server_principal_name
, server_principal_name
, server_principal_sid
, database_principal_name
, target_server_principal_name
, target_server_principal_sid
, target_database_principal_name
, server_instance_name
, database_name
, schema_name
, object_name
, statement
, additional_information
FROM sys.fn_get_audit_file(
'https://blob_storage_name.blob.core.windows.net/sqldbauditlogs/SERVER_NAME/DATABASE_NAME/SqlDbAuditing_ServerAudit/2018-11-27' -- INSERT date here
, DEFAULT
, DEFAULT
)
WHERE statement LIKE '%CREATE EVENT SESSION%';
This should give you back the information you need. Keep in mind that SQL Auditing can generate A LOT of data, so you may need to query the audit files per day or even per hour (you can read how date patterns are used with sys.fn_get_audit_file here).
If you find the amount of data too big to query you can always download the audit files (.xel files, SQL Auditing is implemented via Extended Events) and write a custom tool to do that (Microsoft is offering a library to parse Extended Event files via LINQ. See details here).
answered Nov 27 '18 at 17:18
S.KarrasS.Karras
1,1871017
add a comment |
In Azure this can be done but you have to have SQL Auditing enabled either on the database or server level. Then you have to execute the following using the sys.fn_get_audit_file function:
SET NOCOUNT ON;
SELECT
server_principal_id
, database_principal_id
, target_server_principal_id
, target_database_principal_id
, session_server_principal_name
, server_principal_name
, server_principal_sid
, database_principal_name
, target_server_principal_name
, target_server_principal_sid
, target_database_principal_name
, server_instance_name
, database_name
, schema_name
, object_name
, statement
, additional_information
FROM sys.fn_get_audit_file(
'https://blob_storage_name.blob.core.windows.net/sqldbauditlogs/SERVER_NAME/DATABASE_NAME/SqlDbAuditing_ServerAudit/2018-11-27' -- INSERT date here
, DEFAULT
, DEFAULT
)
WHERE statement LIKE '%CREATE EVENT SESSION%';
This should give you back the information you need. Keep in mind that SQL Auditing can generate A LOT of data, so you may need to query the audit files per day or even per hour (you can read how date patterns are used with sys.fn_get_audit_file here).
If you find the amount of data too big to query you can always download the audit files (.xel files, SQL Auditing is implemented via Extended Events) and write a custom tool to do that (Microsoft is offering a library to parse Extended Event files via LINQ. See details here).
answered Nov 27 '18 at 17:18
S.KarrasS.Karras
1,1871017
add a comment |
In Azure this can be done but you have to have SQL Auditing enabled either on the database or server level. Then you have to execute the following using the sys.fn_get_audit_file function:
SET NOCOUNT ON;
SELECT
server_principal_id
, database_principal_id
, target_server_principal_id
, target_database_principal_id
, session_server_principal_name
, server_principal_name
, server_principal_sid
, database_principal_name
, target_server_principal_name
, target_server_principal_sid
, target_database_principal_name
, server_instance_name
, database_name
, schema_name
, object_name
, statement
, additional_information
FROM sys.fn_get_audit_file(
'https://blob_storage_name.blob.core.windows.net/sqldbauditlogs/SERVER_NAME/DATABASE_NAME/SqlDbAuditing_ServerAudit/2018-11-27' -- INSERT date here
, DEFAULT
, DEFAULT
)
WHERE statement LIKE '%CREATE EVENT SESSION%';
This should give you back the information you need. Keep in mind that SQL Auditing can generate A LOT of data, so you may need to query the audit files per day or even per hour (you can read how date patterns are used with sys.fn_get_audit_file here).
If you find the amount of data too big to query you can always download the audit files (.xel files, SQL Auditing is implemented via Extended Events) and write a custom tool to do that (Microsoft is offering a library to parse Extended Event files via LINQ. See details here).
answered Nov 27 '18 at 17:18
S.KarrasS.Karras
1,1871017
In Azure this can be done but you have to have SQL Auditing enabled either on the database or server level. Then you have to execute the following using the sys.fn_get_audit_file function:
SET NOCOUNT ON;
SELECT
server_principal_id
, database_principal_id
, target_server_principal_id
, target_database_principal_id
, session_server_principal_name
, server_principal_name
, server_principal_sid
, database_principal_name
, target_server_principal_name
, target_server_principal_sid
, target_database_principal_name
, server_instance_name
, database_name
, schema_name
, object_name
, statement
, additional_information
FROM sys.fn_get_audit_file(
'https://blob_storage_name.blob.core.windows.net/sqldbauditlogs/SERVER_NAME/DATABASE_NAME/SqlDbAuditing_ServerAudit/2018-11-27' -- INSERT date here
, DEFAULT
, DEFAULT
)
WHERE statement LIKE '%CREATE EVENT SESSION%';
This should give you back the information you need. Keep in mind that SQL Auditing can generate A LOT of data, so you may need to query the audit files per day or even per hour (you can read how date patterns are used with sys.fn_get_audit_file here).
If you find the amount of data too big to query you can always download the audit files (.xel files, SQL Auditing is implemented via Extended Events) and write a custom tool to do that (Microsoft is offering a library to parse Extended Event files via LINQ. See details here).
answered Nov 27 '18 at 17:18
S.KarrasS.Karras
1,1871017
edited Nov 28 '18 at 9:30
answered Nov 27 '18 at 17:18
S.KarrasS.Karras
1,1871017
answered Nov 27 '18 at 17:18
S.KarrasS.Karras
1,1871017
answered Nov 27 '18 at 17:18
S.KarrasS.Karras
1,1871017
1,1871017
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420943%2fwho-created-this-extended-events-session%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
