Forum Discussion

Copper Contributor

Oct 21, 2019

Solved

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

Oct 23, 2019

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)

CliveWatson

Former Employee

Oct 22, 2019

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

TheGreenGorilla

Copper Contributor

Oct 23, 2019

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.

CliveWatson

Former Employee

Oct 23, 2019

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)

TheGreenGorilla

Copper Contributor

Oct 24, 2019

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.