10-11-2017 06:41 AM
10-11-2017 06:41 AM
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.
10-11-2017 11:02 PM
10-24-2017 02:03 AM
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.
10-24-2017 02:07 AM
10-25-2017 03:07 AM
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 )
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.
10-25-2017 03:17 AM
10-25-2017 03:31 AM
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
10-25-2017 03:42 AMSolution
11-02-2017 04:23 AM
I tried with the sample dataset, its working now. Also my older query is working now without any change.