HYDROLIX BLOG

Ponderings, insights and industry updates

Video streaming analysis with CMCD

June 9, 2022

Author: David Sztykman |

Tags: , , , , , ,

In this blog post we are going to see how we can leverage a new feature to extract CMCD information from Akamai realtime logs delivery.

The Common Media Client Data specification allows Media player clients to convey information to Content Delivery Networks (CDNs) with each object request.
This information is often useful in log analysis, QoS monitoring and delivery optimisation.
Session identification allows thousands of individual server log lines to be interpreted as a single user session, leading to a clearer picture of end-user quality of service.

Getting CMCD Data

CMCD information needs to be delivered to the CDN (and then to Hydrolix) for that, the video player needs to enable beaconing of information into the video fragment.
Akamai provides a player example here.

For this blog post we are using the Akamai Media Player and some sample stream where we have enabled DataStreamV2 to send data back to Hydrolix see the previous blog post – index and enrich Akamai logs in realtime for the setup.

The player is sending the CMCD data as part of the query parameter for the video fragment.
Here’s an example of such HTTP request generated:

https://host.akamaized.net/ans/ape/sc-gaFEAQ/a0_F0008.ts?CMCD=bl%3D47200%2Cbr%3D522%2Ccid%3D%22123-456%22%2Cd%3D6016%2Cmtp%3D128300%2Cot%3Da%2Csf%3Dh%2Csid%3D%22b248658d-1d1a-4039-91d0-8c08ba597da5%22

The logs generated by Akamai contain a field called queryStr which is a full query string called CMCD including all the parameters.
So in our case something like that:

"queryStr": "CMCD=bl%3D47200%2Cbr%3D522%2Ccid%3D%22123-456%22%2Cd%3D6016%2Cmtp%3D128300%2Cot%3Da%2Csf%3Dh%2Csid%3D%22b248658d-1d1a-4039-91d0-8c08ba597da5%22"

As you can see the information is URLEncoded and not very user friendly.

Extracting CMCD information

The CMCD specification defines all the keys and values we should expect.

For example:

Leveraging Hydrolix new realtime enrichment at ingest we are going to write a SQL statement which will extract the CMCD information.

The SQL statement need to get the queryString, URLDecode it, extract all the key/value after CMCD=.
Once we have those key/value we need to replace the key name with a proper definition and then either just put the value or transform the value based on the specification.

SELECT decodeURLComponent(queryStr) as url_cmcd,
if(startsWith(url_cmcd, 'CMCD='), substring(url_cmcd, 6), url_cmcd) as url,
flatten(extractAllGroupsVertical(assumeNotNull(url), '([^=,"]+)="?([^,="]+)"? ?')) as groups

I can run this example on the queryString:

SELECT
    decodeURLComponent('CMCD=bl%3D47200%2Cbr%3D522%2Ccid%3D%22123-456%22%2Cd%3D6016%2Cmtp%3D128300%2Cot%3Da%2Csf%3Dh%2Csid%3D%22b248658d-1d1a-4039-91d0-8c08ba597da5%22') AS url_cmcd,
    if(startsWith(url_cmcd, 'CMCD='), substring(url_cmcd, 6), url_cmcd) AS url,
    flatten(extractAllGroupsVertical(assumeNotNull(url), '([^=,"]+)="?([^,="]+)"? ?')) AS groups
FORMAT Vertical

Row 1:
──────
url_cmcd: CMCD=bl=47200,br=522,cid="123-456",d=6016,mtp=128300,ot=a,sf=h,sid="b248658d-1d1a-4039-91d0-8c08ba597da5"
url:      bl=47200,br=522,cid="123-456",d=6016,mtp=128300,ot=a,sf=h,sid="b248658d-1d1a-4039-91d0-8c08ba597da5"
groups:   ['bl','47200','br','522','cid','123-456','d','6016','mtp','128300','ot','a','sf','h','sid','b248658d-1d1a-4039-91d0-8c08ba597da5']

Functions to the rescue

Now we have an array of CMCD data it requires some custom functions to do this efficiently. Fortunately Hydrolix allows users to create and define custom functions on the platform, see the dictionaries user defined functions in our docs for more information.

We’ll use 4 different functions:

{
    "functions": {
        "cmcd_query_param": {
            "enabled": true,
            "sql": "(param, groups_arr) -> (multiIf(has(groups_arr, param), empty(groups_arr[indexOf(groups_arr, param)+1]) ?  NULL :groups_arr[indexOf(groups_arr, param)+1], NULL));"
        },
        "cmcd_object_type": {
            "enabled": true,
            "sql":"(object_type) -> multiIf(object_type  = 'av', 'muxed_audio_video', object_type = 'v', 'video_only', object_type = 'a', 'audio_only', object_type = 'm', 'manifest', object_type = 'i', 'init_segment', object_type = 'c', 'caption_subtitle', object_type = 'tt', 'timed_text_track', object_type = 'k', 'key_license_cert', object_type)"
        },
        
        "cmcd_stream_type": {
            "enabled": true,
            "sql":"(stream_type) -> multiIf(stream_type  = 'v', 'VOD', stream_ty = 'l', 'Live', stream_type)"
        },
        
        "cmcd_streaming_format": {
            "enabled": true,
            "sql":"(streaming_format) -> multiIf(streaming_format = 'h', 'hls', streaming_format = 'd', 'dash', streaming_format = 's', 'smooth_streaming', streaming_format = 'o', 'other', streaming_format)"
        }
    }
}

The first function cmcd_query_param takes 2 parameters, a key name and the array. It will match the name of the key in the array and then get the value right next in the array.

For example:

SELECT
    decodeURLComponent('CMCD=bl%3D47200%2Cbr%3D522%2Ccid%3D%22123-456%22%2Cd%3D6016%2Cmtp%3D128300%2Cot%3Da%2Csf%3Dh%2Csid%3D%22b248658d-1d1a-4039-91d0-8c08ba597da5%22') AS url_cmcd,
    if(startsWith(url_cmcd, 'CMCD='), substring(url_cmcd, 6), url_cmcd) AS url,
    flatten(extractAllGroupsVertical(assumeNotNull(url), '([^=,"]+)="?([^,="]+)"? ?')) AS groups,
    cmcd_query_param('bl', groups) AS cmcd_buffer_length

Row 1:
──────
url_cmcd:      CMCD=bl=47200,br=522,cid="123-456",d=6016,mtp=128300,ot=a,sf=h,sid="b248658d-1d1a-4039-91d0-8c08ba597da5"
url:           bl=47200,br=522,cid="123-456",d=6016,mtp=128300,ot=a,sf=h,sid="b248658d-1d1a-4039-91d0-8c08ba597da5"
groups:        ['bl','47200','br','522','cid','123-456','d','6016','mtp','128300','ot','a','sf','h','sid','b248658d-1d1a-4039-91d0-8c08ba597da5']
cmcd_buffer_length: 47200

Additional functions like cmcd_object_type will match the value and replace it with the proper information based on the CMCD specification. For example we have ot=a which will need to be replaced by the value audio_only.

For example:

SELECT
    decodeURLComponent('CMCD=bl%3D47200%2Cbr%3D522%2Ccid%3D%22123-456%22%2Cd%3D6016%2Cmtp%3D128300%2Cot%3Da%2Csf%3Dh%2Csid%3D%22b248658d-1d1a-4039-91d0-8c08ba597da5%22') AS url_cmcd,
    if(startsWith(url_cmcd, 'CMCD='), substring(url_cmcd, 6), url_cmcd) AS url,
    flatten(extractAllGroupsVertical(assumeNotNull(url), '([^=,"]+)="?([^,="]+)"? ?')) AS groups,
    cmcd_object_type(cmcd_query_param('ot', groups)) AS cmcd_object_type
FORMAT Vertical

Row 1:
──────
url_cmcd:         CMCD=bl=47200,br=522,cid="123-456",d=6016,mtp=128300,ot=a,sf=h,sid="b248658d-1d1a-4039-91d0-8c08ba597da5"
url:              bl=47200,br=522,cid="123-456",d=6016,mtp=128300,ot=a,sf=h,sid="b248658d-1d1a-4039-91d0-8c08ba597da5"
groups:           ['bl','47200','br','522','cid','123-456','d','6016','mtp','128300','ot','a','sf','h','sid','b248658d-1d1a-4039-91d0-8c08ba597da5']
cmcd_object_type: audio_only

The final query extracting all the CMCD information is as follows:

SELECT
    decodeURLComponent('CMCD=bl%3D47200%2Cbr%3D522%2Ccid%3D%22123-456%22%2Cd%3D6016%2Cmtp%3D128300%2Cot%3Da%2Csf%3Dh%2Csid%3D%22b248658d-1d1a-4039-91d0-8c08ba597da5%22') AS url_cmcd,
    if(startsWith(url_cmcd, 'CMCD='), substring(url_cmcd, 6), url_cmcd) AS url,
    flatten(extractAllGroupsVertical(assumeNotNull(url), '([^=,"]+)="?([^,="]+)"? ?')) AS groups,
    cmcd_query_param('bl', groups) AS cmcd_buffer_length,
    cmcd_query_param('br', groups) AS cmcd_encoded_bitrate,
    cmcd_query_param('bs', groups) AS cmcd_buffer_starvation,
    cmcd_query_param('cid', groups) AS cmcd_content_id,
    cmcd_query_param('d', groups) AS cmcd_object_duration,
    cmcd_query_param('dl', groups) AS cmcd_deadline,
    cmcd_query_param('mtp', groups) AS cmcd_measured_throughput,
    cmcd_query_param('nor', groups) AS cmcd_next_object_requests,
    cmcd_query_param('nrr', groups) AS cmcd_next_range_request,
    cmcd_object_type(cmcd_query_param('ot', groups)) AS cmcd_object_type,
    cmcd_query_param('pr', groups) AS cmcd_playback_rate,
    cmcd_query_param('rtp', groups) AS cmcd_requested_max_throughput,
    cmcd_streaming_format(cmcd_query_param('sf', groups)) AS cmcd_streaming_format,
    cmcd_query_param('sid', groups) AS cmcd_session_id,
    cmcd_stream_type(cmcd_query_param('st', groups)) AS cmcd_stream_type,
    cmcd_query_param('su', groups) AS cmcd_startup,
    cmcd_query_param('tb', groups) AS cmcd_top_bitrate,
    cmcd_query_param('v', groups) AS cmcd_version
FORMAT Vertical

Row 1:
──────
url_cmcd:                      CMCD=bl=47200,br=522,cid="123-456",d=6016,mtp=128300,ot=a,sf=h,sid="b248658d-1d1a-4039-91d0-8c08ba597da5"
url:                           bl=47200,br=522,cid="123-456",d=6016,mtp=128300,ot=a,sf=h,sid="b248658d-1d1a-4039-91d0-8c08ba597da5"
groups:                        ['bl','47200','br','522','cid','123-456','d','6016','mtp','128300','ot','a','sf','h','sid','b248658d-1d1a-4039-91d0-8c08ba597da5']
cmcd_buffer_length:            47200
cmcd_encoded_bitrate:          522
cmcd_buffer_starvation:        ᴺᵁᴸᴸ
cmcd_content_id:               123-456
cmcd_object_duration:          6016
cmcd_deadline:                 ᴺᵁᴸᴸ
cmcd_measured_throughput:      128300
cmcd_next_object_requests:     ᴺᵁᴸᴸ
cmcd_next_range_request:       ᴺᵁᴸᴸ
cmcd_object_type:              audio_only
cmcd_playback_rate:            ᴺᵁᴸᴸ
cmcd_requested_max_throughput: ᴺᵁᴸᴸ
cmcd_streaming_format:         hls
cmcd_session_id:               b248658d-1d1a-4039-91d0-8c08ba597da5
cmcd_stream_type:              ᴺᵁᴸᴸ
cmcd_startup:                  ᴺᵁᴸᴸ
cmcd_top_bitrate:              ᴺᵁᴸᴸ
cmcd_version:                  ᴺᵁᴸᴸ

Tie it all together

IIn the previous blog post Index and enrich Akamai logs in realtime we have already already shown how to extract and enriching logs using breadcrumbs.

Using the previous we can augment it with the CMCD information:

Akamai Transform with breadcrumbs and CMCD extract

### Global variable to replace with your own needs
@host = host
@projectname = sample
@tablename = akamai_logs
@transformname = akamai_sql_transform
@username = "user@company.com"
@password = "xxxxxxx"

@base_url = https://{{host}}.hydrolix.live/config/v1/
@post_endpoint = https://{{host}}.hydrolix.live/ingest/event
@sql_endpoint = https://{{host}}.hydrolix.live/query/

### Login authentication get access token  and UUID Org variable
# @name login
POST {{base_url}}login
Content-Type: application/json

{
    "username": {{username}},
    "password": {{password}}
}
### Store, parse the login response body to store the access token and organization id
@access_token = {{login.response.body.auth_token.access_token}}
@org_id = {{login.response.body.orgs[0].uuid}}


### Create a new project using the variable {{projectname}}
# @name new_project
POST  {{base_url}}orgs/{{org_id}}/projects/
Authorization: Bearer {{access_token}}
Content-Type: application/json

{
    "name": "{{projectname}}",
    "org": "{{org_id}}"
}
### Store, parse project ID from response
@projectid = {{new_project.response.body.uuid}}

### Create a new table named {{tablename}} in the {{projectname}}
# @name new_table
POST  {{base_url}}orgs/{{org_id}}/projects/{{projectid}}/tables/
Authorization: Bearer {{access_token}}
Content-Type: application/json

{
    "name": "{{tablename}}",
    "project": "{{projectid}}",
    "description": "Sample Table Akamai Logs",
    "settings": {
        "merge": {
            "enabled": true
        },
        "autoingest": {
            "enabled": false
        }
    }
}
### Store, parse table ID from response
@tableid = {{new_table.response.body.uuid}}

#### Creates a a transform for the json format and upload to our table
# @name new_transform
POST {{base_url}}orgs/{{org_id}}/projects/{{projectid}}/tables/{{tableid}}/transforms/
Authorization: Bearer {{access_token}}
Content-Type: application/json

{
    "name": "{{transformname}}",
    "description": "Demo Akamai Transform",
    "type": "json",
    "settings": {
       "is_default": true,
       "sql_transform": "SELECT now() - reqTimeSec as latency, decodeURLComponent(assumeNotNull(UA)) as UA, decodeURLComponent(assumeNotNull(referer)) as referer, decodeURLComponent(assumeNotNull(queryStr)) as queryStr, extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '\\\/\\\/BC\\\/(\\S*)') as breadcrumbs, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=g[^]]*\\])'), 'a=([^,\\]]+)') as Edge_IP, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=g[^]]*\\])'), 'b=([^,\\]]+)') as Edge_RequestID, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=g[^]]*\\])'), 'k=([^,\\]]+)') as Edge_RequestEndTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=g[^]]*\\])'), 'l=([^,\\]]+)') as Edge_TurnAroundTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=g[^]]*\\])'), 'm=([^,\\]]+)') as Edge_DNSLookupTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=g[^]]*\\])'), 'n=([^,\\]]+)') as Edge_GeoInfo, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=g[^]]*\\])'), 'o=([^,\\]]+)') as Edge_ASN, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=o[^]]*\\])'), 'a=([^,\\]]+)') as Origin_IP, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=o[^]]*\\])'), 'b=([^,\\]]+)') as Origin_RequestID, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=o[^]]*\\])'), 'k=([^,\\]]+)') as Origin_RequestEndTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=o[^]]*\\])'), 'l=([^,\\]]+)') as Origin_TurnAroundTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=o[^]]*\\])'), 'm=([^,\\]]+)') as Origin_DNSLookupTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=o[^]]*\\])'), 'n=([^,\\]]+)') as Origin_GeoInfo, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(\\[[^[]*c=o[^]]*\\])'), 'o=([^,\\]]+)') as Origin_ASN, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=p[^]]*\\])'), 'a=([^,\\]]+)') as Peer_IP, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=p[^]]*\\])'), 'b=([^,\\]]+)') as Peer_RequestID, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=p[^]]*\\])'), 'k=([^,\\]]+)') as Peer_RequestEndTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=p[^]]*\\])'), 'l=([^,\\]]+)') as Peer_TurnAroundTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=p[^]]*\\])'), 'm=([^,\\]]+)') as Peer_DNSLookupTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=p[^]]*\\])'), 'n=([^,\\]]+)') as Peer_GeoInfo, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=p[^]]*\\])'), 'o=([^,\\]]+)') as Peer_ASN, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=c[^]]*\\])'), 'a=([^,\\]]+)') as Parent_IP, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=c[^]]*\\])'), 'b=([^,\\]]+)') as Parent_RequestID, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=c[^]]*\\])'), 'k=([^,\\]]+)') as Parent_RequestEndTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=c[^]]*\\])'), 'l=([^,\\]]+)') as Parent_TurnAroundTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=c[^]]*\\])'), 'm=([^,\\]]+)') as Parent_DNSLookupTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=c[^]]*\\])'), 'n=([^,\\]]+)') as Parent_GeoInfo, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=c[^]]*\\])'), 'o=([^,\\]]+)') as Parent_ASN, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=w[^]]*\\])'), 'a=([^,\\]]+)') as CloudWrapper_IP, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=w[^]]*\\])'), 'b=([^,\\]]+)') as CloudWrapper_RequestID, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=w[^]]*\\])'), 'k=([^,\\]]+)') as CloudWrapper_RequestEndTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=w[^]]*\\])'), 'l=([^,\\]]+)') as CloudWrapper_TurnAroundTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=w[^]]*\\])'), 'm=([^,\\]]+)') as CloudWrapper_DNSLookupTime, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=w[^]]*\\])'), 'n=([^,\\]]+)') as CloudWrapper_GeoInfo, extract(extract(decodeURLComponent(assumeNotNull(breadcrumbs)), '(,\\[[^[]*c=w[^]]*\\])'), 'o=([^,\\]]+)') as CloudWrapper_ASN, assumeNotNull(decodeURLComponent(queryStr)) as url_cmcd, if(startsWith(url_cmcd, 'CMCD='), substring(url_cmcd, 6), url_cmcd) as url, flatten(extractAllGroupsVertical(assumeNotNull(url), '([^=,\"]+)=\"?([^,=\"]+)\"? ?')) as groups, cmcd_query_param('bl', groups) as cmcd_buffer_length, cmcd_query_param('br', groups) as cmcd_encoded_bitrate, cmcd_query_param('bs', groups) as cmcd_buffer_starvation, cmcd_query_param('cid', groups) as cmcd_content_id, cmcd_query_param('d', groups) as cmcd_object_duration, cmcd_query_param('dl', groups) as cmcd_deadline, cmcd_query_param('mtp', groups) as cmcd_measured_throughput, cmcd_query_param('nor', groups) as cmcd_next_object_requests, cmcd_query_param('nrr', groups) as cmcd_next_range_request, cmcd_object_type(cmcd_query_param('ot', groups)) as cmcd_object_type, cmcd_query_param('pr', groups) as cmcd_playback_rate, cmcd_query_param('rtp', groups) as cmcd_requested_max_throughput, cmcd_streaming_format(cmcd_query_param('sf', groups)) as cmcd_streaming_format, cmcd_query_param('sid', groups) as cmcd_session_id, cmcd_stream_type(cmcd_query_param('st', groups)) as cmcd_stream_type, cmcd_query_param('su', groups) as cmcd_startup, cmcd_query_param('tb', groups) as cmcd_top_bitrate, cmcd_query_param('v', groups) as cmcd_version, * FROM {STREAM}",
       "output_columns": [
          {
             "name": "version",
             "datatype": {
                "type": "uint8"
             }
          },
          {
             "name": "customField",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "reqId",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "reqTimeSec",
             "datatype": {
                "type": "epoch",
                "format": "s",
                "primary": true,
                "resolution": "s"
             }
          },
          {
             "name": "city",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "state",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "country",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "cacheStatus",
             "datatype": {
                "type": "boolean"
             }
          },
          {
             "name": "breadcrumbs",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "dnsLookupTimeMSec",
             "datatype": {
                "type": "uint32"
             }
          },
          {
             "name": "transferTimeMSec",
             "datatype": {
                "type": "uint32"
             }
          },
          {
             "name": "turnAroundTimeMSec",
             "datatype": {
                "type": "uint32"
             }
          },
          {
             "name": "errorCode",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "reqEndTimeMSec",
             "datatype": {
                "type": "uint32"
             }
          },
          {
             "name": "xForwardedFor",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "maxAgeSec",
             "datatype": {
                "type": "uint64"
             }
          },
          {
             "name": "referer",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "range",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "cookie",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "accLang",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "reqPort",
             "datatype": {
                "type": "uint32"
             }
          },
          {
             "name": "reqPath",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "reqMethod",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "reqHost",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "proto",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "statusCode",
             "datatype": {
                "type": "uint32"
             }
          },
          {
             "name": "cliIP",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "cp",
             "datatype": {
                "type": "uint32"
             }
          },
          {
             "name": "bytes",
             "datatype": {
                "type": "uint64"
             }
          },
          {
             "name": "rspContentLen",
             "datatype": {
                "type": "uint64"
             }
          },
          {
             "name": "rspContentType",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "UA",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "tlsOverheadTimeMSec",
             "datatype": {
                "type": "uint32"
             }
          },
          {
             "name": "tlsVersion",
             "datatype": {
                "type": "string"
             }
          },
          {
             "name": "objSize",
             "datatype": {
                "type": "uint64"
             }
          },
          {
             "name": "uncompressedSize",
             "datatype": {
                "type": "uint64"
             }
          },
          {
             "name": "overheadBytes",
             "datatype": {
                "type": "uint64"
             }
          },
          {
             "name": "totalBytes",
             "datatype": {
                "type": "uint64"
             }
          },
          {
             "name": "queryStr",
             "datatype": {
                "type": "string"
             }
          },
                {
        "name": "Edge_IP",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Edge_RequestID",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Edge_RequestEndTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Edge_TurnAroundTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Edge_DNSLookupTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Edge_GeoInfo",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Edge_ASN",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Origin_IP",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Origin_RequestID",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Origin_RequestEndTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Origin_TurnAroundTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Origin_DNSLookupTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Origin_GeoInfo",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Origin_ASN",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Parent_IP",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Parent_RequestID",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Parent_RequestEndTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Parent_TurnAroundTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Parent_DNSLookupTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Parent_GeoInfo",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Parent_ASN",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "CloudWrapper_IP",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "CloudWrapper_RequestID",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "CloudWrapper_RequestEndTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "CloudWrapper_TurnAroundTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "CloudWrapper_DNSLookupTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "CloudWrapper_GeoInfo",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "CloudWrapper_ASN",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Peer_IP",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Peer_RequestID",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Peer_RequestEndTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Peer_TurnAroundTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Peer_DNSLookupTime",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Peer_GeoInfo",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "Peer_ASN",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "latency",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
            {
        "name": "cmcd_buffer_length",
        "datatype": {
          "type": "uint64",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_encoded_bitrate",
        "datatype": {
          "type": "uint64",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_buffer_starvation",
        "datatype": {
          "type": "uint8",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_content_id",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_object_duration",
        "datatype": {
          "type": "uint64",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_deadline",
        "datatype": {
          "type": "uint64",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_measured_throughput",
        "datatype": {
          "type": "uint64",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_next_object_requests",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_next_range_request",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_object_type",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_playback_rate",
        "datatype": {
          "type": "uint8",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_requested_max_throughput",
        "datatype": {
          "type": "uint64",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_streaming_format",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_session_id",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_stream_type",
        "datatype": {
          "type": "string",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_startup",
        "datatype": {
          "type": "uint8",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_top_bitrate",
        "datatype": {
          "type": "uint64",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      },
      {
        "name": "cmcd_version",
        "datatype": {
          "type": "uint32",
          "source": {
            "from_input_field": "sql_transform"
          }
        }
      }
        ],
        "compression": "gzip",
        "format_details": {
            "flattening": {
                "active": false
            }
        }
    }
}

Our transform now contain a SQL query which extracts breadcrumbs, CMCD information and index all the information.

Here’s an example of data generated by Hydrolix from Akamai logs:

CloudWrapper_ASN:              ᴺᵁᴸᴸ
CloudWrapper_DNSLookupTime:    ᴺᵁᴸᴸ
CloudWrapper_GeoInfo:
CloudWrapper_IP:
CloudWrapper_RequestEndTime:   ᴺᵁᴸᴸ
CloudWrapper_RequestID:
CloudWrapper_TurnAroundTime:   ᴺᵁᴸᴸ
Edge_ASN:                      ᴺᵁᴸᴸ
Edge_DNSLookupTime:            0
Edge_GeoInfo:
Edge_IP:                       23.75.216.XXX
Edge_RequestEndTime:           1
Edge_RequestID:
Edge_TurnAroundTime:           5
Origin_ASN:                    ᴺᵁᴸᴸ
Origin_DNSLookupTime:          ᴺᵁᴸᴸ
Origin_GeoInfo:
Origin_IP:
Origin_RequestEndTime:         ᴺᵁᴸᴸ
Origin_RequestID:
Origin_TurnAroundTime:         ᴺᵁᴸᴸ
Parent_ASN:                    ᴺᵁᴸᴸ
Parent_DNSLookupTime:          ᴺᵁᴸᴸ
Parent_GeoInfo:
Parent_IP:
Parent_RequestEndTime:         ᴺᵁᴸᴸ
Parent_RequestID:
Parent_TurnAroundTime:         ᴺᵁᴸᴸ
Peer_ASN:                      ᴺᵁᴸᴸ
Peer_DNSLookupTime:            ᴺᵁᴸᴸ
Peer_GeoInfo:
Peer_IP:                       23.75.216.XXX
Peer_RequestEndTime:           1
Peer_RequestID:
Peer_TurnAroundTime:           0
UA:                            Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.5005.61 Safari/537.36
accLang:                       ^
breadcrumbs:                   [a=23.75.216.XXX,c=g,k=1,l=5,j=[[a=10.75.216.XXX,c=p,k=0,l=4,m=0]]],[a=23.75.216.XXX,c=p,k=1,l=0]
bytes:                         109416
cacheStatus:                   0
city:                          PARIS
cliIP:                         86.242.XX.YY
cmcd_buffer_length:            53400
cmcd_buffer_starvation:        ᴺᵁᴸᴸ
cmcd_content_id:               123-456
cmcd_deadline:                 ᴺᵁᴸᴸ
cmcd_encoded_bitrate:          522
cmcd_measured_throughput:      214700
cmcd_next_object_requests:     ᴺᵁᴸᴸ
cmcd_next_range_request:       ᴺᵁᴸᴸ
cmcd_object_duration:          6016
cmcd_object_type:              audio_only
cmcd_playback_rate:            ᴺᵁᴸᴸ
cmcd_requested_max_throughput: ᴺᵁᴸᴸ
cmcd_session_id:               b248658d-1d1a-4039-91d0-8c08ba597da5
cmcd_startup:                  ᴺᵁᴸᴸ
cmcd_stream_type:              ᴺᵁᴸᴸ
cmcd_streaming_format:         hls
cmcd_top_bitrate:              ᴺᵁᴸᴸ
cmcd_version:                  ᴺᵁᴸᴸ
cookie:                        ^
country:                       FR
cp:                            385647
customField:                   -
dnsLookupTimeMSec:             0
errorCode:                     -
latency:                       153
maxAgeSec:                     31536000
objSize:                       109416
overheadBytes:                 0
proto:                         HTTP/3
queryStr:                      CMCD=bl=53400,br=522,cid="123-456",d=6016,mtp=214700,ot=a,sf=h,sid="b248658d-1d1a-4039-91d0-8c08ba597da5"
range:                         -
referer:                       https://mdtp-a.akamaihd.net/
reqEndTimeMSec:                1
reqHost:                       host.akamaized.net
reqId:                         15959e0b
reqMethod:                     GET
reqPath:                       ans/ape/sc-gaFEAQ/a0_F0009.ts
reqPort:                       443
reqTimeSec:                    2022-06-08 15:39:43
rspContentLen:                 109416
rspContentType:                video/MP2T
state:                         Ile-de-France
statusCode:                    200
tlsOverheadTimeMSec:           0
tlsVersion:                    QUIC
totalBytes:                    114873
transferTimeMSec:              12
turnAroundTimeMSec:            5
uncompressedSize:              0
version:                       3
xForwardedFor:                 -

As you can see we have a very full pictures of the streaming session for this users, from a CDN point of view and also from a content QoS perspective!

Share Now