Union on timechart

%3CLINGO-SUB%20id%3D%22lingo-sub-1533471%22%20slang%3D%22en-US%22%3EUnion%20on%20timechart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533471%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhen%20using%20the%20connectors%20timechart%20table%2C%20i%20have%20modified%20it%20to%20be%20alligned%20with%20our%20Logsources.%3C%2FP%3E%3CP%3Ecan%20you%20clarify%20clarify%20about%20the%20syntax%20and%20the%20unions%20and%20how%20they%20work%3C%2FP%3E%3CP%3E1.%20the%20range%20does%20not%20seem%20to%20have%20any%20effect%20on%20the%20query%20run%20time%2C%20is%20that%20only%20being%20used%20to%20populate%20the%20union%20%3F%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20why%20are%20there%203%20unions%20used%20for%20(specifically%20the%202nd%20one)%3C%2FP%3E%3CP%3E3.%20why%20use%20union%20is%20fuzzy%20and%20not%20other%20operator%20such%20as%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3E%20withsource%3D%20TableName%20Table1%2C%20Table2%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Elet%20Now%20%3D%20now()%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2F%2Flet%20Time%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%3Ed%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Erange%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20from%20ago(%3C%2FSPAN%3E%3CSPAN%3E3%3C%2FSPAN%3E%3CSPAN%3Ed)%20to%20Now-1d%20step%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Count%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3E%20isfuzzy%3D%3C%2FSPAN%3E%3CSPAN%3Etrue%3C%2FSPAN%3E%3CSPAN%3E%20(Table1%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%3D%20ago(Time)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20Count%20%3D%20%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20bin_at(TimeGenerated%2C%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed%2C%20Now))%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20Count%3Dmax(Count)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20bin_at(TimeGenerated%2C%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed%2C%20Now)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esort%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20Value%20%3D%20iff(isnull(Count)%2C%20%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%2C%20Count)%2C%20Time%20%3D%20TimeGenerated%2C%20Legend%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Table1%22%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3E%20isfuzzy%20%3D%20%3C%2FSPAN%3E%3CSPAN%3Etrue%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Erange%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20from%20ago(%3C%2FSPAN%3E%3CSPAN%3E3%3C%2FSPAN%3E%3CSPAN%3Ed)%20to%20Now-1d%20step%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Count%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3E%20isfuzzy%3D%3C%2FSPAN%3E%3CSPAN%3Etrue%3C%2FSPAN%3E%3CSPAN%3E%20(Table2_CL%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%3D%20ago(Time)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20Count%20%3D%20%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20bin_at(TimeGenerated%2C%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed%2C%20Now))%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20Count%3Dmax(Count)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20bin_at(TimeGenerated%2C%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed%2C%20Now)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esort%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20Value%20%3D%20iff(isnull(Count)%2C%20%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%2C%20Count)%2C%20Time%20%3D%20TimeGenerated%2C%20Legend%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Table2%22%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Erender%3C%2FSPAN%3E%3CSPAN%3E%20timechart%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1533471%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESentinel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533650%22%20slang%3D%22en-US%22%3ERe%3A%20Union%20on%20timechart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533650%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F501739%22%20target%3D%22_blank%22%3E%40MIkushOmri%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20recall%20this%2C%20do%20you%20have%20the%20post%20and%20what's%20the%20problem%20you%20are%20trying%20to%20solve%3F%26nbsp%3B%20There%20is%20maybe%20a%20better%20way%20now.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.%20The%20first%20RANGE%20is%20to%20set%20the%20days%20ago%20(i.e.%20go%20back%2014days)%3C%2FP%3E%0A%3CP%3E2.%20isfuzzy%20is%20used%20to%20handle%20a%20missing%20table%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1534966%22%20slang%3D%22en-US%22%3ERe%3A%20Union%20on%20timechart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1534966%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F501739%22%20target%3D%22_blank%22%3E%40MIkushOmri%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20need%20a%20different%20date%20range%20for%20each%20Table%2C%20then%20this%20maybe%20better%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA8VTsU7DMBTcK%25252FUfTp0cKShtF6RKZWGAgYGhYkUv8UtiSOzKdlqK%25252BvHYbaENZQCEhK0kjvzene9O7rQyGhgOEIbYf26ZrM%25252BZ%25252FP43jixDZZBT8Qy%25252FNpC0cSAtUbEHIVcapbFgKuq4d%25252BzbYl2zZSxUyzes2ZJniSs4HyhMGWoFVUZMZZKcNrmubcmqV8a16bSfF%25252FEtEuSbyPVIXvQAU0zCo81a9GGMlWxjU6%25252F6tGJpzRMXHg%25252FUdIw5VFkK5XTXNGLHnKQYI8X7OuKEqk%25252Fkd1xx8GKO0YLyhiezo4Ojg7NJ2nP4nm35pbmOV6z%25252F3N7L%25252F7E36FoGoVBBDGpV1dgRwRmQlOGghGwyHo%25252FhDSzLrmD4muEKahgXWDZMjsNOa1b829CQIVJ8N7vpbJfNR2zDwTYcQEedPjQWdfB1OIjz5JKcGRrgfmTpeSItvRy0fAfhDW9dTLnFAwAA%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Eunion%20%20%0A%20%20%20%20(%0A%20%20%20%20Heartbeat%0A%20%20%20%20%20%20%20%20%2F%2F%20go%20back%20two%20days%20and%20get%20a%20bin%20for%20each%20day%0A%20%20%20%20%20%20%20%20%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(2d))%0A%20%20%20%20%20%20%20%20%7C%20summarize%20Count%3Dcount()%20by%20bin_at(TimeGenerated%2C%201d%2C%20now())%0A%20%20%20%20%20%20%20%20%7C%20order%20by%20TimeGenerated%0A%20%20%20%20%20%20%20%20%7C%20project%20Value%20%3D%20iff(isnull(Count)%2C%200%20%2C%20Count)%2C%20Time%20%3D%20TimeGenerated%2C%20Legend%20%3D%20%22Table1%3A%20Heartbeat%22%20%0A%20%20%20%20)%2C%0A%20%20%20%20(%0A%20%20%20%20Perf%0A%20%20%20%20%20%20%20%20%2F%2F%20go%20back%20seven%20days%20and%20get%20a%20bin%20for%20each%20day%0A%20%20%20%20%20%20%20%20%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(7d))%0A%20%20%20%20%20%20%20%20%7C%20summarize%20Count%3Dcount()%20by%20bin_at(TimeGenerated%2C%201d%2C%20now())%0A%20%20%20%20%20%20%20%20%7C%20order%20by%20TimeGenerated%0A%20%20%20%20%20%20%20%20%2F%2F%20perf%20is%20a%20high%20count%20so%20added%20a%20%2F1000%20to%20reduce%20the%20scale%20-%20please%20remove%0A%20%20%20%20%20%20%20%20%7C%20project%20Value%20%3D%20iff(isnull(Count)%2C%200%20%2C%20Count%20%2F%201000)%2C%20Time%20%3D%20TimeGenerated%2C%20Legend%20%3D%20%22Table2%3A%20Perf%22%20%0A%20%20%20%20)%0A%7C%20render%20timechart%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECount%20and%20max(Count)%20would%20have%20been%20the%20same%20in%20the%20examples%20given%2C%20so%20I%20removed%20that%20line.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1535219%22%20slang%3D%22en-US%22%3ERe%3A%20Union%20on%20timechart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1535219%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etnx%2C%3C%2FP%3E%3CP%3Eended%20up%20doing%20something%20similar%2C%3C%2FP%3E%3CP%3EFYI%20the%20initial%20query%20is%20used%20from%20the%20connector%20UI%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MIkushOmri_1-1595325637761.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206780iBF2663514DEBA7BC%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22MIkushOmri_1-1595325637761.png%22%20alt%3D%22MIkushOmri_1-1595325637761.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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
3 Replies

@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

@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. 

 

 

 

 

@CliveWatson 

tnx,

ended up doing something similar,

FYI the initial query is used from the connector UI

MIkushOmri_1-1595325637761.png