SQL Server
SQL Server
Azure Monitor - List Activity Logs
See more Azure Monitor Examples
Provides the list of records from the activity logs.Note: The $filter criteria cannot specify a time range that begins more than 90 days in the past.
Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Load an OAuth2 access token previously fetched by this example: Get Azure OAuth2 Access Token
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/azureToken.json'
-- Assuming success..
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
EXEC sp_OAGetProperty @http, 'AuthToken', @sTmp0 OUT
PRINT 'AuthToken: ' + @sTmp0
EXEC sp_OASetProperty @http, 'Accept', 'application/json'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', 'https://management.azure.com/subscriptions/{subscriptionId}/providers/microsoft.insights/eventtypes/management/values?api-version=2015-04-01&$filter=eventTimestamp%20ge%20%272019-05-16T04%3A36%3A37.6407898Z%27%20and%20eventTimestamp%20le%20%272019-06-12T04%3A36%3A37.6407898Z%27', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response Status Code: ' + @iTmp0
DECLARE @jsonResponse int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResponse OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @jsonResponse, 'Load', @success OUT, @sTmp0
EXEC sp_OASetProperty @jsonResponse, 'EmitCompact', 0
EXEC sp_OAMethod @jsonResponse, 'Emit', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonResponse
RETURN
END
-- Sample output...
-- (See the parsing code below..)
--
-- Use the this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- {
-- "value": [
-- {
-- "authorization": {
-- "action": "microsoft.support/supporttickets/write",
-- "role": "Subscription Admin",
-- "scope": "/subscriptions/089bd33f-d4ec-47fe-8ba5-0753aa5c5b33/resourceGroups/MSSupportGroup/providers/microsoft.support/supporttickets/115012112305841"
-- },
-- "caller": "admin@contoso.com",
-- "claims": {
-- "aud": "https://management.core.windows.net/",
-- "iss": "https://sts.windows.net/72f988bf-86f1-41af-91ab-2d7cd011db47/",
-- "iat": "1421876371",
-- "nbf": "1421876371",
-- "exp": "1421880271",
-- "ver": "1.0",
-- "http://schemas.microsoft.com/identity/claims/tenantid": "1e8d8218-c5e7-4578-9acc-9abbd5d23315 ",
-- "http://schemas.microsoft.com/claims/authnmethodsreferences": "pwd",
-- "http://schemas.microsoft.com/identity/claims/objectidentifier": "2468adf0-8211-44e3-95xq-85137af64708",
-- "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn": "admin@contoso.com",
-- "puid": "20030000801A118C",
-- "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/nameidentifier": "9vckmEGF7zDKk1YzIY8k0t1_EAPaXoeHyPRn6f413zM",
-- "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname": "John",
-- "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/surname": "Smith",
-- "name": "John Smith",
-- "groups": "cacfe77c-e058-4712-83qw-f9b08849fd60,7f71d11d-4c41-4b23-99d2-d32ce7aa621c,31522864-0578-4ea0-9gdc-e66cc564d18c",
-- "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name": " admin@contoso.com",
-- "appid": "c44b4083-3bq0-49c1-b47d-974e53cbdf3c",
-- "appidacr": "2",
-- "http://schemas.microsoft.com/identity/claims/scope": "user_impersonation",
-- "http://schemas.microsoft.com/claims/authnclassreference": "1"
-- },
-- "correlationId": "1e121103-0ba6-4300-ac9d-952bb5d0c80f",
-- "description": "",
-- "eventDataId": "44ade6b4-3813-45e6-ae27-7420a95fa2f8",
-- "eventName": {
-- "value": "EndRequest",
-- "localizedValue": "End request"
-- },
-- "httpRequest": {
-- "clientRequestId": "27003b25-91d3-418f-8eb1-29e537dcb249",
-- "clientIpAddress": "192.168.35.115",
-- "method": "PUT"
-- },
-- "id": "/subscriptions/089bd33f-d4ec-47fe-8ba5-0753aa5c5b33/resourceGroups/MSSupportGroup/providers/microsoft.support/supporttickets/115012112305841/events/44ade6b4-3813-45e6-ae27-7420a95fa2f8/ticks/635574752669792776",
-- "level": "Informational",
-- "resourceGroupName": "MSSupportGroup",
-- "resourceProviderName": {
-- "value": "microsoft.support",
-- "localizedValue": "microsoft.support"
-- },
-- "operationId": "1e121103-0ba6-4300-ac9d-952bb5d0c80f",
-- "operationName": {
-- "value": "microsoft.support/supporttickets/write",
-- "localizedValue": "microsoft.support/supporttickets/write"
-- },
-- "properties": {
-- "statusCode": "Created"
-- },
-- "status": {
-- "value": "Succeeded",
-- "localizedValue": "Succeeded"
-- },
-- "subStatus": {
-- "value": "Created",
-- "localizedValue": "Created (HTTP Status Code: 201)"
-- },
-- "eventTimestamp": "2015-01-21T22:14:26.9792776Z",
-- "submissionTimestamp": "2015-01-21T22:14:39.9936304Z",
-- "subscriptionId": "089bd33f-d4ec-47fe-8ba5-0753aa5c5b33"
-- }
-- ],
-- "nextLink": "https://management.azure.com/########-####-####-####-############$skiptoken=######"
-- }
--
DECLARE @nextLink nvarchar(4000)
DECLARE @i int
DECLARE @count_i int
DECLARE @authorizationAction nvarchar(4000)
DECLARE @authorizationRole nvarchar(4000)
DECLARE @authorizationScope nvarchar(4000)
DECLARE @caller nvarchar(4000)
DECLARE @claimsAud nvarchar(4000)
DECLARE @claimsIss nvarchar(4000)
DECLARE @claimsIat nvarchar(4000)
DECLARE @claimsNbf nvarchar(4000)
DECLARE @claimsExp nvarchar(4000)
DECLARE @claimsVer nvarchar(4000)
DECLARE @claims_identity_claims_tenantid nvarchar(4000)
DECLARE @claims_claims_authnmethodsreferences nvarchar(4000)
DECLARE @claims_identity_claims_objectidentifier nvarchar(4000)
DECLARE @claims_ws_2005_05_identity_claims_upn nvarchar(4000)
DECLARE @claimsPuid nvarchar(4000)
DECLARE @claims_ws_2005_05_identity_claims_nameidentifier nvarchar(4000)
DECLARE @claims_ws_2005_05_identity_claims_givenname nvarchar(4000)
DECLARE @claims_ws_2005_05_identity_claims_surname nvarchar(4000)
DECLARE @claimsName nvarchar(4000)
DECLARE @claimsGroups nvarchar(4000)
DECLARE @claims_ws_2005_05_identity_claims_name nvarchar(4000)
DECLARE @claimsAppid nvarchar(4000)
DECLARE @claimsAppidacr nvarchar(4000)
DECLARE @claims_identity_claims_scope nvarchar(4000)
DECLARE @claims_claims_authnclassreference nvarchar(4000)
DECLARE @correlationId nvarchar(4000)
DECLARE @description nvarchar(4000)
DECLARE @eventDataId nvarchar(4000)
DECLARE @eventNameValue nvarchar(4000)
DECLARE @eventNameLocalizedValue nvarchar(4000)
DECLARE @httpRequestClientRequestId nvarchar(4000)
DECLARE @httpRequestClientIpAddress nvarchar(4000)
DECLARE @httpRequestMethod nvarchar(4000)
DECLARE @id nvarchar(4000)
DECLARE @level nvarchar(4000)
DECLARE @resourceGroupName nvarchar(4000)
DECLARE @resourceProviderNameValue nvarchar(4000)
DECLARE @resourceProviderNameLocalizedValue nvarchar(4000)
DECLARE @operationId nvarchar(4000)
DECLARE @operationNameValue nvarchar(4000)
DECLARE @operationNameLocalizedValue nvarchar(4000)
DECLARE @propertiesStatusCode nvarchar(4000)
DECLARE @statusValue nvarchar(4000)
DECLARE @statusLocalizedValue nvarchar(4000)
DECLARE @subStatusValue nvarchar(4000)
DECLARE @subStatusLocalizedValue nvarchar(4000)
DECLARE @eventTimestamp nvarchar(4000)
DECLARE @submissionTimestamp nvarchar(4000)
DECLARE @subscriptionId nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @nextLink OUT, 'nextLink'
SELECT @i = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_i OUT, 'value'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'I', @i
EXEC sp_OAMethod @jsonResponse, 'StringOf', @authorizationAction OUT, 'value[i].authorization.action'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @authorizationRole OUT, 'value[i].authorization.role'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @authorizationScope OUT, 'value[i].authorization.scope'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @caller OUT, 'value[i].caller'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claimsAud OUT, 'value[i].claims.aud'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claimsIss OUT, 'value[i].claims.iss'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claimsIat OUT, 'value[i].claims.iat'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claimsNbf OUT, 'value[i].claims.nbf'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claimsExp OUT, 'value[i].claims.exp'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claimsVer OUT, 'value[i].claims.ver'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claims_identity_claims_tenantid OUT, 'value[i].claims."http://schemas.microsoft.com/identity/claims/tenantid"'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claims_claims_authnmethodsreferences OUT, 'value[i].claims."http://schemas.microsoft.com/claims/authnmethodsreferences"'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claims_identity_claims_objectidentifier OUT, 'value[i].claims."http://schemas.microsoft.com/identity/claims/objectidentifier"'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claims_ws_2005_05_identity_claims_upn OUT, 'value[i].claims."http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn"'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claimsPuid OUT, 'value[i].claims.puid'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claims_ws_2005_05_identity_claims_nameidentifier OUT, 'value[i].claims."http://schemas.xmlsoap.org/ws/2005/05/identity/claims/nameidentifier"'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claims_ws_2005_05_identity_claims_givenname OUT, 'value[i].claims."http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname"'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claims_ws_2005_05_identity_claims_surname OUT, 'value[i].claims."http://schemas.xmlsoap.org/ws/2005/05/identity/claims/surname"'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claimsName OUT, 'value[i].claims.name'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claimsGroups OUT, 'value[i].claims.groups'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claims_ws_2005_05_identity_claims_name OUT, 'value[i].claims."http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name"'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claimsAppid OUT, 'value[i].claims.appid'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claimsAppidacr OUT, 'value[i].claims.appidacr'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claims_identity_claims_scope OUT, 'value[i].claims."http://schemas.microsoft.com/identity/claims/scope"'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @claims_claims_authnclassreference OUT, 'value[i].claims."http://schemas.microsoft.com/claims/authnclassreference"'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @correlationId OUT, 'value[i].correlationId'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @description OUT, 'value[i].description'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @eventDataId OUT, 'value[i].eventDataId'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @eventNameValue OUT, 'value[i].eventName.value'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @eventNameLocalizedValue OUT, 'value[i].eventName.localizedValue'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @httpRequestClientRequestId OUT, 'value[i].httpRequest.clientRequestId'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @httpRequestClientIpAddress OUT, 'value[i].httpRequest.clientIpAddress'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @httpRequestMethod OUT, 'value[i].httpRequest.method'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @id OUT, 'value[i].id'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @level OUT, 'value[i].level'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceGroupName OUT, 'value[i].resourceGroupName'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceProviderNameValue OUT, 'value[i].resourceProviderName.value'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceProviderNameLocalizedValue OUT, 'value[i].resourceProviderName.localizedValue'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @operationId OUT, 'value[i].operationId'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @operationNameValue OUT, 'value[i].operationName.value'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @operationNameLocalizedValue OUT, 'value[i].operationName.localizedValue'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @propertiesStatusCode OUT, 'value[i].properties.statusCode'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @statusValue OUT, 'value[i].status.value'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @statusLocalizedValue OUT, 'value[i].status.localizedValue'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @subStatusValue OUT, 'value[i].subStatus.value'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @subStatusLocalizedValue OUT, 'value[i].subStatus.localizedValue'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @eventTimestamp OUT, 'value[i].eventTimestamp'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @submissionTimestamp OUT, 'value[i].submissionTimestamp'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @subscriptionId OUT, 'value[i].subscriptionId'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonResponse
END
GO