Is it possible to "pipe" the output of one query to another?

[TheGreenGorilla]

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.

[#2]

@TheGreenGorilla 

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 , OperationName 

[#3]

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

[#4 · Accepted solution]

@TheGreenGorilla 

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, ResourceId

Or 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)

[#5]

@CliveWatson 

Yes! The IN operator has done the trick and have added to my vocabulary. I had to make a small adjustment to the first Project operator to produce the results 

let AddMember = (
AuditLogs
| where TimeGenerated > ago(2h)
| where OperationName == "Add member to group" and TargetResources contains "Our Group"
| project UserName=TargetResources[0].userPrincipalName
);
SigninLogs
| where TimeGenerated > ago(2h)
| where AppDisplayName == "Our App"
| where Status.errorCode == "0"
| where UserPrincipalName in (AddMember)
| project TimeGenerated, OperationName, UserName=UserPrincipalName, AppDisplayName, ResourceId

 Thanks for taking the time to offer a solution.