Read JSON output from AppInsights in C#

Question

I want to read AppInsights API output in C# console application.

WebClient wc = new WebClient();
wc.BaseAddress = "https://api.applicationinsights.io/v1/apps/AppInsighID/query?query=requests|where timestamp>= ago(1h)|limit 100";
wc.Headers.Add("Host", "api.applicationinsights.io");
wc.Headers.Add("x-api-key", "key");

string json = wc.DownloadString("");

JObject jsonObject = JObject.Parse(json);

//With this, i got values for Rows

var rowsObject = jsonObject["tables"][0]["rows"];

Now values are in array, under rowObject, so how to read this?

I would also like to know the best practice we should follow when reading json string

I can see data like this

{
"tables": [
    {
        "name": "PrimaryResult",
        "columns": [
            {
                "name": "timestamp",
                "type": "datetime"
            },
            {
                "name": "id",
                "type": "string"
            },
            {
                "name": "source",
                "type": "string"
            },
            {
                "name": "name",
                "type": "string"
            },
            {
                "name": "url",
                "type": "string"
            },
            {
                "name": "success",
                "type": "string"
            },
            {
                "name": "resultCode",
                "type": "string"
            },
            {
                "name": "duration",
                "type": "real"
            },
            {
                "name": "performanceBucket",
                "type": "string"
            },
            {
                "name": "itemType",
                "type": "string"
            },
            {
                "name": "customDimensions",
                "type": "dynamic"
            },
            {
                "name": "customMeasurements",
                "type": "dynamic"
            },
            {
                "name": "operation_Name",
                "type": "string"
            },
            {
                "name": "operation_Id",
                "type": "string"
            },
            {
                "name": "operation_ParentId",
                "type": "string"
            },
            {
                "name": "operation_SyntheticSource",
                "type": "string"
            },
            {
                "name": "session_Id",
                "type": "string"
            },
            {
                "name": "user_Id",
                "type": "string"
            },
            {
                "name": "user_AuthenticatedId",
                "type": "string"
            },
            {
                "name": "user_AccountId",
                "type": "string"
            },
            {
                "name": "application_Version",
                "type": "string"
            },
            {
                "name": "client_Type",
                "type": "string"
            },
            {
                "name": "client_Model",
                "type": "string"
            },
            {
                "name": "client_OS",
                "type": "string"
            },
            {
                "name": "client_IP",
                "type": "string"
            },
            {
                "name": "client_City",
                "type": "string"
            },
            {
                "name": "client_StateOrProvince",
                "type": "string"
            },
            {
                "name": "client_CountryOrRegion",
                "type": "string"
            },
            {
                "name": "client_Browser",
                "type": "string"
            },
            {
                "name": "cloud_RoleName",
                "type": "string"
            },
            {
                "name": "cloud_RoleInstance",
                "type": "string"
            },
            {
                "name": "appId",
                "type": "string"
            },
            {
                "name": "appName",
                "type": "string"
            },
            {
                "name": "iKey",
                "type": "string"
            },
            {
                "name": "sdkVersion",
                "type": "string"
            },
            {
                "name": "itemId",
                "type": "string"
            },
            {
                "name": "itemCount",
                "type": "int"
            }
        ],
        "rows": [
            [
                "2020-01-16T07:07:35.8423912Z",
                "ID",
                "",
                "POST ",
                "https://",
                "True",
                "200",
                57.679,
                "<250ms",
                "request",
                "{\"Product Name\":\"Name\",\"Subscription Name\":\"Name\",\"Operation Name\":\"AdvancedSearch\",\"ApimanagementRegion\":\"Region\",\"ApimanagementServiceName\":\"Name\",\"Apim Request Id\":\"ID\",\"Request-Body\":\"{\\\"P1\\\":25,\\\"P2\\\":1,\\\"P3\\\":\\\"All \\\",\\\"P4\\\":\\\"Earliest\\\",\\\"P5\\\":\\\"Extended\\\",\\\"P6\\\":\\\"All \\\",\\\"P6\\\":\\\"Latest\\\",\\\"queryList\\\":[{\\\"P7\\\":\\\"physics\\\",\\\"P8\\\":\\\"A1\\\",\\\"operator\\\":\\\"\\\"}]}\",\"Cache\":\"None\",\"P9\":\"195.43.22.145\",\"API Name\":\"Name\",\"HTTP Method\":\"POST\"}",
                "{\"Response Size\":776,\"Request Size\":1092,\"Client Time (in ms)\":0}",
                "POST ",
                "ID",
                "ID",
                "",
                "",
                "",
                "1",
                "",
                "",
                "PC",
                "",
                "",
                "0.0.0.0",
                "Milan",
                "Milan",
                "Italy",
                "",
                "Value1",
                "Value2",
                "ID1",
                "AppInsight Name",
                "Name",
                "apim:0.12.885.0",
                "ID",
                1
            ]
        ]
    }
]
}

Answer 1

You could deserialize the Json and fetch the Rows information. For example,

var result = JsonConvert.DeserializeObject<RootObject>(str);
var rowData = result.tables.SelectMany(x=>x.rows.SelectMany(c=>c));

If you do not want to Flatten the results, you could use

var rowData = result.tables.SelectMany(x=>x.rows.Select(c=>c));

Where RootObject is defined as

public class Column
{
    public string name { get; set; }
    public string type { get; set; }
}

public class Table
{
    public string name { get; set; }
    public List<Column> columns { get; set; }
    public List<List<string>> rows { get; set; }
}

public class RootObject
{
    public List<Table> tables { get; set; }
}

If you intend to eliminate empty values, you could filter them out using Linq

var rowData = result.tables.SelectMany(x=>x.rows.SelectMany(c=>c))
                           .Where(x=>!string.IsNullOrEmpty(x));

Or for non-Flatten results

var rowData = result.tables.SelectMany(x=>x.rows.Select(c=>c))
                           .Where(x=>!string.IsNullOrEmpty(x.ToString()));

Update

Based on comment, to retrieve the information and parse it to a Dto based on the position of values in array, you could do the following. The attribute could be used for handling inline Json Properties as well, as mentioned in the comment.

You could begin by defining an attribute as following.

public class DtoDefinitionAttribute:Attribute
{

    public DtoDefinitionAttribute(int order)=>Order = order;
    public DtoDefinitionAttribute(int order,bool isJson,Type jsonDataType)
    {
        Order = order;
        JsonDataType = jsonDataType;
        IsJson = isJson;
    }
    public bool IsJson{get;} = false;
    public int Order{get;}
    public Type JsonDataType {get;}
}

And, then you could decorate your Dto properties with the index of corresponding value in the array. Additionally, in case of Json string expected as Json, then you could use the attribute to indicate it as well, as shown in the ClientTime property For example,

public class Dto
{
    [DtoDefinition(0)]
    public DateTime CurrentDate{get;set;}
    [DtoDefinition(1)]
    public string ID{get;set;}
    [DtoDefinition(2)]
    public string Url{get;set;}
    [DtoDefinition(11,true,typeof(Response))]
    public Response Json1{get;set;}
}

public class Response
{
    [JsonProperty("Response Size")]
    public string ResponseSize{get;set;}

    [JsonProperty("Request Size")]
    public string RequestSize{get;set;}

    [JsonProperty("Client Time (in ms)")]
    public int ClientTime{get;set;}
}

Now you could use the rowData result obtained using

var listDto = new List<Dto>();
foreach(var row in rowData)
{
    listDto.Add(AssignValues(row));
}

Where AssignValues are defined as

public Dto AssignValues(List<string> row)
{
    var dto = new Dto();
    var properties = typeof(Dto).GetProperties().Where(x=>x.GetCustomAttributes<DtoDefinitionAttribute>().Any());
    foreach(var property in properties)
    {
        var attribute = property.GetCustomAttribute<DtoDefinitionAttribute>();
        if(attribute.IsJson)
        {
            var jsonData = row[attribute.Order].ToString();
            var deserializedData = JsonConvert.DeserializeObject(jsonData,attribute.JsonDataType);
            property.SetValue(dto,deserializedData);
        }
        else
        {
            property.SetValue(dto,Convert.ChangeType(row[attribute.Order],property.PropertyType));
        }
    }
    return dto;
}

The AssignValues method uses reflection to read the Attributes and create an instance of Dto based on it. In case, it finds the attribute defines it as Json, then it would deserialize the json value and use the result.

Demo Code