Exemplos de consultas Kusto

Este artigo identifica consultas comuns e como você pode usar a Linguagem de Consulta Kusto para atendê-las.

Exibir um gráfico de colunas

Para projetar duas ou mais colunas e usá-las como os eixos x e y de um gráfico:

StormEvents
| where isnotempty(EndLocation) 
| summarize event_count=count() by EndLocation
| top 10 by event_count
| render columnchart
  • A primeira coluna deve formar o eixo x. Ela pode ser numérica, de data/hora ou de cadeia de caracteres.
  • Use os operadores where, summarize e top para limitar o volume de dados exibidos.
  • Classifique os resultados para definir a ordem do eixo x.

Uma captura de tela de um gráfico de colunas com dez colunas coloridas que representam os respectivos valores de 10 locais.

Obter sessões de eventos start e stop

Em um log de eventos, alguns eventos marcam o início/término de uma atividade ou sessão estendida.

Nome City SessionId Timestamp
Iniciar London 2817330 2015-12-09T10:12:02.32
Game London 2817330 2015-12-09T10:12:52.45
Iniciar Manchester 4267667 2015-12-09T10:14:02.23
Parar London 2817330 2015-12-09T10:23:43.18
Cancelar Manchester 4267667 2015-12-09T10:27:26.29
Parar Manchester 4267667 2015-12-09T10:28:31.72

Cada evento tem uma ID da sessão (SessionId). O desafio é obter uma correspondência entre eventos de início e interrupção e uma ID da sessão.

Exemplo:

let Events = MyLogTable | where ... ;
Events
| where Name == "Start"
| project Name, City, SessionId, StartTime=timestamp
| join (Events 
        | where Name="Stop"
        | project StopTime=timestamp, SessionId) 
    on SessionId
| project City, SessionId, StartTime, StopTime, Duration = StopTime - StartTime

Para obter uma correspondência entre eventos de início e interrupção e uma ID da sessão:

  1. Use o operador let para nomear uma projeção da tabela que foi reduzida ao máximo antes de iniciar a junção.
  2. Use o operador project para alterar os nomes dos carimbos de data/hora, de modo que os horários de início e interrupção apareçam nos resultados. O operador project também seleciona outras colunas a serem exibidas nos resultados.
  3. Use o operador join a fim de obter uma correspondência entre entradas de início e interrupção para a mesma atividade. Uma linha será criada para cada atividade.
  4. Use o operador project novamente para adicionar uma coluna a fim de mostrar a duração da atividade.

Esta é a saída:

City SessionId StartTime StopTime Duração
London 2817330 2015-12-09T10:12:02.32 2015-12-09T10:23:43.18 00:11:40.46
Manchester 4267667 2015-12-09T10:14:02.23 2015-12-09T10:28:31.72 00:14:29.49

Obter sessões sem usar uma ID da sessão

Suponha que os eventos de início e interrupção, de modo conveniente, não tenham uma ID da sessão com a qual seja possível obter uma correspondência. No entanto, temos o endereço IP do cliente em que a sessão ocorreu. Vamos supor que cada endereço do cliente conduza a somente uma sessão por vez. Desse modo, poderemos obter uma correspondência entre cada evento de início e o próximo evento de interrupção do mesmo endereço IP:

Exemplo:

Events 
| where Name == "Start" 
| project City, ClientIp, StartTime = timestamp
| join  kind=inner
    (Events
    | where Name == "Stop" 
    | project StopTime = timestamp, ClientIp)
    on ClientIp
| extend duration = StopTime - StartTime 
    // Remove matches with earlier stops:
| where  duration > 0  
    // Pick out the earliest stop for each start and client:
| summarize arg_min(duration, *) by bin(StartTime,1s), ClientIp

O operador join corresponde a cada hora de início com todos os horários de interrupção do mesmo endereço IP do cliente. O código de exemplo:

  • Remove correspondências com horários de interrupção anteriores.
  • Agrupa por hora de início e endereço IP a fim de obter um grupo para cada sessão.
  • Fornece uma função bin para o parâmetro StartTime. Caso não conclua essa etapa, o Azure Data Explorer usará de modo automático compartimentos de uma hora que obterão uma correspondência entre alguns horários de início e horários de interrupção incorretos.

O operador arg_min localiza a linha com a menor duração em cada grupo e o parâmetro * passa por todas as outras colunas.

O argumento prefixa min_ para cada nome da coluna.

Uma captura de tela de uma tabela que lista resultados usando colunas de hora de início, o IP do cliente, a duração, a cidade e a primeira interrupção de cada combinação de cliente/hora de início.

Adicione códigos para contar durações em compartimentos dimensionados de modo conveniente. Neste exemplo, devido à preferência por um gráfico de barras, divida por 1s para converter intervalos de tempo em números:

    // Count the frequency of each duration:
    | summarize count() by duration=bin(min_duration/1s, 10) 
      // Cut off the long tail:
    | where duration < 300
      // Display in a bar chart:
    | sort by duration asc | render barchart 

Uma captura de tela de um gráfico de colunas que representa o número de sessões com durações em intervalos especificados.

Exemplo completo

Logs  
| filter ActivityId == "ActivityId with Blablabla" 
| summarize max(Timestamp), min(Timestamp)  
| extend Duration = max_Timestamp - min_Timestamp 

wabitrace  
| filter Timestamp >= datetime(2015-01-12 11:00:00Z)  
| filter Timestamp < datetime(2015-01-12 13:00:00Z)  
| filter EventText like "NotifyHadoopApplicationJobPerformanceCounters"  	 
| extend Tenant = extract("tenantName=([^,]+),", 1, EventText) 
| extend Environment = extract("environmentName=([^,]+),", 1, EventText)  
| extend UnitOfWorkId = extract("unitOfWorkId=([^,]+),", 1, EventText)  
| extend TotalLaunchedMaps = extract("totalLaunchedMaps=([^,]+),", 1, EventText, typeof(real))  
| extend MapsSeconds = extract("mapsMilliseconds=([^,]+),", 1, EventText, typeof(real)) / 1000 
| extend TotalMapsSeconds = MapsSeconds  / TotalLaunchedMaps 
| filter Tenant == 'DevDiv' and Environment == 'RollupDev2'  
| filter TotalLaunchedMaps > 0 
| summarize sum(TotalMapsSeconds) by UnitOfWorkId  
| extend JobMapsSeconds = sum_TotalMapsSeconds * 1 
| project UnitOfWorkId, JobMapsSeconds 
| join ( 
wabitrace  
| filter Timestamp >= datetime(2015-01-12 11:00:00Z)  
| filter Timestamp < datetime(2015-01-12 13:00:00Z)  
| filter EventText like "NotifyHadoopApplicationJobPerformanceCounters"  
| extend Tenant = extract("tenantName=([^,]+),", 1, EventText) 
| extend Environment = extract("environmentName=([^,]+),", 1, EventText)  
| extend UnitOfWorkId = extract("unitOfWorkId=([^,]+),", 1, EventText)   
| extend TotalLaunchedReducers = extract("totalLaunchedReducers=([^,]+),", 1, EventText, typeof(real)) 
| extend ReducesSeconds = extract("reducesMilliseconds=([^,]+)", 1, EventText, typeof(real)) / 1000 
| extend TotalReducesSeconds = ReducesSeconds / TotalLaunchedReducers 
| filter Tenant == 'DevDiv' and Environment == 'RollupDev2'  
| filter TotalLaunchedReducers > 0 
| summarize sum(TotalReducesSeconds) by UnitOfWorkId  
| extend JobReducesSeconds = sum_TotalReducesSeconds * 1 
| project UnitOfWorkId, JobReducesSeconds ) 
on UnitOfWorkId 
| join ( 
wabitrace  
| filter Timestamp >= datetime(2015-01-12 11:00:00Z)  
| filter Timestamp < datetime(2015-01-12 13:00:00Z)  
| filter EventText like "NotifyHadoopApplicationJobPerformanceCounters"  
| extend Tenant = extract("tenantName=([^,]+),", 1, EventText) 
| extend Environment = extract("environmentName=([^,]+),", 1, EventText)  
| extend JobName = extract("jobName=([^,]+),", 1, EventText)  
| extend StepName = extract("stepName=([^,]+),", 1, EventText)  
| extend UnitOfWorkId = extract("unitOfWorkId=([^,]+),", 1, EventText)  
| extend LaunchTime = extract("launchTime=([^,]+),", 1, EventText, typeof(datetime))  
| extend FinishTime = extract("finishTime=([^,]+),", 1, EventText, typeof(datetime)) 
| extend TotalLaunchedMaps = extract("totalLaunchedMaps=([^,]+),", 1, EventText, typeof(real))  
| extend TotalLaunchedReducers = extract("totalLaunchedReducers=([^,]+),", 1, EventText, typeof(real)) 
| extend MapsSeconds = extract("mapsMilliseconds=([^,]+),", 1, EventText, typeof(real)) / 1000 
| extend ReducesSeconds = extract("reducesMilliseconds=([^,]+)", 1, EventText, typeof(real)) / 1000 
| extend TotalMapsSeconds = MapsSeconds  / TotalLaunchedMaps  
| extend TotalReducesSeconds = (ReducesSeconds / TotalLaunchedReducers / ReducesSeconds) * ReducesSeconds  
| extend CalculatedDuration = (TotalMapsSeconds + TotalReducesSeconds) * time(1s) 
| filter Tenant == 'DevDiv' and Environment == 'RollupDev2') 
on UnitOfWorkId 
| extend MapsFactor = TotalMapsSeconds / JobMapsSeconds 
| extend ReducesFactor = TotalReducesSeconds / JobReducesSeconds 
| extend CurrentLoad = 1536 + (768 * TotalLaunchedMaps) + (1536 * TotalLaunchedMaps) 
| extend NormalizedLoad = 1536 + (768 * TotalLaunchedMaps * MapsFactor) + (1536 * TotalLaunchedMaps * ReducesFactor) 
| summarize sum(CurrentLoad), sum(NormalizedLoad) by  JobName  
| extend SaveFactor = sum_NormalizedLoad / sum_CurrentLoad 

Sessões simultâneas do gráfico ao longo do tempo

Vamos supor que você tenha uma tabela de atividades com horários de início e término. É possível mostrar um gráfico que exiba quantas execuções de atividades ocorrem de modo simultâneo ao longo do tempo.

Veja uma entrada de exemplo chamada X:

SessionId StartTime StopTime
um 10:01:03 10:10:08
b 10:01:29 10:03:10
c 10:03:02 10:05:20

Você deseja contar cada atividade em execução em intervalos de um minuto para obter um gráfico de compartimentos de um minuto.

Veja um resultado intermediário:

X | extend samples = range(bin(StartTime, 1m), StopTime, 1m)

range gera uma matriz de valores nos intervalos especificados:

SessionId StartTime StopTime amostras
um 10:01:33 10:06:31 [10:01:00,10:02:00,...10:06:00]
b 10:02:29 10:03:45 [10:02:00,10:03:00]
c 10:03:12 10:04:30 [10:03:00,10:04:00]

Expanda as matrizes usando mv-expand em vez de mantê-las:

X | mv-expand samples = range(bin(StartTime, 1m), StopTime , 1m)
SessionId StartTime StopTime amostras
um 10:01:33 10:06:31 10:01:00
um 10:01:33 10:06:31 10:02:00
um 10:01:33 10:06:31 10:03:00
um 10:01:33 10:06:31 10:04:00
um 10:01:33 10:06:31 10:05:00
um 10:01:33 10:06:31 10:06:00
b 10:02:29 10:03:45 10:02:00
b 10:02:29 10:03:45 10:03:00
c 10:03:12 10:04:30 10:03:00
c 10:03:12 10:04:30 10:04:00

Agora, agrupe os resultados por tempo de exemplo e conte as ocorrências de cada atividade:

X
| mv-expand samples = range(bin(StartTime, 1m), StopTime , 1m)
| summarize count_SessionId = count() by bin(todatetime(samples),1m)
  • Use todatetime() porque o todatetime() resulta em uma coluna de tipo dinâmico.
  • Use bin() porque caso não forneça um intervalo para valores numéricos e datas, o operador summarize sempre aplicará uma função bin() usando um intervalo padrão.

Esta é a saída:

count_SessionId amostras
1 10:01:00
2 10:02:00
3 10:03:00
2 10:04:00
1 10:05:00
1 10:06:00

É possível usar um gráfico de barras ou um gráfico de tempo para renderizar os resultados.

Introduzir compartimentos nulos no operador summarize

Quando o operador summarize for aplicado em uma chave de grupo que consiste em uma coluna de data e hora, use esses valores em compartimentos de largura fixa:

let StartTime=ago(12h);
let StopTime=now()
T
| where Timestamp > StartTime and Timestamp <= StopTime 
| where ...
| summarize Count=count() by bin(Timestamp, 5m)

Este exemplo produzirá uma tabela com uma linha única por grupo de linhas em T que se enquadrarão em cada compartimento de cinco minutos.

O código não adicionará "compartimentos nulos", ou seja, linhas para valores de compartimentos de tempo entre StartTime e StopTime para os quais não haja linhas correspondentes em T. Recomendamos "preencher" a tabela com esses compartimentos. Veja um modo de fazer isso:

let StartTime=ago(12h);
let StopTime=now()
T
| where Timestamp > StartTime and Timestamp <= StopTime 
| summarize Count=count() by bin(Timestamp, 5m)
| where ...
| union ( // 1
  range x from 1 to 1 step 1 // 2
  | mv-expand Timestamp=range(StartTime, StopTime, 5m) to typeof(datetime) // 3
  | extend Count=0 // 4
  )
| summarize Count=sum(Count) by bin(Timestamp, 5m) // 5 

Veja uma explicação passo a passo sobre a consulta anterior:

  1. Use o operador union para adicionar linhas a uma tabela. Essas linhas serão produzidas pela expressão union.
  2. O operador range produzirá uma tabela com somente uma linha e coluna. A tabela será usada somente para possibilitar o funcionamento de mv-expand.
  3. O operador mv-expand criará na função range o mesmo número de linhas e compartimentos de cinco minutos entre StartTime e EndTime.
  4. Use uma Count de 0.
  5. O operador summarize agrupará compartimentos do argumento original (esquerdo ou externo) para o operador union. O operador também obterá compartimentos de um argumento interno (linhas de compartimentos nulos). Esse processo garantirá que a saída tenha uma linha por compartimento cujo valor será zero ou a contagem original.

Aproveite mais os seus dados usando o Azure Data Explorer com o machine learning

Vários casos de uso interessantes usam algoritmos de machine learning e obtêm insights úteis de dados de telemetria. Esses algoritmos geralmente exigem um conjunto de dados estritamente estruturado como entrada. Os dados de log brutos normalmente não correspondem à estrutura e ao tamanho necessários.

Comece procurando por anomalias na taxa de erro de um serviço específico de inferências do Bing. A tabela de logs tem 65 bilhões de registros. A consulta básica a seguir filtrará 250.000 erros e criará uma série temporal de contagem de erros que usa a função de detecção de anomalias series_decompose_anomalies. As anomalias são detectadas pelo serviço do Azure Data Explorer e realçadas como pontos vermelhos no gráfico de série temporal.

Logs
| where Timestamp >= datetime(2015-08-22) and Timestamp < datetime(2015-08-23) 
| where Level == "e" and Service == "Inferences.UnusualEvents_Main" 
| summarize count() by bin(Timestamp, 5min)
| render anomalychart 

O serviço identificou alguns buckets de tempo com taxas de erro suspeitas. Use o Azure Data Explorer para aplicar zoom nesse período de tempo. Depois execute uma consulta que será agregada na coluna Message. Tente localizar os principais erros.

As partes relevantes de todo o rastreamento de pilha da mensagem serão cortadas para que os resultados se ajustem de modo mais adequado na página.

Será possível conferir uma identificação bem-sucedida dos oito principais erros. No entanto, a seguir você verá uma longa série de erros porque a mensagem de erro foi criada usando uma cadeia de caracteres de formato que continha dados de alteração:

Logs
| where Timestamp >= datetime(2015-08-22 05:00) and Timestamp < datetime(2015-08-22 06:00)
| where Level == "e" and Service == "Inferences.UnusualEvents_Main"
| summarize count() by Message 
| top 10 by count_ 
| project count_, Message 
count_ Mensagem
7125 Houve falha de ExecuteAlgorithmMethod para o método 'RunCycleFromInterimData'...
7125 Houve falha da chamada de InferenceHostService...System.NullReferenceException: A referência de objeto não foi definida para a instância de um objeto...
7124 Houve um erro inesperado do Sistema de Inferência...System.NullReferenceException: A referência de objeto não foi definida para a instância de um objeto...
5112 Houve um erro inesperado do Sistema de Inferência...System.NullReferenceException: A referência de objeto não foi definida para a instância de um objeto...
174 Houve falha da chamada de InferenceHostService...System.ServiceModel.CommunicationException: Houve um erro ao gravar no pipe:...
10 Houve falha de ExecuteAlgorithmMethod para o método 'RunCycleFromInterimData'...
10 Houve um erro do Sistema de Inferência...Microsoft.Bing.Platform.Inferences.Service.Managers.UserInterimDataManagerException:...
3 Houve falha da chamada de InferenceHostService...System.ServiceModel.CommunicationObjectFaultedException:...
1 Erro do Sistema de Inferência... SocialGraph.BOSS.OperationResponse...Serviço de Informações de Aplicativos TraceId:8292FC561AC64BED8FA243808FE74EFD...
1 Erro do Sistema de Inferência... SocialGraph.BOSS.OperationResponse...Serviço de Informações de Aplicativos TraceId: 5F79F7587FF943EC9B641E02E701AFBF...

Neste momento, recomendamos usar o operador reduce. O operador identificou 63 erros diferentes originados no mesmo ponto de instrumentação do rastreamento no código. O operador reduce ajuda a manter o foco em rastreamentos de erros adicionais e significativos nessa janela de tempo.

Logs
| where Timestamp >= datetime(2015-08-22 05:00) and Timestamp < datetime(2015-08-22 06:00)
| where Level == "e" and Service == "Inferences.UnusualEvents_Main"
| reduce by Message with threshold=0.35
| project Count, Pattern
Contagem Padrão
7125 Houve falha de ExecuteAlgorithmMethod para o método 'RunCycleFromInterimData'...
7125 Houve falha da chamada de InferenceHostService...System.NullReferenceException: A referência de objeto não foi definida para a instância de um objeto...
7124 Houve um erro inesperado do Sistema de Inferência...System.NullReferenceException: A referência de objeto não foi definida para a instância de um objeto...
5112 Houve um erro inesperado do Sistema de Inferência...System.NullReferenceException: A referência de objeto não foi definida para a instância de um objeto...
174 Houve falha da chamada de InferenceHostService...System.ServiceModel.CommunicationException: Houve um erro ao gravar no pipe:...
63 Erro do sistema de inferência.. O. Bing. Platform. inferências. *: Write * para gravar no objeto chefe. *: SocialGraph. chefe. solicitação...
10 Houve falha de ExecuteAlgorithmMethod para o método 'RunCycleFromInterimData'...
10 Houve um erro do Sistema de Inferência...Microsoft.Bing.Platform.Inferences.Service.Managers.UserInterimDataManagerException:...
3 Falha na chamada de InferenceHostService.. System. ServiceModel. *: o objeto, System. ServiceModel. Channels. * + *, para * * é o *... em sist...

Agora, você tem informações suficientes sobre os principais erros que contribuíram para as anomalias detectadas.

Para entender o efeito desses erros no sistema de exemplo, considere o seguinte:

  • A tabela Logs contém dados dimensionais adicionais, como Component e Cluster.
  • O novo plug-in do cluster automático poderá ajudar a derivar insights de componentes e clusters usando uma consulta simples.

No exemplo a seguir, será possível conferir com clareza que cada um dos quatro principais erros é específico de um componente. Além disso, embora os três principais erros sejam específicos do cluster DB4, o quarto erro ocorrerá em todos os clusters.

Logs
| where Timestamp >= datetime(2015-08-22 05:00) and Timestamp < datetime(2015-08-22 06:00)
| where Level == "e" and Service == "Inferences.UnusualEvents_Main"
| evaluate autocluster()
Contagem Porcentagem (%) Componente Cluster Mensagem
7125 26,64 InferenceHostService DB4 Houve falha de ExecuteAlgorithmMethod para o método...
7125 26,64 Componente Desconhecido DB4 Houve falha da chamada de InferenceHostService...
7124 26,64 InferenceAlgorithmExecutor DB4 Houve um erro inesperado do Sistema de Inferência...
5112 19,11 InferenceAlgorithmExecutor * Houve um erro inesperado do Sistema de Inferência...

Mapear valores de um conjunto para o outro

Um caso de uso comum de consulta é o mapeamento estático de valores. O mapeamento estático pode ajudar a tornar os resultados mais apresentáveis.

Por exemplo, na tabela a seguir, DeviceModel especificará um modelo do dispositivo. Usar o modelo do dispositivo não é um modo conveniente de fazer referência ao nome do dispositivo. 

DeviceModel Contagem
iPhone 5.1 32
iPhone 3.2 432
iPhone 7.2 55
iPhone 5.2 66

 Usar um nome amigável é mais conveniente:

FriendlyName Contagem
iPhone 5 32
iPhone 4 432
iPhone 6 55
iPhone 5 66

Os dois exemplos a seguir demonstrarão como usar um nome amigável em vez do modelo do dispositivo a fim de identificá-lo. 

Obter um mapeamento usando um dicionário dinâmico

É possível obter um mapeamento usando um dicionário e acessadores dinâmicos. Por exemplo:

// Dataset definition
let Source = datatable(DeviceModel:string, Count:long)
[
  'iPhone5,1', 32,
  'iPhone3,2', 432,
  'iPhone7,2', 55,
  'iPhone5,2', 66,
];
// Query start here
let phone_mapping = dynamic(
  {
    "iPhone5,1" : "iPhone 5",
    "iPhone3,2" : "iPhone 4",
    "iPhone7,2" : "iPhone 6",
    "iPhone5,2" : "iPhone5"
  });
Source 
| project FriendlyName = phone_mapping[DeviceModel], Count
FriendlyName Contagem
iPhone 5 32
iPhone 4 432
iPhone 6 55
iPhone 5 66

Obter um mapeamento usando uma tabela estática

Também é possível obter um mapeamento usando uma tabela persistente e um operador join.

  1. Crie uma tabela de mapeamento somente uma vez:

    .create table Devices (DeviceModel: string, FriendlyName: string) 
    
    .ingest inline into table Devices 
        ["iPhone5,1","iPhone 5"]["iPhone3,2","iPhone 4"]["iPhone7,2","iPhone 6"]["iPhone5,2","iPhone5"]
    
  2. Crie uma tabela com o conteúdo do dispositivo:

    DeviceModel FriendlyName
    iPhone 5.1 iPhone 5
    iPhone 3.2 iPhone 4
    iPhone 7.2 iPhone 6
    iPhone 5.2 iPhone 5
  3. Crie a origem da tabela de teste:

    .create table Source (DeviceModel: string, Count: int)
    
    .ingest inline into table Source ["iPhone5,1",32]["iPhone3,2",432]["iPhone7,2",55]["iPhone5,2",66]
    
  4. Una as tabelas e execute o projeto:

    Devices  
    | join (Source) on DeviceModel  
    | project FriendlyName, Count
    

Esta é a saída:

FriendlyName Contagem
iPhone 5 32
iPhone 4 432
iPhone 6 55
iPhone 5 66

Criar e usar tabelas de dimensões de tempo de consulta

Você desejará unir com frequência os resultados de uma consulta em uma tabela de dimensões ad hoc que não está armazenada no banco de dados. É possível definir uma expressão cujo resultado será uma tabela com escopo para uma consulta única.

Por exemplo:

// Create a query-time dimension table using datatable
let DimTable = datatable(EventType:string, Code:string)
  [
    "Heavy Rain", "HR",
    "Tornado",    "T"
  ]
;
DimTable
| join StormEvents on EventType
| summarize count() by Code

Veja um exemplo um pouco mais complexo:

// Create a query-time dimension table using datatable
let TeamFoundationJobResult = datatable(Result:int, ResultString:string)
  [
    -1, 'None', 0, 'Succeeded', 1, 'PartiallySucceeded', 2, 'Failed',
    3, 'Stopped', 4, 'Killed', 5, 'Blocked', 6, 'ExtensionNotFound',
    7, 'Inactive', 8, 'Disabled', 9, 'JobInitializationError'
  ]
;
JobHistory
  | where PreciseTimeStamp > ago(1h)
  | where Service  != "AX"
  | where Plugin has "Analytics"
  | sort by PreciseTimeStamp desc
  | join kind=leftouter TeamFoundationJobResult on Result
  | extend ExecutionTimeSpan = totimespan(ExecutionTime)
  | project JobName, StartTime, ExecutionTimeSpan, ResultString, ResultMessage

Recuperar os registros mais recentes por carimbo de data/hora e identidade

Suponha que você tenha uma tabela que inclui:

  • Uma coluna ID que identifica a entidade com a qual cada linha está associada, como uma ID de usuário ou uma ID de nó
  • Uma coluna timestamp que fornece a referência de tempo da linha
  • Outras colunas

É possível usar o operador top-nested para fazer uma consulta que retornará os dois registros mais recentes de cada valor da coluna , em que o mais recente será definido como ter o maior valor de :

datatable(id:string, timestamp:datetime, bla:string)           // #1
  [
  "Barak",  datetime(2015-01-01), "1",
  "Barak",  datetime(2016-01-01), "2",
  "Barak",  datetime(2017-01-20), "3",
  "Donald", datetime(2017-01-20), "4",
  "Donald", datetime(2017-01-18), "5",
  "Donald", datetime(2017-01-19), "6"
  ]
| top-nested   of id        by dummy0=max(1),                  // #2
  top-nested 2 of timestamp by dummy1=max(timestamp),          // #3
  top-nested   of bla       by dummy2=max(1)                   // #4
| project-away dummy0, dummy1, dummy2                          // #5

Veja uma explicação passo a passo sobre a consulta anterior (a numeração se refere aos números nos comentários do código):

  1. Usar o operador datatable é um modo de produzir alguns dados de teste para fins de demonstração. Neste momento, geralmente são usados dados reais.
  2. Essa linha basicamente significa retornar todos os valores distintos de .
  3. Essa linha retornará para os dois primeiros registros que maximizarão:
    • A coluna timestamp
    • As colunas do nível anterior (somente id, neste exemplo)
    • A coluna especificada neste nível (timestamp, neste exemplo)
  4. Essa linha adicionará os valores da coluna bla para cada um dos registros retornados pelo nível anterior. Caso a tabela tenha outras colunas nas quais você esteja interessado, será possível repetir essa linha para cada uma delas.
  5. A linha final usa o operador projeto-ausente para remover as colunas "extras" que são introduzidas pelo .

Estender uma tabela por um percentual do cálculo total

Uma expressão de tabela que inclui uma coluna numérica será mais útil para o usuário quando for acompanhada pelo seu respectivo valor como um percentual do total.

Por exemplo, suponha que uma consulta produza a seguinte tabela:

SomeSeries SomeInt
Apple 100
Banana 200

Você deseja mostrar a tabela deste modo:

SomeSeries SomeInt Pct
Apple 100 33,3
Banana 200 66,6

Calcule o total (soma) da coluna SomeInt e divida cada valor dessa coluna pelo total para alterar o modo como a tabela será exibida. Use o operador as para obter resultados arbitrários.

Por exemplo:

// The following table literally represents a long calculation
// that ends up with an anonymous tabular value:
datatable (SomeInt:int, SomeSeries:string) [
  100, "Apple",
  200, "Banana",
]
// We now give this calculation a name ("X"):
| as X
// Having this name we can refer to it in the sub-expression
// "X | summarize sum(SomeInt)":
| extend Pct = 100 * bin(todouble(SomeInt) / toscalar(X | summarize sum(SomeInt)), 0.001)

Executar agregações em uma janela deslizante

O exemplo a seguir mostrará como resumir colunas usando uma janela deslizante. Use a tabela a seguir, que contém preços de frutas por carimbos de data/hora para obter uma consulta.

Calcule os custos mínimos, máximos e a soma de cada fruta por dia usando uma janela deslizante de sete dias. Cada registro agregará os sete dias anteriores no conjunto de resultados. Além disso, os resultados conterão um registro por dia no período de análise.

Tabela de frutas:

Timestamp Fruta Preço
24/09/2018 21:00:00.0000000 Bananas 3
25/09/2018 20:00:00.0000000 Maçãs 9
26/09/2018 03:00:00.0000000 Bananas 4
27/09/2018 10:00:00.0000000 Ameixas 8
28/09/2018 07:00:00.0000000 Bananas 6
29/09/2018 21:00:00.0000000 Bananas 8
30/09/2018 01:00:00.0000000 Ameixas 2
01/10/2018 05:00:00.0000000 Bananas 0
02/10/2018 02:00:00.0000000 Bananas 0
03/10/2018 13:00:00.0000000 Ameixas 4
04/10/2018 14:00:00.0000000 Maçãs 8
05/10/2018 05:00:00.0000000 Bananas 2
06/10/2018 08:00:00.0000000 Ameixas 8
07/10/2018 12:00:00.0000000 Bananas 0

Veja a consulta de agregação da janela deslizante. Confira a explicação após o resultado da consulta.

let _start = datetime(2018-09-24);
let _end = _start + 13d; 
Fruits 
| extend _bin = bin_at(Timestamp, 1d, _start) // #1 
| extend _endRange = iif(_bin + 7d > _end, _end, 
                            iff( _bin + 7d - 1d < _start, _start,
                                iff( _bin + 7d - 1d < _bin, _bin,  _bin + 7d - 1d)))  // #2
| extend _range = range(_bin, _endRange, 1d) // #3
| mv-expand _range to typeof(datetime) limit 1000000 // #4
| summarize min(Price), max(Price), sum(Price) by Timestamp=bin_at(_range, 1d, _start) ,  Fruit // #5
| where Timestamp >= _start + 7d; // #6

Esta é a saída:

Timestamp Fruta min_Price max_Price sum_Price
01/10/2018 00:00:00.0000000 Maçãs 9 9 9
01/10/2018 00:00:00.0000000 Bananas 0 8 18
01/10/2018 00:00:00.0000000 Ameixas 2 8 10
02/10/2018 00:00:00.0000000 Bananas 0 8 18
02/10/2018 00:00:00.0000000 Ameixas 2 8 10
03/10/2018 00:00:00.0000000 Ameixas 2 8 14
03/10/2018 00:00:00.0000000 Bananas 0 8 14
04/10/2018 00:00:00.0000000 Bananas 0 8 14
04/10/2018 00:00:00.0000000 Ameixas 2 4 6
04/10/2018 00:00:00.0000000 Maçãs 8 8 8
05/10/2018 00:00:00.0000000 Bananas 0 8 10
05/10/2018 00:00:00.0000000 Ameixas 2 4 6
05/10/2018 00:00:00.0000000 Maçãs 8 8 8
06/10/2018 00:00:00.0000000 Ameixas 2 8 14
06/10/2018 00:00:00.0000000 Bananas 0 2 2
06/10/2018 00:00:00.0000000 Maçãs 8 8 8
07/10/2018 00:00:00.0000000 Bananas 0 2 2
07/10/2018 00:00:00.0000000 Ameixas 4 8 12
07/10/2018 00:00:00.0000000 Maçãs 8 8 8

A consulta "ampliará" (duplicará) cada registro na tabela de entrada durante sete dias após mostrar sua respectiva aparência real. Cada registro aparecerá sete vezes. Como resultado, a agregação diária incluirá todos os registros dos sete dias anteriores.

Veja uma explicação passo a passo sobre a consulta anterior:

  1. Compartimentalize cada registro em um dia (relativo a _start).
  2. Determine o fim do intervalo por registro: _bin + 7d, a menos que o valor esteja fora dos intervalos de _start e _end, nesse caso, ele será ajustado.
  3. Crie uma matriz de sete dias (carimbos de data/hora) para cada registro, começando no dia do registro atual.
  4. Use o operador mv-expand na matriz duplicando, desse modo, cada registro em sete registros com um dia de diferença entre eles.
  5. Execute a função de agregação para cada dia. Devido ao nº 4, essa etapa resumirá os últimos sete dias.
  6. Os dados dos primeiros sete dias estão incompletos porque não há um período de retrospectiva para os primeiros sete dias. Os primeiros sete dias serão excluídos do resultado final. No exemplo, eles participam somente da agregação de 01/10/2018.

Localizar o evento anterior

O exemplo a seguir demonstrará como localizar um evento anterior entre dois conjuntos de dados.

Você tem dois conjuntos de dados, A e B. Para cada registro no conjuntos de dados B, encontre seu evento anterior no conjuntos de dados A (ou seja, o registro em A que arg_max ainda é mais arg_max que B).

Veja conjuntos de dados de exemplo:

let A = datatable(Timestamp:datetime, ID:string, EventA:string)
[
    datetime(2019-01-01 00:00:00), "x", "Ax1",
    datetime(2019-01-01 00:00:01), "x", "Ax2",
    datetime(2019-01-01 00:00:02), "y", "Ay1",
    datetime(2019-01-01 00:00:05), "y", "Ay2",
    datetime(2019-01-01 00:00:00), "z", "Az1"
];
let B = datatable(Timestamp:datetime, ID:string, EventB:string)
[
    datetime(2019-01-01 00:00:03), "x", "B",
    datetime(2019-01-01 00:00:04), "x", "B",
    datetime(2019-01-01 00:00:04), "y", "B",
    datetime(2019-01-01 00:02:00), "z", "B"
];
A; B
Timestamp ID EventB
01/01/2019 00:00:00.0000000 x Ax1
01/01/2019 00:00:00.0000000 z Az1
01/01/2019 00:00:01.0000000 x Ax2
01/01/2019 00:00:02.0000000 s Ay1
01/01/2019 00:00:05.0000000 s Ay2

Timestamp ID EventA
01/01/2019 00:00:03.0000000 x B
01/01/2019 00:00:04.0000000 x B
01/01/2019 00:00:04.0000000 s B
01/01/2019 00:02:00.0000000 z B

Saída esperada:

ID Timestamp EventB A_Timestamp EventA
x 01/01/2019 00:00:03.0000000 B 01/01/2019 00:00:01.0000000 Ax2
x 01/01/2019 00:00:04.0000000 B 01/01/2019 00:00:01.0000000 Ax2
s 01/01/2019 00:00:04.0000000 B 01/01/2019 00:00:02.0000000 Ay1
z 01/01/2019 00:02:00.0000000 B 01/01/2019 00:00:00.0000000 Az1

Recomendamos lidar com esse problema usando duas abordagens diferentes. É possível testar as duas abordagens em um conjunto de dados específico a fim de encontrar a mais adequada para seu cenário.

Observação

Cada abordagem poderá ser executada de modo diferente em conjuntos de dados distintos.

Abordagem 1

Essa abordagem serializa os conjuntos de dados por ID e carimbo de data/hora. Depois ela agrupará todos os eventos do conjunto de dados B com os respectivos eventos anteriores no conjunto de dados A. Por fim, ela escolherá o registro arg_max de todos os eventos do conjunto de dados no grupo.

A
| extend A_Timestamp = Timestamp, Kind="A"
| union (B | extend B_Timestamp = Timestamp, Kind="B")
| order by ID, Timestamp asc 
| extend t = iff(Kind == "A" and (prev(Kind) != "A" or prev(Id) != ID), 1, 0)
| extend t = row_cumsum(t)
| summarize Timestamp=make_list(Timestamp), EventB=make_list(EventB), arg_max(A_Timestamp, EventA) by t, ID
| mv-expand Timestamp to typeof(datetime), EventB to typeof(string)
| where isnotempty(EventB)
| project-away t

Abordagem 2

Essa abordagem requer um período de retrospectiva máximo para resolver o problema. A abordagem examina se o registro no conjunto de dados A é muito anterior ao conjunto de dados B. Depois, o método une os dois conjuntos de dados com base na ID e nesse período de retrospectiva.

O operador join produzirá todos os possíveis candidatos, além de registros do conjunto de dados A anteriores aos registros do conjunto de dados B e dentro do período de retrospectiva. Depois, o mais próximo do conjunto de dados B será filtrado por arg_min (TimestampB - TimestampA). Quanto menor for o período de retrospectiva, mais adequado será o resultado da consulta.

No exemplo a seguir, o período de retrospectiva será definido como 1m. O registro com a ID z não tem um evento A correspondente porque o evento A é anterior em dois minutos.

let _maxLookbackPeriod = 1m;  
let _internalWindowBin = _maxLookbackPeriod / 2;
let B_events = B 
    | extend ID = new_guid()
    | extend _time = bin(Timestamp, _internalWindowBin)
    | extend _range = range(_time - _internalWindowBin, _time + _maxLookbackPeriod, _internalWindowBin) 
    | mv-expand _range to typeof(datetime) 
    | extend B_Timestamp = Timestamp, _range;
let A_events = A 
    | extend _time = bin(Timestamp, _internalWindowBin)
    | extend _range = range(_time - _internalWindowBin, _time + _maxLookbackPeriod, _internalWindowBin) 
    | mv-expand _range to typeof(datetime) 
    | extend A_Timestamp = Timestamp, _range;
B_events
    | join kind=leftouter (
        A_events
) on ID, _range
| where isnull(A_Timestamp) or (A_Timestamp <= B_Timestamp and B_Timestamp <= A_Timestamp + _maxLookbackPeriod)
| extend diff = coalesce(B_Timestamp - A_Timestamp, _maxLookbackPeriod*2)
| summarize arg_min(diff, *) by ID
| project ID, B_Timestamp, A_Timestamp, EventB, EventA
ID B_Timestamp A_Timestamp EventB EventA
x 01/01/2019 00:00:03.0000000 01/01/2019 00:00:01.0000000 B Ax2
x 01/01/2019 00:00:04.0000000 01/01/2019 00:00:01.0000000 B Ax2
s 01/01/2019 00:00:04.0000000 01/01/2019 00:00:02.0000000 B Ay1
z 01/01/2019 00:02:00.0000000 B

Próximas etapas

Este artigo identificará necessidades comuns de consultas no Azure Monitor e de que modo será possível usar a Linguagem de Consulta do Azure Data Explorer para atendê-las.

Operações da cadeia de caracteres

As seções a seguir fornecerão exemplos de como trabalhar com cadeias de caracteres ao usar a Linguagem de Consulta do Azure Data Explorer.

As cadeias de caracteres e como escapar delas

Os valores da cadeia de caracteres são agrupados com aspas simples ou duplas. Adicione a barra invertida (\) à esquerda de um caractere para escapar do caractere: \t para Tab, \n para nova linha e \" para o caractere aspa simples.

print "this is a 'string' literal in double \" quotes"
print 'this is a "string" literal in single \' quotes'

Para impedir que "\" atue como um caractere de escape, adicione "@" como um prefixo à cadeia de caracteres:

print @"C:\backslash\not\escaped\with @ prefix"

Comparações de cadeias de caracteres

Operador Descrição Diferenciar maiúsculas de minúsculas Exemplo (suspende true)
== Igual a Sim "aBc" == "aBc"
!= Diferente de Sim "abc" != "ABC"
=~ Igual a Não "abc" =~ "ABC"
!~ Diferente de Não "aBc" !~ "xyz"
has O valor do lado direito é um termo completo no valor do lado esquerdo Não "North America" has "america"
!has O valor do lado direito não é um termo completo no valor do lado esquerdo Não "North America" !has "amer"
has_cs O valor do lado direito é um termo completo no valor do lado esquerdo Sim "North America" has_cs "America"
!has_cs O valor do lado direito não é um termo completo no valor do lado esquerdo Sim "North America" !has_cs "amer"
hasprefix O valor do lado direito é prefixo de um termo no valor do lado esquerdo Não "North America" hasprefix "ame"
!hasprefix O valor do lado direito não é prefixo de um termo no valor do lado esquerdo Não "North America" !hasprefix "mer"
hasprefix_cs O valor do lado direito é prefixo de um termo no valor do lado esquerdo Sim "North America" hasprefix_cs "Ame"
!hasprefix_cs O valor do lado direito não é prefixo de um termo no valor do lado esquerdo Sim "North America" !hasprefix_cs "CA"
hassuffix O valor do lado direito é sufixo de um termo no valor do lado esquerdo Não "North America" hassuffix "ica"
!hassuffix O valor do lado direito não é sufixo de um termo no valor do lado esquerdo Não "North America" !hassuffix "americ"
hassuffix_cs O valor do lado direito é sufixo de um termo no valor do lado esquerdo Sim "North America" hassuffix_cs "ica"
!hassuffix_cs O valor do lado direito não é sufixo de um termo no valor do lado esquerdo Sim "North America" !hassuffix_cs "icA"
contains O valor do lado direito ocorre como uma subsequência do valor do lado esquerdo Não "FabriKam" contains "BRik"
!contains O valor do lado direito não ocorre no valor do lado esquerdo Não "Fabrikam" !contains "xyz"
contains_cs O valor do lado direito ocorre como uma subsequência do valor do lado esquerdo Sim "FabriKam" contains_cs "Kam"
!contains_cs O valor do lado direito não ocorre no valor do lado esquerdo Sim "Fabrikam" !contains_cs "Kam"
startswith O valor do lado direito é uma subsequência inicial do valor do lado esquerdo Não "Fabrikam" startswith "fab"
!startswith O valor do lado direito não é uma subsequência inicial do valor do lado esquerdo Não "Fabrikam" !startswith "kam"
startswith_cs O valor do lado direito é uma subsequência inicial do valor do lado esquerdo Sim "Fabrikam" startswith_cs "Fab"
!startswith_cs O valor do lado direito não é uma subsequência inicial do valor do lado esquerdo Sim "Fabrikam" !startswith_cs "fab"
endswith O valor do lado direito é uma subsequência de fechamento do valor do lado esquerdo Não "Fabrikam" endswith "Kam"
!endswith O valor do lado direito não é uma subsequência de fechamento do valor do lado esquerdo Não "Fabrikam" !endswith "brik"
endswith_cs O valor do lado direito é uma subsequência de fechamento do valor do lado esquerdo Sim "Fabrikam" endswith "Kam"
!endswith_cs O valor do lado direito não é uma subsequência de fechamento do valor do lado esquerdo Sim "Fabrikam" !endswith "brik"
matches regex O valor do lado esquerdo contém uma correspondência do valor do lado direito Sim "Fabrikam" matches regex "b.*k"
in Equivale a um dos elementos Sim "abc" in ("123", "345", "abc")
!in Não equivale a qualquer um dos elementos Sim "bca" !in ("123", "345", "abc")

countof

Conta ocorrências de uma substring em uma cadeia de caracteres. Ele pode corresponder a cadeias de caracteres sem formatação ou usar uma expressão regular (regex). As correspondências de uma cadeia de caracteres sem formatação podem se sobrepor, porém o mesmo não ocorre com as correspondências de regex.

countof(text, search [, kind])
  • text: A cadeia de caracteres de entrada
  • search: uma cadeia de caracteres sem formatação ou um regex que obtém uma correspondência dentro do texto
  • kind: kind | | normal (padrão: normal).

Retorna o número de vezes que a cadeia de caracteres de pesquisa pode obter uma correspondência no contêiner. As correspondências de uma cadeia de caracteres sem formatação podem se sobrepor, porém o mesmo não ocorre com as correspondências de regex.

Correspondências de cadeia de caracteres sem formatação

print countof("The cat sat on the mat", "at");  //result: 3
print countof("aaa", "a");  //result: 3
print countof("aaaa", "aa");  //result: 3 (not 2!)
print countof("ababa", "ab", "normal");  //result: 2
print countof("ababa", "aba");  //result: 2

Correspondências de regex

print countof("The cat sat on the mat", @"\b.at\b", "regex");  //result: 3
print countof("ababa", "aba", "regex");  //result: 1
print countof("abcabc", "a.c", "regex");  // result: 2

extract

Obtém uma correspondência para uma expressão regular de uma cadeia de caracteres específica. Como alternativa, o operador poderá converter uma substring extraída de um tipo especificado.

extract(regex, captureGroup, text [, typeLiteral])
  • regex: uma expressão regular.
  • captureGroup: uma constante de inteiro positivo que indica o grupo de captura a ser extraído. Use 0 para a correspondência inteira, 1 para o valor correspondido pelo primeiro parêntese () na expressão regular e 2 ou mais para parênteses subsequentes.
  • text – A cadeia de caracteres a ser pesquisada.
  • typeLiteral – Um literal de tipo opcional (por exemplo, typeof(long)). Se for fornecido, a subcadeia de caracteres extraída será convertida para esse tipo.

Retorna uma substring correspondente ao grupo de captura captureGroup indicado, convertida em typeLiteral, como alternativa. Retorna nulo, caso não haja correspondência ou ocorra uma falha na conversão de tipo.

O seguinte exemplo extrairá o octeto de ComputerIP mais recente de um registro de pulsação:

Heartbeat
| where ComputerIP != "" 
| take 1
| project ComputerIP, last_octet=extract("([0-9]*$)", 1, ComputerIP) 

O seguinte exemplo extrairá o octeto mais recente, convertendo-o em um tipo real (número). Depois ele calculará o próximo valor de IP:

Heartbeat
| where ComputerIP != "" 
| take 1
| extend last_octet=extract("([0-9]*$)", 1, ComputerIP, typeof(real)) 
| extend next_ip=(last_octet+1)%255
| project ComputerIP, last_octet, next_ip

No exemplo a seguir, a cadeia de caracteres Trace será pesquisada em busca de uma definição de Duration. A correspondência será convertida em real e multiplicada por uma constante de tempo (1s), que converterá Duration no tipo timespan.

let Trace="A=12, B=34, Duration=567, ...";
print Duration = extract("Duration=([0-9.]+)", 1, Trace, typeof(real));  //result: 567
print Duration_seconds =  extract("Duration=([0-9.]+)", 1, Trace, typeof(real)) * time(1s);  //result: 00:09:27

isempty, isnotempty e notempty

  • O operador isempty retornará true caso o argumento seja uma cadeia de caracteres vazia ou nula (confira isnull).
  • O operador isnotempty retornará true caso o argumento não seja uma cadeia de caracteres vazia ou nula (confira isnotnull). Alias: notempty.
isempty(value)
isnotempty(value)

Exemplo

print isempty("");  // result: true

print isempty("0");  // result: false

print isempty(0);  // result: false

print isempty(5);  // result: false

Heartbeat | where isnotempty(ComputerIP) | take 1  // return 1 Heartbeat record in which ComputerIP isn't empty

parseurl

Divide uma URL em várias partes, como protocolo, host e porta, além de retornar um objeto de dicionário que contém partes semelhantes às cadeias de caracteres.

parseurl(urlstring)

Exemplo

print parseurl("http://user:pass@contoso.com/icecream/buy.aspx?a=1&b=2#tag")

Esta é a saída:

{
	"Scheme" : "http",
	"Host" : "contoso.com",
	"Port" : "80",
	"Path" : "/icecream/buy.aspx",
	"Username" : "user",
	"Password" : "pass",
	"Query Parameters" : {"a":"1","b":"2"},
	"Fragment" : "tag"
}

replace

Substitui todas as correspondências de regex por outra cadeia de caracteres.

replace(regex, rewrite, input_text)
  • regex: a expressão regular que deve obter uma correspondência. Ele pode conter grupos de captura entre parênteses ().
  • rewrite: o regex de substituição para todas as correspondências obtidas por um regex correspondente. Use \0 para se referir à uma correspondência inteira, \1 para o primeiro grupo de captura, \2 para o segundo e assim por diante para os grupos de captura subsequentes.
  • input_text: a cadeia de caracteres de entrada a ser pesquisada.

Retorna o texto depois de substituir todas as correspondências de regex por avaliações de reescrita. Correspondências não se sobrepõem.

Exemplo

SecurityEvent
| take 1
| project Activity 
| extend replaced = replace(@"(\d+) -", @"Activity ID \1: ", Activity) 

Esta é a saída:

Atividade Substituído
4663 - Foi feita uma tentativa de acessar um objeto ID da Atividade 4663: Foi feita uma tentativa de acessar um objeto.

split

Divide uma cadeia de caracteres especificada de acordo com um delimitador característico e retorna uma matriz de substrings resultantes.

split(source, delimiter [, requestedIndex])
  • source: a cadeia de caracteres a ser dividida de acordo com um delimitador especificado.
  • delimiter: o delimitador que será usado para dividir a cadeia de caracteres de origem.
  • requestedIndex: um índice opcional baseado em zero. Caso seja fornecida, a matriz da cadeia de caracteres retornada conterá somente esse item (se existir).

Exemplo

print split("aaa_bbb_ccc", "_");    // result: ["aaa","bbb","ccc"]
print split("aa_bb", "_");          // result: ["aa","bb"]
print split("aaa_bbb_ccc", "_", 1);	// result: ["bbb"]
print split("", "_");              	// result: [""]
print split("a__b", "_");           // result: ["a","","b"]
print split("aabbcc", "bb");        // result: ["aa","cc"]

strcat

Concatena os argumentos de cadeia de caracteres (dá suporte para 1 a 16 argumentos).

strcat("string1", "string2", "string3")

Exemplo

print strcat("hello", " ", "world")	// result: "hello world"

strlen

Retorna o comprimento de uma cadeia de caracteres.

strlen("text_to_evaluate")

Exemplo

print strlen("hello")	// result: 5

substring

Extrai uma substring de uma cadeia de caracteres de origem especificada, começando no índice especificado. Como alternativa, será possível especificar o comprimento da substring solicitada.

substring(source, startingIndex [, length])
  • source: a cadeia de caracteres de origem da qual a substring será retirada.
  • startingIndex: a posição do caractere inicial baseado em zero da substring solicitada.
  • length: um parâmetro opcional que poderá ser usado para especificar o comprimento solicitado da substring retornada.

Exemplo

print substring("abcdefg", 1, 2);	// result: "bc"
print substring("123456", 1);		// result: "23456"
print substring("123456", 2, 2);	// result: "34"
print substring("ABCD", 0, 2);	// result: "AB"

tolower e toupper

Converte uma cadeia de caracteres específica em letras minúsculas ou maiúsculas.

tolower("value")
toupper("value")

Exemplo

print tolower("HELLO");	// result: "hello"
print toupper("hello");	// result: "HELLO"

Operações de data e hora

As seções a seguir fornecerão exemplos de como trabalhar com valores temporais e dados ao usar a Linguagem de Consulta do Azure Data Explorer.

Noções básicas sobre datas e horários

A Linguagem de Consulta do Azure Data Explorer tem dois tipos de dados principais associados a datas e horários: datetime e timespan. Todas as datas são expressas em UTC. Embora vários formatos de data e hora sejam compatíveis, o formato ISO-8601 é preferencial.

Intervalos de tempo (timespans) são expressos como um decimal seguido por uma unidade de tempo:

Abreviação Unidade de tempo
d dia
h hour
m minute
s second
ms milissegundo
microssegundo microssegundo
tique nanossegundo

É possível criar valores de data e hora ao converter uma cadeia de caracteres usando o operador todatetime. Por exemplo, use o operador between para especificar um intervalo de tempo a fim de examinar pulsações de VMs enviadas em um período de tempo específico:

Heartbeat
| where TimeGenerated between(datetime("2018-06-30 22:46:42") .. datetime("2018-07-01 00:57:27"))

Outro cenário comum é comparar um valor de data e hora com o presente. Por exemplo, para ver todas as pulsações durante os últimos dois minutos, você pode usar o operador now junto com um timespan que representa dois minutos:

Heartbeat
| where TimeGenerated > now() - 2m

Um atalho também está disponível para esta função:

Heartbeat
| where TimeGenerated > now(-2m)

No entanto, o método mais rápido e legível é usar o operador ago:

Heartbeat
| where TimeGenerated > ago(2m)

Suponha que em vez de saber quais são os horários de início e término, você saiba a hora de início e a duração. É possível reescrever a consulta:

let startDatetime = todatetime("2018-06-30 20:12:42.9");
let duration = totimespan(25m);
Heartbeat
| where TimeGenerated between(startDatetime .. (startDatetime+duration) )
| extend timeFromStart = TimeGenerated - startDatetime

Converter unidades de tempo

Talvez você queira expressar um valor de data/hora ou intervalo de tempo em uma unidade de tempo diferente do padrão. Por exemplo, caso esteva revisando eventos de erros dos últimos 30 minutos e precise de uma coluna calculada que mostre há quanto tempo o evento ocorreu, você poderá usar esta consulta:

Event
| where TimeGenerated > ago(30m)
| where EventLevelName == "Error"
| extend timeAgo = now() - TimeGenerated 

A coluna timeAgo contém valores idênticos a 00:09:31.5118992, que são formatados como hh:mm:ss.fffffff. Caso queira formatar esses valores com o number de minutos transcorridos da hora de início, divida esse valor por 1m:

Event
| where TimeGenerated > ago(30m)
| where EventLevelName == "Error"
| extend timeAgo = now() - TimeGenerated
| extend timeAgoMinutes = timeAgo/1m 

Agregações e particionamento por intervalos de tempo

Outro cenário comum é a necessidade de obter estatísticas de um período de tempo específico em uma determinada unidade de tempo. É possível usar um operador bin como parte de uma cláusula summarize para esse cenário.

Use a seguinte consulta para obter o número de eventos que ocorreram a cada cinco minutos durante a última meia hora:

Event
| where TimeGenerated > ago(30m)
| summarize events_count=count() by bin(TimeGenerated, 5m) 

Essa consulta produz a seguinte tabela:

TimeGenerated(UTC) events_count
2018-08-01T09:30:00 54
2018-08-01T09:35:00 41
2018-08-01T09:40:00 42
2018-08-01T09:45:00 41
2018-08-01T09:50:00 41
2018-08-01T09:55:00 16

Outro modo de criar buckets de resultados é usar funções como startofday:

Event
| where TimeGenerated > ago(4d)
| summarize events_count=count() by startofday(TimeGenerated) 

Esta é a saída:

timestamp count_
2018-07-28T00:00:00 7.136
2018-07-29T00:00:00 12.315
2018-07-30T00:00:00 16.847
2018-07-31T00:00:00 12.616
2018-08-01T00:00:00 5.416

Fusos horários

Como todos os valores de data e hora são expressos em UTC, normalmente será útil converter esses valores no fuso horário local. Por exemplo, use este cálculo para converter horas UTC em horas PST:

Event
| extend localTimestamp = TimeGenerated - 8h

Agregações

As seções a seguir fornecerão exemplos de como agregar resultados de uma consulta ao usar a Linguagem de Consulta do Azure Data Explorer.

contagem

Conte o número de linhas no conjunto de resultados depois que os filtros forem aplicados. O exemplo a seguir retornará o número total de linhas da tabela Perf dos últimos 30 minutos. Os resultados serão retornados em uma coluna chamada count_, a menos que você atribua um nome específico a ela:

Perf
| where TimeGenerated > ago(30m) 
| summarize count()
Perf
| where TimeGenerated > ago(30m) 
| summarize num_of_records=count() 

Uma visualização do gráfico de tempo poderá ser útil para conferir uma determinada tendência ao longo do tempo:

Perf 
| where TimeGenerated > ago(30m) 
| summarize count() by bin(TimeGenerated, 5m)
| render timechart

A saída deste exemplo mostrará a linha de tendência de contagem de registros Perf em intervalos de cinco minutos:

Uma captura de tela de um gráfico de linhas que mostra a linha de tendência de contagem de registros Perf em intervalos de cinco minutos.

dcount e dcountif

Use dcountedcountif para contar valores distintos em uma coluna específica. A consulta a seguir avalia quantos computadores distintos enviam pulsações na última hora:

Heartbeat 
| where TimeGenerated > ago(1h) 
| summarize dcount(Computer)

Para contar apenas os computadores Linux que enviaram heartbeats, use dcountif:

Heartbeat 
| where TimeGenerated > ago(1h) 
| summarize dcountif(Computer, OSType=="Linux")

Avaliar subgrupos

Para executar uma contagem ou outras agregações em subgrupos em seus dados, use a by palavra-chave. Por exemplo, use a consulta abaixo para contar o número de computadores Linux distintos que enviaram pulsações em cada país ou região:

Heartbeat 
| where TimeGenerated > ago(1h) 
| summarize distinct_computers=dcountif(Computer, OSType=="Linux") by RemoteIPCountry
RemoteIPCountry distinct_computers
Estados Unidos 19
Canada 3
Irlanda 0
Reino Unido 0
Países Baixos 2

Adicione nomes de colunas à seção by para analisar até mesmo subgrupos menores de seus dados. Por exemplo, talvez você queira contar computadores distintos de cada país ou região por tipo de sistema operacional (OSType):

Heartbeat 
| where TimeGenerated > ago(1h) 
| summarize distinct_computers=dcountif(Computer, OSType=="Linux") by RemoteIPCountry, OSType

Percentil

Para encontrar o valor mediano, use a função percentile com um valor para especificar o percentil:

Perf
| where TimeGenerated > ago(30m) 
| where CounterName == "% Processor Time" and InstanceName == "_Total" 
| summarize percentiles(CounterValue, 50) by Computer

Também é possível especificar diferentes percentuais para obter um resultado agregado para cada um deles:

Perf
| where TimeGenerated > ago(30m) 
| where CounterName == "% Processor Time" and InstanceName == "_Total" 
| summarize percentiles(CounterValue, 25, 50, 75, 90) by Computer

Os resultados poderão mostrar que algumas CPUs de computadores têm valores medianos semelhantes. No entanto, embora alguns computadores fiquem estáveis perto da mediana, outros relataram valores de CPU muito mais baixos ou mais altos. Os valores altos e baixos significam que os computadores tiveram picos.

Variância

Para avaliar diretamente a variância de um valor, use os métodos de desvio padrão e variância:

Perf
| where TimeGenerated > ago(30m) 
| where CounterName == "% Processor Time" and InstanceName == "_Total" 
| summarize stdev(CounterValue), variance(CounterValue) by Computer

Um modo adequado de analisar a estabilidade do uso da CPU é combinar stdev com o cálculo mediano:

Perf
| where TimeGenerated > ago(130m) 
| where CounterName == "% Processor Time" and InstanceName == "_Total" 
| summarize stdev(CounterValue), percentiles(CounterValue, 50) by Computer

Gerar listas e conjuntos

É possível usar makelist para dinamizar dados pela ordem de valores em uma coluna específica. Por exemplo, talvez você queira explorar os eventos de ordem mais comuns que ocorrem em seus computadores. É possível dinamizar os dados basicamente pela ordem de valores de EventID em cada computador:

Event
| where TimeGenerated > ago(12h)
| order by TimeGenerated desc
| summarize makelist(EventID) by Computer

Esta é a saída:

Computador list_EventID
computador1 [704,701,1501,1500,1085,704,704,701]
computador2 [326,105,302,301,300,102]
... ...

makelist gera uma lista na ordem em que os dados foram passados para ela. Use asc na instrução order em vez de desc para classificar eventos do mais antigo para o mais recente.

Talvez você ache útil criar uma lista somente de valores distintos. Essa lista é chamada de conjuntoe você pode gerá-la usando o comando:

Event
| where TimeGenerated > ago(12h)
| order by TimeGenerated desc
| summarize makeset(EventID) by Computer

Esta é a saída:

Computador list_EventID
computador1 [704,701,1501,1500,1085]
computador2 [326,105,302,301,300,102]
... ...

Do mesmo modo que makelist, makeset também funciona com os dados ordenados. O comando makeset gera matrizes com base na ordem das linhas que são passadas para ele.

Expandir listas

A operação inversa de makelist ou makeset é mv-expand. O comando mv-expand expande uma lista de valores para separar as linhas. Ele pode se expandir em qualquer número de colunas dinâmicas, incluindo JSON e colunas de matriz. Por exemplo, é possível verificar a tabela Heartbeat em busca de soluções que enviaram dados de computadores e que, por sua vez, enviaram uma pulsação na última hora:

Heartbeat
| where TimeGenerated > ago(1h)
| project Computer, Solutions

Esta é a saída:

Computador Soluções
computador1 "segurança", "atualizações", "changeTracking"
computador2 "segurança", "atualizações"
Computador3 "antiMalware", "controle de alterações"
... ...

Use mv-expand para mostrar cada valor em uma linha separada em vez de uma lista separada por vírgula:

Heartbeat
| where TimeGenerated > ago(1h)
| project Computer, split(Solutions, ",")
| mv-expand Solutions

Esta é a saída:

Computador Soluções
computador1 "segurança"
computador1 "atualizações"
computador1 "controle de alterações"
computador2 "segurança"
computador2 "atualizações"
Computador3 "antiMalware"
Computador3 "controle de alterações"
... ...

É possível usar makelist para agrupar itens. É possível conferir uma lista de computadores por solução na saída:

Heartbeat
| where TimeGenerated > ago(1h)
| project Computer, split(Solutions, ",")
| mv-expand Solutions
| summarize makelist(Computer) by tostring(Solutions) 

Esta é a saída:

Soluções list_Computer
"segurança" ["Computador1", "computador2"]
"atualizações" ["Computador1", "computador2"]
"controle de alterações" ["Computador1", "Computador3"]
"antiMalware" ["Computador3"]
... ...

Compartimentos ausentes

Uma aplicação útil de mv-expand é preencher valores padrão para compartimentos ausentes. Por exemplo, suponha que você esteja procurando o tempo de atividade de um computador específico explorando sua respectiva pulsação. Você também deseja conferir a origem da pulsação que está na coluna Category. Normalmente, usaríamos uma instrução summarize básica:

Heartbeat
| where TimeGenerated > ago(12h)
| summarize count() by Category, bin(TimeGenerated, 1h)

Esta é a saída:

Category TimeGenerated count_
Agente direto 2017-06-06T17:00:00Z 15
Agente direto 2017-06-06T18:00:00Z 60
Agente direto 2017-06-06T20:00:00Z 55
Agente direto 2017-06-06T21:00:00Z 57
Agente direto 2017-06-06T22:00:00Z 60
... ... ...

Na saída, o bucket associado a "06/06/2017T19:00:00Z" está ausente porque não há dados de pulsação para esse horário. Use a função make-series para atribuir um valor padrão a depósitos vazios. Uma linha será gerada para cada categoria. A saída incluirá duas colunas de matriz adicionais: uma para valores e outra para buckets de tempo correspondentes:

Heartbeat
| make-series count() default=0 on TimeGenerated in range(ago(1d), now(), 1h) by Category 

Esta é a saída:

Category count_ TimeGenerated
Agente direto [15,60,0,55,60,57,60...] ["2017-06-06T17:00:00.0000000Z","2017-06-06T18:00:00.0000000Z","2017-06-06T19:00:00.0000000Z","2017-06-06T20:00:00.0000000Z","2017-06-06T21:00:00.0000000Z",...]
... ... ...

O terceiro elemento da matriz count_ será 0, conforme esperado. A matriz TimeGenerated tem um carimbo de data/hora correspondente de "06/06/2017T19:00:00.0000000Z". No entanto, esse formato de matriz será difícil de ler. Use mv-expand para expandir as matrizes e produzem o mesmo formato de saída gerada pelo summarize:

Heartbeat
| make-series count() default=0 on TimeGenerated in range(ago(1d), now(), 1h) by Category 
| mv-expand TimeGenerated, count_
| project Category, TimeGenerated, count_

Esta é a saída:

Category TimeGenerated count_
Agente direto 2017-06-06T17:00:00Z 15
Agente direto 2017-06-06T18:00:00Z 60
Agente direto 2017-06-06T19:00:00Z 0
Agente direto 2017-06-06T20:00:00Z 55
Agente direto 2017-06-06T21:00:00Z 57
Agente direto 2017-06-06T22:00:00Z 60
... ... ...

Restringir resultados a um conjunto de elementos: let, makeset, toscalar e in

Um cenário comum é selecionar os nomes de algumas entidades específicas com base em um conjunto de critérios e filtrar um conjunto de dados diferente para esse conjunto de entidades. Por exemplo, será possível encontrar computadores conhecidos por terem atualizações ausentes e identificar os endereços IP para os quais eles fizeram chamadas.

Veja um exemplo:

let ComputersNeedingUpdate = toscalar(
    Update
    | summarize makeset(Computer)
    | project set_Computer
);
WindowsFirewall
| where Computer in (ComputersNeedingUpdate)

Junções

É possível usar junções para analisar dados de várias tabelas na mesma consulta. Uma junção mesclará as linhas de dois conjuntos de dados por valores correspondentes de colunas especificadas.

Veja um exemplo:

SecurityEvent 
| where EventID == 4624		// sign-in events
| project Computer, Account, TargetLogonId, LogonTime=TimeGenerated
| join kind= inner (
    SecurityEvent 
    | where EventID == 4634		// sign-out events
    | project TargetLogonId, LogoffTime=TimeGenerated
) on TargetLogonId
| extend Duration = LogoffTime-LogonTime
| project-away TargetLogonId1 
| top 10 by Duration desc

No exemplo, o primeiro conjunto de dados filtrará todos os eventos de entrada. Esse conjunto de dados será associado a outro que filtrará todos os eventos de saída. As colunas projetadas serão Computer, Account, TargetLogonId e TimeGenerated. Os conjuntos de dados serão correlacionados por uma coluna compartilhada, chamada TargetLogonId. A saída será um registro único por correlação com horários de entrada e saída.

Caso os dois conjuntos de dados tenham colunas com o mesmo nome, as colunas do conjunto de dados do lado direito receberão um número de índice. Neste exemplo, os resultados mostrarão TargetLogonId com valores da tabela do lado esquerdo e TargetLogonId1 com valores da tabela do lado direito. Nesse caso, a segunda coluna TargetLogonId1 foi removida usando o operador project-away.

Observação

Mantenha somente as colunas relevantes de conjuntos de dados associados usando o operador project para aprimorar o desempenho.

Use a seguinte sintaxe para unir dois conjuntos de dados em que uma chave associada tenha um nome diferente entre as duas tabelas:

Table1
| join ( Table2 ) 
on $left.key1 == $right.key2

Tabelas de pesquisa

Uma aplicação comum de junções é usar datatable para obter um mapeamento de valores estáticos. Usar datatable pode ajudar a tornar os resultados mais apresentáveis. Por exemplo, é possível aprimorar dados de um evento de segurança usando o nome do evento para cada ID de evento:

let DimTable = datatable(EventID:int, eventName:string)
  [
    4625, "Account activity",
    4688, "Process action",
    4634, "Account activity",
    4658, "The handle to an object was closed",
    4656, "A handle to an object was requested",
    4690, "An attempt was made to duplicate a handle to an object",
    4663, "An attempt was made to access an object",
    5061, "Cryptographic operation",
    5058, "Key file operation"
  ];
SecurityEvent
| join kind = inner
 DimTable on EventID
| summarize count() by eventName

Esta é a saída:

eventName count_
O identificador de um objeto foi fechado 290.995
O identificador de um objeto foi solicitado 154.157
Houve uma tentativa de duplicar o identificador de um objeto 144.305
Houve uma tentativa de acessar um objeto 123.669
Operação de criptografia 153.495
Operação de um arquivo chave 153.496

JSON e estruturas de dados

Os objetos aninhados contêm outros objetos em uma matriz ou um mapa de pares de valores-chave. Esses objetos são representados como cadeias de caracteres JSON. Esta seção descreverá de que modo é possível usar o JSON para recuperar dados e analisar objetos aninhados.

Trabalhar com cadeias de caracteres JSON

Use extractjson para acessar um elemento específico de JSON em um caminho conhecido. Essa função requer uma expressão de caminho que usará as seguintes convenções:

  • Use $ para fazer referência à pasta raiz.
  • Use o colchete ou a notação de ponto para se referir a índices e elementos, conforme ilustrado nos exemplos a seguir.

Use parênteses para índices e pontos para separar elementos:

let hosts_report='{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"location":"South_DC", "status":"stopped", "rate":3}]}';
print hosts_report
| extend status = extractjson("$.hosts[0].status", hosts_report)

Este exemplo é semelhante, porém usará somente a notação de colchetes:

let hosts_report='{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"location":"South_DC", "status":"stopped", "rate":3}]}';
print hosts_report 
| extend status = extractjson("$['hosts'][0]['status']", hosts_report)

É possível usar somente uma notação de ponto para um elemento:

let hosts_report=dynamic({"location":"North_DC", "status":"running", "rate":5});
print hosts_report 
| extend status = hosts_report.status

parsejson

É mais fácil acessar vários elementos em sua estrutura JSON como um objeto dinâmico. Use parsejson para converter dados de texto para um objeto dinâmico. Depois de converter o JSON em um tipo dinâmico, será possível usar funções adicionais para analisar os dados.

let hosts_object = parsejson('{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"location":"South_DC", "status":"stopped", "rate":3}]}');
print hosts_object 
| extend status0=hosts_object.hosts[0].status, rate1=hosts_object.hosts[1].rate

arraylength

Use arraylength para contar o número de elementos em uma matriz:

let hosts_object = parsejson('{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"location":"South_DC", "status":"stopped", "rate":3}]}');
print hosts_object 
| extend hosts_num=arraylength(hosts_object.hosts)

mv-expand

Use mv-expand para dividir as propriedades de um objeto em linhas separadas:

let hosts_object = parsejson('{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"location":"South_DC", "status":"stopped", "rate":3}]}');
print hosts_object 
| mv-expand hosts_object.hosts[0]

Uma captura de tela mostra hosts_0 com valores de localização, status e taxa.

buildschema

Use buildschema para obter o esquema que admite todos os valores de um objeto:

let hosts_object = parsejson('{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"location":"South_DC", "status":"stopped", "rate":3}]}');
print hosts_object 
| summarize buildschema(hosts_object)

O resultado será um esquema no formato JSON:

{
    "hosts":
    {
        "indexer":
        {
            "location": "string",
            "rate": "int",
            "status": "string"
        }
    }
}

O esquema descreverá nomes dos campos de objetos e tipos de dados correspondentes.

Os objetos aninhados poderão ter esquemas diferentes, do mesmo modo que o seguinte exemplo:

let hosts_object = parsejson('{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"status":"stopped", "rate":"3", "range":100}]}');
print hosts_object 
| summarize buildschema(hosts_object)

Gráficos

As seções a seguir fornecerão exemplos de como trabalhar com gráficos ao usar a Linguagem de Consulta do Azure Data Explorer.

Elaborar um gráfico com os resultados

Comece revisando o número de computadores por sistema operacional da última hora:

Heartbeat
| where TimeGenerated > ago(1h)
| summarize count(Computer) by OSType  

Por padrão, os resultados serão exibidos no formato de uma tabela:

Uma captura de tela que mostra os resultados da consulta de uma tabela.

Selecione as opções Gráfico e Pizza para visualizar os resultados e obter uma exibição mais adequada:

Uma captura de tela que mostra os resultados da consulta em um gráfico de pizza.

Timecharts

Mostre a média e os percentuais de 50% e 95% do tempo do processador em compartimentos de uma hora.

A consulta a seguir vai gerar várias séries. Nos resultados, será possível escolher qual série será exibida no gráfico de tempo.

Perf
| where TimeGenerated > ago(1d) 
| where CounterName == "% Processor Time" 
| summarize avg(CounterValue), percentiles(CounterValue, 50, 95)  by bin(TimeGenerated, 1h)

Selecione o linha opção de exibição de gráfico:

Uma captura de tela que mostra um gráfico de linhas de várias séries.

Linha de referência

Uma linha de referência poderá ajudar você a identificar de modo fácil se a métrica excedeu um limite específico. Estenda o conjunto de dados adicionando uma coluna constante para adicionar uma linha a um gráfico:

Perf
| where TimeGenerated > ago(1d) 
| where CounterName == "% Processor Time" 
| summarize avg(CounterValue), percentiles(CounterValue, 50, 95)  by bin(TimeGenerated, 1h)
| extend Threshold = 20

Uma captura de tela que mostra um gráfico de linhas de várias séries com uma linha de referência de limite.

Várias dimensões

Ter várias expressões na cláusula by de summarize criará diversas linhas nos resultados. Uma linha será criada para cada combinação de valores.

SecurityEvent
| where TimeGenerated > ago(1d)
| summarize count() by tostring(EventID), AccountType, bin(TimeGenerated, 1h)

Ao exibir os resultados no formato de um gráfico, ele usará a primeira coluna da cláusula by. O exemplo a seguir mostrará um gráfico de colunas empilhadas criado com o valor de EventID. As dimensões deverão ser do tipo string. Neste exemplo, o valor de EventID será convertido em string:

Uma captura de tela que mostra um gráfico de barras baseado na coluna EventID.

É possível alternar entre colunas selecionando uma seta suspensa para o nome da coluna:

Uma captura de tela que mostra um gráfico de barras baseado na coluna AccountType com um seletor de colunas visível.

Análise inteligente

Esta seção incluirá exemplos que usam funções analíticas inteligentes no Azure Log Analytics para analisar a atividade do usuário. É possível usar esses exemplos para analisar aplicativos próprios monitorados pelo Azure Application Insights. Ou usar conceitos dessas consultas para obter uma análise semelhante em outros dados.

Análise de coortes

A análise de coorte rastreia a atividade de grupos de usuários específicos, conhecidos como coortes. A análise de coorte tenta medir a apelação de um serviço, medindo a taxa de usuários que retornam. Os usuários são agrupados pelo tempo em que usaram o serviço pela primeira vez. Ao analisar coortes, espera-se encontrar uma diminuição na atividade durante os primeiros períodos acompanhados. Cada coorte é intitulado segundo a semana em que seus membros foram observados pela primeira vez.

O seguinte exemplo analisará o número de atividades que os usuários concluíram durante cinco semanas após o primeiro uso do serviço:

let startDate = startofweek(bin(datetime(2017-01-20T00:00:00Z), 1d));
let week = range Cohort from startDate to datetime(2017-03-01T00:00:00Z) step 7d;
// For each user, we find the first and last timestamp of activity
let FirstAndLastUserActivity = (end:datetime) 
{
    customEvents
    | where customDimensions["sourceapp"]=="ai-loganalyticsui-prod"
    // Check 30 days back to see first time activity.
    | where timestamp > startDate - 30d
    | where timestamp < end      
    | summarize min=min(timestamp), max=max(timestamp) by user_AuthenticatedId
};
let DistinctUsers = (cohortPeriod:datetime, evaluatePeriod:datetime) {
    toscalar (
    FirstAndLastUserActivity(evaluatePeriod)
    // Find members of the cohort: only users that were observed in this period for the first time.
    | where min >= cohortPeriod and min < cohortPeriod + 7d  
    // Pick only the members that were active during the evaluated period or after.
    | where max > evaluatePeriod - 7d
    | summarize dcount(user_AuthenticatedId)) 
};
week 
| where Cohort == startDate
// Finally, calculate the desired metric for each cohort. In this sample, we calculate distinct users but you can change
// this to any other metric that would measure the engagement of the cohort members.
| extend 
    r0 = DistinctUsers(startDate, startDate+7d),
    r1 = DistinctUsers(startDate, startDate+14d),
    r2 = DistinctUsers(startDate, startDate+21d),
    r3 = DistinctUsers(startDate, startDate+28d),
    r4 = DistinctUsers(startDate, startDate+35d)
| union (week | where Cohort == startDate + 7d 
| extend 
    r0 = DistinctUsers(startDate+7d, startDate+14d),
    r1 = DistinctUsers(startDate+7d, startDate+21d),
    r2 = DistinctUsers(startDate+7d, startDate+28d),
    r3 = DistinctUsers(startDate+7d, startDate+35d) )
| union (week | where Cohort == startDate + 14d 
| extend 
    r0 = DistinctUsers(startDate+14d, startDate+21d),
    r1 = DistinctUsers(startDate+14d, startDate+28d),
    r2 = DistinctUsers(startDate+14d, startDate+35d) )
| union (week | where Cohort == startDate + 21d 
| extend 
    r0 = DistinctUsers(startDate+21d, startDate+28d),
    r1 = DistinctUsers(startDate+21d, startDate+35d) ) 
| union (week | where Cohort == startDate + 28d 
| extend 
    r0 = DistinctUsers (startDate+28d, startDate+35d) )
// Calculate the retention percentage for each cohort by weeks
| project Cohort, r0, r1, r2, r3, r4,
          p0 = r0/r0*100,
          p1 = todouble(r1)/todouble (r0)*100,
          p2 = todouble(r2)/todouble(r0)*100,
          p3 = todouble(r3)/todouble(r0)*100,
          p4 = todouble(r4)/todouble(r0)*100 
| sort by Cohort asc

Esta é a saída:

Uma captura de tela que mostra uma tabela de coortes com base nas atividades.

Acumulando usuários ativos mensais e a adesão de usuários

O exemplo a seguir usará uma análise de série temporal com a função series_fir. É possível usar a função series_fir para obter computações de janelas deslizantes. O aplicativo de exemplo que está sendo monitorado é uma loja online que rastreia a atividade dos usuários por meio de eventos personalizados. A consulta rastreará dois tipos de atividades do usuário: AddToCart e Checkout. Ela definirá um usuário ativo como aquele que finalizar compra pelo menos uma vez em um dia específico.

let endtime = endofday(datetime(2017-03-01T00:00:00Z));
let window = 60d;
let starttime = endtime-window;
let interval = 1d;
let user_bins_to_analyze = 28;
// Create an array of filters coefficients for series_fir(). A list of '1' in our case will produce a simple sum.
let moving_sum_filter = toscalar(range x from 1 to user_bins_to_analyze step 1 | extend v=1 | summarize makelist(v)); 
// Level of engagement. Users will be counted as engaged if they completed at least this number of activities.
let min_activity = 1;
customEvents
| where timestamp > starttime  
| where customDimensions["sourceapp"] == "ai-loganalyticsui-prod"
// We want to analyze users who actually checked out in our website.
| where (name == "Checkout") and user_AuthenticatedId <> ""
// Create a series of activities per user.
| make-series UserClicks=count() default=0 on timestamp 
	in range(starttime, endtime-1s, interval) by user_AuthenticatedId
// Create a new column that contains a sliding sum. 
// Passing 'false' as the last parameter to series_fir() prevents normalization of the calculation by the size of the window.
// For each time bin in the *RollingUserClicks* column, the value is the aggregation of the user activities in the 
// 28 days that preceded the bin. For example, if a user was active once on 2016-12-31 and then inactive throughout 
// January, then the value will be 1 between 2016-12-31 -> 2017-01-28 and then 0s. 
| extend RollingUserClicks=series_fir(UserClicks, moving_sum_filter, false)
// Use the zip() operator to pack the timestamp with the user activities per time bin.
| project User_AuthenticatedId=user_AuthenticatedId , RollingUserClicksByDay=zip(timestamp, RollingUserClicks)
// Transpose the table and create a separate row for each combination of user and time bin (1 day).
| mv-expand RollingUserClicksByDay
| extend Timestamp=todatetime(RollingUserClicksByDay[0])
// Mark the users that qualify according to min_activity.
| extend RollingActiveUsersByDay=iff(toint(RollingUserClicksByDay[1]) >= min_activity, 1, 0)
// And finally, count the number of users per time bin.
| summarize sum(RollingActiveUsersByDay) by Timestamp
// First 28 days contain partial data, so we filter them out.
| where Timestamp > starttime + 28d
// Render as timechart.
| render timechart

Esta é a saída:

Uma captura de tela de um gráfico que mostra a reversão de usuários ativos por dia em um mês.

O exemplo a seguir transformará a consulta anterior em uma função reutilizável. Depois o exemplo usará a consulta para calcular a adesão continua de usuários. Um usuário ativo será definido nesta consulta como aquele que finalizar compra pelo menos uma vez em um dia específico.

let rollingDcount = (sliding_window_size: int, event_name:string)
{
    let endtime = endofday(datetime(2017-03-01T00:00:00Z));
    let window = 90d;
    let starttime = endtime-window;
    let interval = 1d;
    let moving_sum_filter = toscalar(range x from 1 to sliding_window_size step 1 | extend v=1| summarize makelist(v));    
    let min_activity = 1;
    customEvents
    | where timestamp > starttime
    | where customDimensions["sourceapp"]=="ai-loganalyticsui-prod"
    | where (name == event_name)
    | where user_AuthenticatedId <> ""
    | make-series UserClicks=count() default=0 on timestamp 
		in range(starttime, endtime-1s, interval) by user_AuthenticatedId
    | extend RollingUserClicks=fir(UserClicks, moving_sum_filter, false)
    | project User_AuthenticatedId=user_AuthenticatedId , RollingUserClicksByDay=zip(timestamp, RollingUserClicks)
    | mv-expand RollingUserClicksByDay
    | extend Timestamp=todatetime(RollingUserClicksByDay[0])
    | extend RollingActiveUsersByDay=iff(toint(RollingUserClicksByDay[1]) >= min_activity, 1, 0)
    | summarize sum(RollingActiveUsersByDay) by Timestamp
    | where Timestamp > starttime + 28d
};
// Use the moving_sum_filter with bin size of 28 to count MAU.
rollingDcount(28, "Checkout")
| join
(
    // Use the moving_sum_filter with bin size of 1 to count DAU.
    rollingDcount(1, "Checkout")
)
on Timestamp
| project sum_RollingActiveUsersByDay1 *1.0 / sum_RollingActiveUsersByDay, Timestamp
| render timechart

Esta é a saída:

Uma captura de tela de um gráfico que mostra a adesão do usuário ao longo do tempo.

Análise de regressão

Este exemplo demonstra como criar um detector automatizado para interrupções de serviço com base exclusivamente em logs de rastreamento do aplicativo. O detector busca aumentos repentinos e anormais na quantidade relativa de rastreamentos de erros e avisos no aplicativo.

Duas técnicas são usadas para avaliar o status do serviço com base nos dados de logs de rastreamento:

  • Use make-series para converter os logs de rastreamento de texto semiestruturados em uma métrica que representa a taxa entre as linhas de rastreamento positiva e negativa.
  • Use series_fit_2lines e series_fit_line para obter uma detecção avançada de etapas ignoradas usando uma análise de série temporal com a regressão linear de duas linhas.
let startDate = startofday(datetime("2017-02-01"));
let endDate = startofday(datetime("2017-02-07"));
let minRsquare = 0.8;  // Tune the sensitivity of the detection sensor. Values close to 1 indicate very low sensitivity.
// Count all Good (Verbose + Info) and Bad (Error + Fatal + Warning) traces, per day.
traces
| where timestamp > startDate and timestamp < endDate
| summarize 
    Verbose = countif(severityLevel == 0),
    Info = countif(severityLevel == 1), 
    Warning = countif(severityLevel == 2),
    Error = countif(severityLevel == 3),
    Fatal = countif(severityLevel == 4) by bin(timestamp, 1d)
| extend Bad = (Error + Fatal + Warning), Good = (Verbose + Info)
// Determine the ratio of bad traces, from the total.
| extend Ratio = (todouble(Bad) / todouble(Good + Bad))*10000
| project timestamp , Ratio
// Create a time series.
| make-series RatioSeries=any(Ratio) default=0 on timestamp in range(startDate , endDate -1d, 1d) by 'TraceSeverity' 
// Apply a 2-line regression to the time series.
| extend (RSquare2, SplitIdx, Variance2,RVariance2,LineFit2)=series_fit_2lines(RatioSeries)
// Find out if our 2-line is trending up or down.
| extend (Slope,Interception,RSquare,Variance,RVariance,LineFit)=series_fit_line(LineFit2)
// Check whether the line fit reaches the threshold, and if the spike represents an increase (rather than a decrease).
| project PatternMatch = iff(RSquare2 > minRsquare and Slope>0, "Spike detected", "No Match")

Próximas etapas