SOLVED

Master table to join with other table without applying the TimeGenerated field

Copper Contributor

I have logs with multiple tables which has customerID in all the tables. Now I need to join the CustomerId with master table (Customer table) so that I can display the result with Customer Name by joining both tables. I was able to create new table Customer and join with the logs (Table).

 

The problem is if the Customer table is uploaded let say 01/Oct/2017 and if I select the date range in the filter for few hours then it is not matching any record since the Customer records are older than the filtered date.  So need somekind of master list which should not have any filter on the TimeGenerated field.  

Any suggession please.

 

8 Replies
Hi, Instead of using the filter from the UI why not filter on the time range directly in your queries. The time range in your queries will override the one chosen from the UI,

Thanks Stanislav. 

 

I tried within the queries itself but it is not working.  When I try joining (left outer) two tables and if I add where condition for both tables with different date range then it is picking the last where condition and applying same date range for both tables. 

 

For customer table: where TimeGenerated > ago(160d) 

For the log table : where TimeGenerated > ago(1d) 

 

Customer table was inserted more than 15 days a go.

 

 

Not sure if I get how you join these tables. Can you describe on which fields you are joining these tables and also what actually you want the end result (table to look like). You can try to give me some mock up data for table 1 and table 2 and how the end result of those two should look like.

Here is the query

 

Job_CL | where TimeGenerated > ago(1d)  | summarize Count = count() by CustomerId_s    
| join kind=leftouter (Customer_CL | where TimeGenerated > ago(160d) | summarize arg_max(TimeGenerated, *) by CustomerId_s  )
 on CustomerId_s

 

Customer_CL has few weeks older data.  And I want to see the Job_CL data with CustomerName.  If I make both 160 days then I am getting customerName.

Can you try using let and see if that works. Something like this: let Customers = Customer_CL | where TimeGenerated > ago(160d) | summarize arg_max(TimeGenerated, *) by CustomerId_s; Job_CL | where TimeGenerated > ago(1d) | summarize Count = count() by CustomerId_s | join kind=leftouter ( Customers ) on CustomerId_s

Thanks Stanislav for your support.

 

I tried before and didn't worked.  Also tried like below, ie with different field, didn't work.

 

 let Customers = Customer_CL  | summarize arg_max(TimeGenerated, *), mdate = max(TimeGenerated) by CustomerId_s | where mdate > ago(160d);
Job_CL | where TimeGenerated > ago(1d) | summarize Count = count() by CustomerId_s | join kind=leftouter ( Customers ) on CustomerId_s

best response confirmed by Sridhar Manickavel (Copper Contributor)
Solution
To my opinion it should work . I've tested with some data on my own and when they have different timeframes different results are produced so it is dividing the time correctly. Can you do a mockup of a few records that you have for both tables and how that mockup should look to what you are trying to achieve and how the mockup will look when using the syntax above?

Thanks Stanislav.

 

I tried with the sample dataset, its working now.  Also my older query is working now without any change. 

1 best response

Accepted Solutions
best response confirmed by Sridhar Manickavel (Copper Contributor)
Solution
To my opinion it should work . I've tested with some data on my own and when they have different timeframes different results are produced so it is dividing the time correctly. Can you do a mockup of a few records that you have for both tables and how that mockup should look to what you are trying to achieve and how the mockup will look when using the syntax above?

View solution in original post