Forum Discussion

OmriPinsker's avatar
OmriPinsker
Copper Contributor
Jul 20, 2020

Union on timechart

Hi CliveWatson 

when using the connectors timechart table, i have modified it to be alligned with our Logsources.

can you clarify clarify about the syntax and the unions and how they work

1. the range does not seem to have any effect on the query run time, is that only being used to populate the union ? 

2. why are there 3 unions used for (specifically the 2nd one)

3. why use union is fuzzy and not other operator such as

union withsource= TableName Table1, Table2

 

 

 

 

let Now = now();
//let Time = 7d;
(range TimeGenerated from ago(3d) to Now-1d step 1d
| extend Count = 0
| union isfuzzy=true (Table1
| where TimeGenerated >= ago(Time)
| summarize Count = count() by bin_at(TimeGenerated, 1d, Now))
| summarize Count=max(Count) by bin_at(TimeGenerated, 1d, Now)
| sort by TimeGenerated
| project Value = iff(isnull(Count), 0, Count), Time = TimeGenerated, Legend = "Table1")
| union isfuzzy = true(range TimeGenerated from ago(3d) to Now-1d step 1d
| extend Count = 0
| union isfuzzy=true (Table2_CL
| where TimeGenerated >= ago(Time)
| summarize Count = count() by bin_at(TimeGenerated, 1d, Now))
| summarize Count=max(Count) by bin_at(TimeGenerated, 1d, Now)
| sort by TimeGenerated
| project Value = iff(isnull(Count), 0, Count), Time = TimeGenerated, Legend = "Table2")
| render timechart
  • CliveWatson's avatar
    CliveWatson
    Icon for Microsoft rankMicrosoft
    Jul 20, 2020

    OmriPinsker 

     

    I don't recall this, do you have the post and what's the problem you are trying to solve?  There is maybe a better way now.

     

    1. The first RANGE is to set the days ago (i.e. go back 14days)

    2. isfuzzy is used to handle a missing table

    • CliveWatson's avatar
      CliveWatson
      Icon for Microsoft rankMicrosoft
      Jul 21, 2020

      OmriPinsker 

       

      If you need a different date range for each Table, then this maybe better: Go to Log Analytics and run query

      union  
    (
    Heartbeat
        // go back two days and get a bin for each day
        | where TimeGenerated > startofday(ago(2d))
        | summarize Count=count() by bin_at(TimeGenerated, 1d, now())
        | order by TimeGenerated
        | project Value = iff(isnull(Count), 0 , Count), Time = TimeGenerated, Legend = "Table1: Heartbeat" 
    ),
    (
    Perf
        // go back seven days and get a bin for each day
        | where TimeGenerated > startofday(ago(7d))
        | summarize Count=count() by bin_at(TimeGenerated, 1d, now())
        | order by TimeGenerated
        // perf is a high count so added a /1000 to reduce the scale - please remove
        | project Value = iff(isnull(Count), 0 , Count / 1000), Time = TimeGenerated, Legend = "Table2: Perf" 
    )
| render timechart

       

      Count and max(Count) would have been the same in the examples given, so I removed that line. 

       

       

       

      • OmriPinsker's avatar
        OmriPinsker
        Copper Contributor
        Jul 21, 2020

         

        CliveWatson 

        tnx,

        ended up doing something similar,

        FYI the initial query is used from the connector UI

         

Resources