SOLVED

Identify workspace after a Union

%3CLINGO-SUB%20id%3D%22lingo-sub-357852%22%20slang%3D%22en-US%22%3EIdentify%20workspace%20after%20a%20Union%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357852%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20executing%20a%20query%20with%20a%20union%20on%20the%20Heartbeat%20table%20of%20two%20work%20spaces.%20I'd%20like%20to%20have%20an%20additional%20column%20that%20identifies%20which%20work%20space%20the%20result%20is%20from.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20query%3A%3C%2FP%3E%3CDIV%3E%3CBLOCKQUOTE%3E%3CDIV%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3Eisfuzzy%3D%20%3C%2FSPAN%3E%3CSPAN%3Etrue%3C%2FSPAN%3E%3CSPAN%3Eworkspace%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%22thisworkspace%22%3C%2FSPAN%3E%3CSPAN%3E).%3C%2FSPAN%3E%3CSPAN%3EHeartbeat%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3Eworkspace%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%22thatworkspace%22%3C%2FSPAN%3E%3CSPAN%3E).%3C%2FSPAN%3E%3CSPAN%3EHeartbeat%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3ETimeGenerated%20%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3EComputer%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3EResourceId%20%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3EOSType%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3ETimeGenerated%20%26lt%3B%20now%3C%2FSPAN%3E%3CSPAN%3E()%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3EResourceId%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3EOSType%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Windows%22%3C%2FSPAN%3E%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3EOSType%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Linux%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3Earg_max%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ETimeGenerated%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E*%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3EComputer%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Edistinct%3C%2FSPAN%3E%3CSPAN%3EComputer%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FBLOCKQUOTE%3E%3CDIV%3E%3CSPAN%3EI'd%20like%20to%20have%20results%20that%20look%20like%20this%3A%3CBR%20%2F%3E%3CBR%20%2F%3EServerABC%26nbsp%3B%20%26nbsp%3Bthisworkspace%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EServerDEF%26nbsp%3B%20%26nbsp%3Bthatworkspace%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EServerGHI%26nbsp%3B%20%26nbsp%3Bthatworkspace%3CBR%20%2F%3EServer123%26nbsp%3B%20%26nbsp%3Bthisworkspace%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20way%20to%20do%20this%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F54923%22%20target%3D%22_blank%22%3E%40Noa%20Kuperberg%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-357852%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-360605%22%20slang%3D%22en-US%22%3ERe%3A%20Identify%20workspace%20after%20a%20Union%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-360605%22%20slang%3D%22en-US%22%3E%3CP%3EForgot%20about%20%22withsource%22--that%20did%20the%20trick.%20Thanks!!%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358374%22%20slang%3D%22en-US%22%3ERe%3A%20Identify%20workspace%20after%20a%20Union%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358374%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20use%2C%20withsource%2C%20you%20might%20need%20to%20trim%2Fparse%20the%20returned%20name%20as%20its%20in%20the%20format%3F%3C%2FP%3E%0A%3CP%3Eworkspace%20%3A%26nbsp%3B%3CSPAN%3Eworkspace('workspacea').Heartbeat%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3Ewithsource%20%3D%20workspace%20isfuzzy%3D%20%3C%2FSPAN%3E%3CSPAN%3Etrue%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3Eworkspace(%3C%2FSPAN%3E%3CSPAN%3E%22a%22%3C%2FSPAN%3E%3CSPAN%3E).Heartbeat%2C%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3Eworkspace(%3C%2FSPAN%3E%3CSPAN%3E%22b%22%3C%2FSPAN%3E%3CSPAN%3E).Heartbeat%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3ETimeGenerated%20%2C%20Computer%2C%20ResourceId%20%2C%20OSType%2C%20workspace%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3ETimeGenerated%20%26lt%3B%20now()%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3EResourceId%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3EOSType%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Windows%22%3C%2FSPAN%3E%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3EOSType%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Linux%22%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3Earg_max(TimeGenerated%2C%20*)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3EComputer%2C%20workspace%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Edistinct%3C%2FSPAN%3E%3CSPAN%3EComputer%2C%20workspace%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358079%22%20slang%3D%22en-US%22%3ERe%3A%20Identify%20workspace%20after%20a%20Union%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358079%22%20slang%3D%22en-US%22%3E%3CP%20style%3D%22text-align%3A%20left%3B%22%3EHi%2C%3C%2FP%3E%0A%3CP%20style%3D%22text-align%3A%20left%3B%22%3EI%20do%20not%20think%20there%20is%20operator%20for%20getting%20the%20workspace%20name%20or%20id%20but%20you%20can%20do%20something%20like%20this%3A%3C%2FP%3E%0A%3CPRE%3Elet%20Table1%20%3D%20Heartbeat%20%7C%20extend%20workspace%20%3D%201%20%7C%20limit%2010%3B%0Alet%20Table2%20%3D%20Perf%20%7C%20extend%20workspace%20%3D%202%20%7C%20limit%2010%3B%0Aunion%20isfuzzy%3D%20true%20Table1%2C%20Table2%3C%2FPRE%3E%0A%3CP%20style%3D%22text-align%3A%20left%3B%22%3EYou%20can%20probably%20rely%20on%20column%20ManagementGroupName%20as%20well%20I%20think.%20The%20value%20of%20that%20column%20is%20usually%20AOI-%3CWORKSPACE%20id%3D%22%22%3E.%3C%2FWORKSPACE%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent 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
Highlighted

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

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

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