Bug: Unique users within retention table

[daltskin]

Unique users query doesn't look right - as it's also counting messages sent from the bot eg:

customEvents 
| where timestamp > ago(30d) 
| where name=='Activity' 
| extend uniqueUser=tostring(customDimensions.from)
| summarize oldestVisit=min(timestamp),
  lastVisit=max(timestamp) by uniqueUser 
| summarize
totalUnique = dcount(uniqueUser),
  totalUniqueUsersIn24hr = countif(lastVisit > ago(24hr)),
  totalUniqueUsersIn7d = countif(lastVisit > ago(7d)),
  totalUniqueUsersIn30d = countif(lastVisit > ago(30d)),
  returning24hr = countif(lastVisit > ago(24hr)
  and oldestVisit <= ago(24hr)),
  returning7d = countif(lastVisit > ago(7d)
  and oldestVisit <= ago(7d)),
  returning30d = countif(lastVisit > ago(30d)
  and oldestVisit <= ago(30d))

You can use the 'tobot' field to filter these out eg:

customEvents 
| where timestamp > ago(30d) 
| where name=='Activity' 
| extend user=tostring(customDimensions.from),
tobot=iif(strlen(customDimensions.tobot) == 0, false, tobool(tolower(customDimensions.tobot))),
recipient=tostring(customDimensions.recipient),
channel=tostring(customDimensions.channel),
from=tostring(customDimensions.from)
|project user, tobot, recipient, from, channel 
| where tobot == true

Found in this dashboard
https://github.com/CatalystCode/ibex-dashboard/blob/master/server/dashboards/preconfigured/bot-framework.ts

[DavidCampaneroGarcia]

I'm having problems showing the Users in this version:

https://github.com/Azure/ibex-dashboard/blob/master/server/dashboards/preconfigured/bot-framework-inst.ts

It seems there is a problem between the timespan datasource and the retention query.

[morsh]

@DavidCampaneroGarcia what's the problem?

[DavidCampaneroGarcia]

@morsh I'm trying to mix 2 dashboards.

First one: Have granurality implemented that works awesome but doesn't count the users:

https://i.imgur.com/h63W763.png

Second One: Users count works but doesn't have granurality:

https://i.imgur.com/T42aG4T.png

I would like to mix them both and have granurality and user count, but it seems that with this code:

	{
	id: "timespan",
	type: "Constant",
	params: { selectedValue: "2018-07-03T00:00:00.000Z/2018-07-03T23:59:59.999Z" },
	calculated: (state, dependencies) => {
        var queryTimespan =  state.selectedValue;
        var dateRangeArray = state.selectedValue.split('/');
        
        var initDate = new Date(dateRangeArray[0]);
        var endDate = new Date(dateRangeArray[1]);
        var diffTime = endDate.getTime() - initDate.getTime();
        var diffDays = diffTime / (3600 * 1000 * 24);
  
        var timeFormat = (diffDays > 3) ? 'date' : 'hour' ;
        
        return {
          queryTimespan: state.selectedValue,
          startTimespan: dateRangeArray[0],
          endTimespan: dateRangeArray[1],
          'timeline-timeFormat': timeFormat,
        };
      }
		},

It comes from here:
https://github.com/Azure/ibex-dashboard/blob/master/server/dashboards/preconfigured/bot-framework-inst.ts

And in the same dashboard it has this query:

{
      id: 'retention',
      type: 'ApplicationInsights/Query',
      dependencies: { timespan: 'timespan', selectedTimespan: 'timespan:queryTimespan', queryTimespan: '::P90D' },
      params: {
        query: () => `
          customEvents 
          | where name=='MBFEvent.UserMessage' 
          | extend uniqueUser=tostring(customDimensions.userId) 
          | summarize oldestVisit=min(timestamp), lastVisit=max(timestamp) by uniqueUser 
          | summarize
            totalUnique = dcount(uniqueUser),
            totalUniqueUsersIn24hr = countif(lastVisit > ago(24hr)),
            totalUniqueUsersIn7d = countif(lastVisit > ago(7d)),
            totalUniqueUsersIn30d = countif(lastVisit > ago(30d)),
            returning24hr = countif(lastVisit > ago(24hr) and oldestVisit <= ago(24hr)),
            returning7d = countif(lastVisit > ago(7d) and oldestVisit <= ago(7d)),
            returning30d = countif(lastVisit > ago(30d) and oldestVisit <= ago(30d))`
      },
      format: 'retention'
    }

It seems it doesn't work well between them and when you click it on it you can see the table of users, but doesn't load the content.

https://i.imgur.com/I0J3yJ5.png

Also I would like to know if there is a way to filter only conversations that has more than 1 message from the user. I don't know if I should post this issue in other thread.

[morsh]

@DavidCampaneroGarcia - I've marked this issue as a bug, it is indeed an issue with the bot-framework-inst dashboard. I think it's a matter of simply pointing the scorecard of total users to the value of totalUnique.
If you're able to fix this issue, I'd be happy to get a PR on this.

Regarding conversations with more than 1 message I'm sure this can be done and I'm guessing this can be done with a summarize, count and where,