CODEWITHSUNDEEP
azure-api-managementazure-data-explorer
David
David

Reputation: 1293

Azure Data Ingest to Data Explorer

I have an APIM policy which logs to event hub like this:-

<log-to-eventhub logger-id="eventhublogger" partition-id="0">
@{

    var body = context.Request.Body?.As<string>(true);
    if (body != null && body.Length > 1024)
    {
        body = body.Substring(0, 1024);
    }

    var json = new JObject( 
        new JProperty("DateTime", DateTime.Now),
        new JProperty("Method", context.Request.Method),
        new JProperty("Path", context.Request.Url.Path + context.Request.Url.QueryString),
        new JProperty("RequestBody", body) 
    ); 
    return json.ToString();


}
</log-to-eventhub>

I want to try and ingest this data from event hub into DataExplorer

I am unsure how I should create my Table mapping

I set up some blob storage and am forwarding the event hub data to it so I can see what it looks like. In the blob it looks like this:-

Objavro.codecnullavro.schema�{"type":"record","name":"EventData","namespace":"Microsoft.ServiceBus.Messaging","fields":[{"name":"SequenceNumber","type":"long"},{"name":"Offset","type":"string"},{"name":"EnqueuedTimeUtc","type":"string"},{"name":"SystemProperties","type":{"type":"map","values":["long","double","string","bytes"]}},{"name":"Properties","type":{"type":"map","values":["long","double","string","bytes","null"]}},{"name":"Body","type":["null","bytes"]}]}

� 106408(8/23/2019 4:41:18 AM

{ "DateTime": "2019-08-23T04:40:53.9151977+00:00", "Method": "POST", "Path": "/api/FuncCreateLead", "RequestBody": "{\r\n \"Title\": \"Miss\",\r\n \"FirstName\": \"Alice\"} }

Do I need to create a table mapping with the following fields?

SequenceNumber, Offset, EnqueuedTimeUtc, SystemProperties, Properties, Body?

Upvotes: 0

Views: 510

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

if this is your payload:

{
    "DateTime": "2019-08-23T04:40:53.9151977+00:00",
    "Method": "POST",
    "Path": "/api/FuncCreateLead",
    "RequestBody": {
        "Title": "Miss",
        "FirstName": "Alice"
    }
}

and this is the table creation command you've used for creating the Kusto/ADX table:

{ "DateTime": "2019-08-23T04:40:53.9151977+00:00", "Method": "POST", "Path": "/api/FuncCreateLead", "RequestBody": { "Title": "Miss", "FirstName": "Alice" } }

.create table TableName (
    DateTime: datetime,
    Method: string,
    Path: string,
    RequestBody: dynamic
)

then this is the mapping you would want to create

.create table TableName ingestion json mapping 'mapping_name' 
'['
'   {'
'       "column": "DateTime",'
'       "path": "$.DateTime",'
'       "datatype": "datetime"'
'   },'
'   {'
'       "column": "Method",'
'       "path": "$.Method",'
'       "datatype": "string"'
'   },'
'   {'
'       "column": "Path",'
'       "path": "$.Path",'
'       "datatype": "string"'
'   },'
'   {'
'       "column": "RequestBody",'
'       "path": "$.RequestBody",'
'       "datatype": "dynamic"'
'   }'
']'

accordingly, you can add/remove columns from the table definition/mapping according to your actual requirements - the above is just an example.

Upvotes: 1

Related Questions