Forum Discussion

ScottAllison's avatar
ScottAllison
Iron Contributor
Feb 27, 2019

Identify workspace after a Union

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 
  • 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
     
  • 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
     
    • ScottAllison's avatar
      ScottAllison
      Iron Contributor

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

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

Resources