CODEWITHSUNDEEP
azureazure-application-insightskqlappinsights
Sammi
Sammi

Reputation: 11

Application Insights usage reports summarising by "OTHER"

Hi I am new to using App Insights. I need help identifying why my query is summarising some users into "OTHER" (upper case)

It looks like there is a limit to the number of items that can be rendered on a chart (column or bar chart)

I have some custom events logged that record when the user performed various functions in our browser based app, recording the user name, their role and the function performed as well as timestamp

I have this query to see when my users log in, lumped into hourly bins.

When I run it for 2 days it works fine when I run it for 7 days it lumps some users together under "OTHER" (all UPPER case)

If I render to a table no "OTHER" shows when I render to columnchart or barchart "OTHER" appears

Yes I can run it for shorter periods, or drop the breakdown by user but is there a way to up the limit?

let mainTable = union customEvents
    | where datetime_utc_to_local(timestamp,'Pacific/Auckland') > startofday(ago(7d)) // from beginning of yesterday
    | where datetime_utc_to_local(timestamp,'Pacific/Auckland') < startofday(now())  // to beginning of today
    | where isempty(operation_SyntheticSource)   //not Synthetic users
    | where itemType == "customEvent"  //only custom data
    | where customDimensions["FunctionName"]=="Log in"  //only logins
    | extend FunctionName = customDimensions["FunctionName"];
let queryTable = mainTable;
let splitTable =  () {   //splitting table by... (user)
    queryTable
    | extend user_dim = customDimensions["User"]  //get username as dimension
    | extend user_dim = iif(isempty(user_dim), "<undefined>", user_dim) //show "undefined" if user is blank
};
let cohortedTable = splitTable
    | extend byDimension = bin(datetime_utc_to_local(timestamp,'Pacific/Auckland'), 1h)  // 1h group by hourly buckets 30m =30 min 
    | summarize metric = count_distinct(user_Id) by byDimension, user_dim;
cohortedTable
| project byDimension, user_dim, metric //show these in the output
| summarize metric = sum(metric) by user_dim, byDimension
| render columnchart
//| render table
//| render barchart

Upvotes: 0

Views: 57

Answers (1)

Jahnavi
Jahnavi

Reputation: 7898

After exploring on your issue, I found that there is a limitation of resultant logs in chart visualization from application insights rather than using a rows table representation.

It usually maintains records visualization up to 12 distinct categories by default, depending on the visualization type. If exceeding this limit is grouped into "OTHER" category.

If you use rows (table) representation, it can record as many as logs of results existed.

And you cannot directly increase the limitation of charts for complete visualization.

Alternatively, as a workaround you can try filtering out the user's information logs in a brief manner.

You can use a top operator available in KQL to limit the number of rows by applying a filter returned from the query as shown below.

I have executed a sample query to show you how it works as I do not have custom events traced in the recent log timestamp.

requests
| top 10 by appId

enter image description here

Snippet of the query after changes:

let cohortedTable = splitTable()
    | extend byDimension = bin(datetime_utc_to_local(timestamp, 'Pacific/Auckland'), 1h)
    | summarize metric = count_distinct(user_Id) by byDimension, user_dim
    | top 10 by metric; //Modify it according to your requirement
| project byDimension, user_dim, metric
| summarize metric = sum(metric) by user_dim, byDimension
| render columnchart

Or else you could aggregate the log information by reducing the time duration (Eg: 24hr) instead of showing it for long duration. And also, you can group users into roles or other categories for ease of analysis.

Upvotes: 0

Related Questions