(SQL Server) Azure Monitor - List Activity Logs

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.

For more information, see https://docs.microsoft.com/en-us/rest/api/monitor/activitylogs/list

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT

    DECLARE @success int
    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 sp_OAMethod @http, 'QuickRequest', @resp 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'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonToken
        RETURN
      END


    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response Status Code: ' + @iTmp0

    DECLARE @jsonResponse int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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 @resp

        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @jsonResponse
        RETURN
      END

    EXEC @hr = sp_OADestroy @resp

    -- 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 @jsonResponse


END
GO