Hi, here is the code with the server names changed to *********. This is exported as a PowerBI M Query and pastes into Advanced Editor of PowerBI. As above it runs fine but returns zero rows in the table.
let lastmonthNumber = getmonth(datetime(now)) - 1;
let lastmonth = iff(lastmonthNumber == 0, 12, lastmonthNumber);
let year = getyear(datetime(now)) - iff(lastmonth == 12, 1, 0);
let dateStart = make_datetime(year, lastmonth, 01);
let OperationIdInTraces = dynamic(['createSwitch']);
let traces =
app('***************').traces
| union app('***************').traces
| union app('***************').exceptions
| union app('***************').exceptions
| where timestamp > dateStart
| project timestamp, customDimensions, operation_Name, message
| extend timestamp_date = format_datetime(timestamp,"yyyy-MM-dd"),
CorrelationId = coalesce(tostring(customDimensions.prop__CorrelationId), tostring(customDimensions.CorrelationId)),
OperationId = coalesce(tostring(customDimensions.prop__OperationId),tostring(customDimensions.prop__operationId),tostring(customDimensions.OperationId)),
Source = coalesce(operation_Name,customDimensions.AzureFunctions_FunctionName ),
MessageStage = coalesce(customDimensions.prop__MessageStage, customDimensions.MessageStage),
Message = coalesce(message,customDimensions.prop__Message, customDimensions.Message),
Year = tostring(datetime_part("year", timestamp)),
Month = datetime_part("month", timestamp),
Day = datetime_part("day", timestamp),
HourOfDay = hourofday(timestamp),
MinuteOfDay = datetime_part("minute", timestamp),
qryAdditionalInfo = coalesce(customDimensions.prop__AdditionalInfo,customDimensions.AdditionalInfo)
| extend AdditionalInfoJson = parse_json(tostring(qryAdditionalInfo))
| extend Mpid = AdditionalInfoJson.mpid,
LogTimeStampMessage = iff(Message contains ",", split(Message, ",")[-1], "")
| extend LogTimestamp = coalesce(todatetime(customDimensions.prop__LogTimestamp), todatetime(customDimensions.LogTimestamp), todatetime(LogTimeStampMessage)),
EventType = AdditionalInfoJson.eventType,
EventDate = todatetime(AdditionalInfoJson.eventDate),
PayloadJson = parse_json(tostring(AdditionalInfoJson.payload))
| extend EventStatus = PayloadJson.eventStatus
;
let distinct_correlationid =
traces
| where OperationId == "createSwitch"
and Source == "ProcessScheduledWork"
and MessageStage == "OperationGetOperationData"
and Message startswith ("Requeuing duplicate request")
| distinct CorrelationId
;
let hours =
traces
| where Source == "VerifyInboundRequest"
| where isempty(['OperationIdInTraces']) or OperationId in (['OperationIdInTraces'])
| where Mpid != ""
| summarize make_set(tostring(CorrelationId)) by bin(timestamp, 1h)
| project
Year = tostring(datetime_part("year", timestamp))
,Month = datetime_part("month", timestamp)
, Day = datetime_part("day", timestamp)
, HourOfDay = datetime_part("hour", timestamp)
, CountTraces = array_length(set_CorrelationId)
, HourType = case(array_length(set_CorrelationId) <= 3500 , "Average",
array_length(set_CorrelationId) > 3500 and array_length(set_CorrelationId) <= 25300, "Peak",
"NoSLA")
| order by Year asc, Month asc, Day asc, HourOfDay asc
;
let count_end =
traces
| where CorrelationId !in (distinct_correlationid)
and Source == "ProcessOutboundQueue-10"
and MessageStage == "MessageDelivery"
and EventType == "RegistrationValidationNotification"
and isempty(['OperationIdInTraces']) or OperationId in (['OperationIdInTraces'])
and EventStatus == "Ok"
and Mpid != ""
| project timestamp_date,
EventDate,LogTimestamp, CorrelationId,Mpid ,Year, Month, Day,HourOfDay, MinuteOfDay
| where isnotempty(timestamp_date) and trim(" ", timestamp_date) != "";
let count_start =
traces
| where Source == "ProcessInboundRequest"
and MessageStage == "SchemaValidation"
and (isempty(['OperationIdInTraces']) or OperationId in (['OperationIdInTraces']))
| project LogTimestampSchema = todatetime(tostring(LogTimestamp)) , CorrelationId;
hours
| join kind=leftouter count_end on Month, Day, HourOfDay
| join kind=leftouter count_start on CorrelationId
| extend ThirdPartyResponseTimeb = datetime_diff('millisecond',LogTimestamp,EventDate),
ResponseTimeInc3b = datetime_diff('millisecond',LogTimestamp,todatetime(LogTimestampSchema))
| extend TrueResponseTime = ResponseTimeInc3b - ThirdPartyResponseTimeb
| summarize AvgExecutionTimeMs = round(avg(TrueResponseTime),2), TotalExecutionTimeMs = round(sum(TrueResponseTime),2)
, MinExecutionTimeMs = round(min(TrueResponseTime),2), MaxExecutionTimeMs = round(max(TrueResponseTime),2)
, VarExecutionTime = round(variance(TrueResponseTime),2), StdDevExecutionTime = round(stdev(TrueResponseTime),2)
,CountTraces = count(), Percentile90Response = round(percentile(TrueResponseTime,90),2)
by Year, Month, Day,HourOfDay, HourType,timestamp_date
| extend SLA = case(case(HourType == "Average" , 3000 - tolong(AvgExecutionTimeMs),
HourType == "Peak" , 5000 - tolong(AvgExecutionTimeMs), 1)
>= 0 , "Pass","Fail")
| extend SLA_90 = case(case(HourType == "Average" , 6000 - tolong(Percentile90Response),
HourType == "Peak" , 8000 - tolong(Percentile90Response), 1)
>= 0 , "Pass","Fail")
| where isnotempty(timestamp_date) and timestamp_date != ""
| project timestamp_date,HourOfDay,CountTraces,HourType,avg_TimeMs = AvgExecutionTimeMs,Percentile90Response,
SLA,SLA_90,TotalExecutionTimeMs, MinExecutionTimeMs, MaxExecutionTimeMs
