SOLVED

Identify workspace after a Union

Iron Contributor

I'm executing a query with a union on the Heartbeat table of two work spaces. I'd like to have an additional column that identifies which work space the result is from.

The query:

union isfuzzy= true workspace("thisworkspace").Heartbeat, workspace("thatworkspace").Heartbeat
| project TimeGenerated , Computer, ResourceId , OSType
| where TimeGenerated < now()
| where ResourceId == ""
| where OSType == "Windows" or OSType == "Linux"
| summarize arg_max(TimeGenerated, *) by Computer
| distinct Computer
 
I'd like to have results that look like this:

ServerABC   thisworkspace
ServerDEF   thatworkspace
ServerGHI   thatworkspace
Server123   thisworkspace

Is there a way to do this?

@Noa Kuperberg 
3 Replies

Hi,

I do not think there is operator for getting the workspace name or id but you can do something like this:

let Table1 = Heartbeat | extend workspace = 1 | limit 10;
let Table2 = Perf | extend workspace = 2 | limit 10;
union isfuzzy= true Table1, Table2

You can probably rely on column ManagementGroupName as well I think. The value of that column is usually AOI-<workspace id>.

best response confirmed by Scott Allison (Iron Contributor)
Solution

You can use, withsource, you might need to trim/parse the returned name as its in the format?

workspace : workspace('workspacea').Heartbeat

 

union withsource = workspace isfuzzy= true
workspace("a").Heartbeat,
workspace("b").Heartbeat
| project TimeGenerated , Computer, ResourceId , OSType, workspace
| where TimeGenerated < now()
| where ResourceId == ""
| where OSType == "Windows" or OSType == "Linux"
| summarize arg_max(TimeGenerated, *) by Computer, workspace
| distinct Computer, workspace
 

Forgot about "withsource"--that did the trick. Thanks!! :)

1 best response

Accepted Solutions
best response confirmed by Scott Allison (Iron Contributor)
Solution

You can use, withsource, you might need to trim/parse the returned name as its in the format?

workspace : workspace('workspacea').Heartbeat

 

union withsource = workspace isfuzzy= true
workspace("a").Heartbeat,
workspace("b").Heartbeat
| project TimeGenerated , Computer, ResourceId , OSType, workspace
| where TimeGenerated < now()
| where ResourceId == ""
| where OSType == "Windows" or OSType == "Linux"
| summarize arg_max(TimeGenerated, *) by Computer, workspace
| distinct Computer, workspace
 

View solution in original post