Forum Discussion
Is it possible to "pipe" the output of one query to another?
Hi,
Firstly excuse me if this is a silly question, I am new to Kusto and query languages in general.
Is it possible to "pipe" the results of one query and use it to query on with another?
What are trying to do is get the output from the Auditlogs and use an attribute from that to then feed in to a query on the SigninLogs;
let AddMember = (
AuditLogs
| where TimeGenerated > ago(2h)
| where OperationName == "Add member to group" and TargetResources contains "Our Group"
| project TimeGenerated, OperationName, UserName=TargetResources[0].userPrincipalName, GroupName=TargetResources[0].modifiedProperties[1].newValue
);
let AppSignIn = (
SigninLogs
| where TimeGenerated > ago(2h)
| where AppDisplayName == "Our App"
| where Status.errorCode == "0"
| project TimeGenerated, OperationName, UserName=UserPrincipalName, AppDisplayName
);
AddMember
| union AppSignIn
| sort by TimeGenerated asc
I am not familiar with all the operators but I understand that the Union operator creates a single table from both outputs... not quite what I am after because I'd like to use the output from one to feed in to the other.
Hopefully I have made sense and thanks.
More like this?
let AddMember = ( AuditLogs | where TimeGenerated > ago(2h) | where OperationName == "Add member to group" and TargetResources contains "Our Group" | project TimeGenerated, OperationName, UserName=TargetResources[0].userPrincipalName, GroupName=TargetResources[0].modifiedProperties[1].newValue, ResourceId ); SigninLogs | where TimeGenerated > ago(2h) | where AppDisplayName == "Our App" | where Status.errorCode == "0" | where UserPrincipalName in (AddMember) | project TimeGenerated, OperationName, UserName=UserPrincipalName, AppDisplayName, ResourceIdOr see my working example (using demo data), please click
Go to Log Analytics and Run Query
Get all computers that startswith a name of "Contoso" from the Heartbeat table and then only show Events for those? You can use !in for the reverse i.e. "not in"
let computerList = ( Heartbeat | where TimeGenerated > ago(24d) | where Computer startswith "Contoso" | distinct Computer | project Computer ); Event | where TimeGenerated > ago(24d) | where Computer in (computerList)
4 Replies
Are you looking for a join? Something like this?
let AddMember = ( AuditLogs | where TimeGenerated > ago(24h) | where OperationName == "Add member to group" and TargetResources contains "Our Group" | project TimeGenerated, OperationName, UserName=TargetResources[0].userPrincipalName, GroupName=TargetResources[0].modifiedProperties[1].newValue, ResourceId ); SigninLogs | where TimeGenerated > ago(2h) | where AppDisplayName == "Our App" | where Status.errorCode == "0" | project TimeGenerated, OperationName, UserName=UserPrincipalName, AppDisplayName, ResourceId | join ( AddMember ) on ResourceId | project AppDisplayName , OperationNameThanks for your reply CliveWatson
I have tried the Join operator, but doesnt that just join the columns from the 2 outputs?
I'd like the output from the AuditLogs to be the source/input in to the SignIn logs query. A bit like how you would pipe resultant objects from one PowerShell command to the next.
I guess I would somehow need to create a temporary table from the AuditLogs and use that as a source for the next query... I am new to Kusto (and query languages) so I may be a tough student.
More like this?
let AddMember = ( AuditLogs | where TimeGenerated > ago(2h) | where OperationName == "Add member to group" and TargetResources contains "Our Group" | project TimeGenerated, OperationName, UserName=TargetResources[0].userPrincipalName, GroupName=TargetResources[0].modifiedProperties[1].newValue, ResourceId ); SigninLogs | where TimeGenerated > ago(2h) | where AppDisplayName == "Our App" | where Status.errorCode == "0" | where UserPrincipalName in (AddMember) | project TimeGenerated, OperationName, UserName=UserPrincipalName, AppDisplayName, ResourceIdOr see my working example (using demo data), please click
Go to Log Analytics and Run Query
Get all computers that startswith a name of "Contoso" from the Heartbeat table and then only show Events for those? You can use !in for the reverse i.e. "not in"
let computerList = ( Heartbeat | where TimeGenerated > ago(24d) | where Computer startswith "Contoso" | distinct Computer | project Computer ); Event | where TimeGenerated > ago(24d) | where Computer in (computerList)
