SOLVED

Cross Workspace Analytic Rule Performance

%3CLINGO-SUB%20id%3D%22lingo-sub-1574844%22%20slang%3D%22en-US%22%3ECross%20Workspace%20Analytic%20Rule%20Performance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1574844%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20several%20workspaces%20containing%20SecurityEvent%20table%20having%20data.%20When%20I%20perform%20a%20union%20on%20the%20workspaces%20and%20then%20do%20the%20filtering%2C%20calculation%2C%20etc.%2C%20the%20query%20consumes%20too%20much%20resources%20(which%20makes%20sense).%20example%20query%3A%3C%2FP%3E%3CP%3Eunion%20(workspace1.SecurityEvent%2C%20workspace2.SecurityEvent%2C%20workspace3.SecurityEvent)%3C%2FP%3E%3CP%3E%7C%20where%20EventID%20%3D%3D%20%224624%22%3C%2FP%3E%3CP%3E%7C%20where%20%3COTHER%20conditions%3D%22%22%3E.%26nbsp%3B%3C%2FOTHER%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20reduce%20the%20resource%20usage%2C%20I%20do%20a%20basic%20filtering%20before%20the%20union%20like%20below%3A%3C%2FP%3E%3CP%3Elet%20workspace1_Events%20%3D%20workspace1.SecurityEvent%20%7C%20where%20EventID%3D%3D%224624%22%3B%3C%2FP%3E%3CP%3Elet%20workspace2_Events%20%3D%20workspace2.SecurityEvent%20%7C%20where%20EventID%3D%3D%224624%22%3B%3C%2FP%3E%3CP%3Elet%20workspace3_Events%20%3D%20workspace3.SecurityEvent%20%7C%20where%20EventID%3D%3D%224624%22%3B%3C%2FP%3E%3CP%3Eunion%20(workspace1_Events%2C%20workspace2_Events%2C%20workspace3_Events)%3C%2FP%3E%3CP%3E%7C%20where%20%3COTHER%20conditions%3D%22%22%3E%3C%2FOTHER%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20running%20a%20query%20%2C%20I%20can%20use%20%22search%20in%22%20and%20it%20is%20more%20efficient%20than%20%22union%22.%20However%2C%20I%20can't%20use%20this%20operator%20in%20the%20rule.%20Is%20there%20a%20way%20to%20write%20crossworkspace%20rule%20queries%20without%20having%20to%20duplicate%20the%20query%20for%20each%20workspace%20to%20make%20it%20more%20efficient%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576396%22%20slang%3D%22en-US%22%3ERe%3A%20Cross%20Workspace%20Analytic%20Rule%20Performance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576396%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F666494%22%20target%3D%22_blank%22%3E%40Cyb3rMonk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Efirst%2C%20search%20should%20not%20be%20more%20performant%20than%20union%2C%20so%20the%20difference%20may%20be%20elsewhere.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20to%20your%20question%3A%20I%20can't%20think%20of%20a%20way%20to%20avoid%20repeating%20the%20query%2C%20however%2C%20I%20can%20make%20it%20simpler%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CPRE%3E%3CSPAN%3Elet%20Filter%20%3D%20(T%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E(EventID%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Eint%3C%2FSPAN%3E%3CSPAN%3E))%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3E%7B%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3E%20T%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20EventID%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E4624%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3CSPAN%3E%3B%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3E%20(workspace(%3C%2FSPAN%3E%3CSPAN%3E%22a%22%3C%2FSPAN%3E%3CSPAN%3E).SecurityEvent%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Einvoke%3C%2FSPAN%3E%3CSPAN%3E%20Filter())%2C(workspace(%3C%2FSPAN%3E%3CSPAN%3E%22b%22%3C%2FSPAN%3E%3CSPAN%3E).SecurityEvent%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Einvoke%3C%2FSPAN%3E%3CSPAN%3E%20Filter())%20%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhile%20for%20a%20single%20condition%20it%20might%20not%20be%20much%20shorter%2C%20it%20is%20still%20more%20elegant%2C%20and%20will%20become%20much%20shorter%20if%20you%20have%20more%20conditions.%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576489%22%20slang%3D%22en-US%22%3ERe%3A%20Cross%20Workspace%20Analytic%20Rule%20Performance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576489%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F293879%22%20target%3D%22_blank%22%3E%40Ofer_Shezaf%3C%2FA%3E%26nbsp%3BI've%20asked%20the%20difference%20between%20%22search%20in%22%20and%20%22union%22%20in%20one%20of%20the%20MTP%20webinar%20and%20I%20was%20told%20that%20%22search%20%3CSTRONG%3Ein%3C%2FSTRONG%3E%22%20was%20more%20efficient%20(not%20the%20%22search%22%20itself).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20suggestion.%20Is%20it%20possible%20to%20create%20complex%20filters%20having%20where%2C%20extend%2C%20summarize%2C%20and%20other%20operators%20with%20the%20method%20you%20suggested%3F%20Where%20can%20I%20find%20documentation%20about%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576570%22%20slang%3D%22en-US%22%3ERe%3A%20Cross%20Workspace%20Analytic%20Rule%20Performance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576570%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F666494%22%20target%3D%22_blank%22%3E%40Cyb3rMonk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%22search%20in%22%20vs.%20%22union%22%3A%20if%20you%20search%20in%20a%20small%20subset%20of%20tables%20and%20do%20%22union%20*%22%2C%20then%20you%20might%20be%20faster.%20In%20general%2C%20search%20(and%20search%20in)%20is%20much%20slower%3A%20it%20usually%20implies%20%22contains%22%20across%20all%20fields%20which%20is%20the%20most%20expensive%20query.%20With%20union%20you%20can%20use%20any%20operator%20and%20more%20directly%20limit%20to%20fields.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWell%2C%20you%20can%20do%26nbsp%3B%3CSTRONG%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3Esearch%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E%20in%20(SecurityAlert%2C%20SecurityEvent)%20_ResourceId%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E%3A%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E%3CSTRONG%3E%22127.0.0.1%3C%2FSTRONG%3E%22%2C%20but%20then%20it%20would%20be%20exactly%20like%20a%20simlar%20union.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20to%20my%20example%3A%20do%20whatever%20you%20like%20in%20the%20function.%20Just%20remember%20to%20declare%20any%20field%20from%20the%20source%20table%20you%20will%20reference.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576654%22%20slang%3D%22en-US%22%3ERe%3A%20Cross%20Workspace%20Analytic%20Rule%20Performance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576654%22%20slang%3D%22en-US%22%3EThanks%20Ofer!%20Creating%20a%20function%20and%20doint%20an%20iteration%20looks%20very%20useful.%3C%2FLINGO-BODY%3E
Contributor

Hi,

 

I have several workspaces containing SecurityEvent table having data. When I perform a union on the workspaces and then do the filtering, calculation, etc., the query consumes too much resources (which makes sense). example query:

union (workspace1.SecurityEvent, workspace2.SecurityEvent, workspace3.SecurityEvent)

| where EventID == "4624"

| where <other conditions>. 

 

To reduce the resource usage, I do a basic filtering before the union like below:

let workspace1_Events = workspace1.SecurityEvent | where EventID=="4624";

let workspace2_Events = workspace2.SecurityEvent | where EventID=="4624";

let workspace3_Events = workspace3.SecurityEvent | where EventID=="4624";

union (workspace1_Events, workspace2_Events, workspace3_Events)

| where <other conditions>

 

When running a query , I can use "search in" and it is more efficient than "union". However, I can't use this operator in the rule. Is there a way to write crossworkspace rule queries without having to duplicate the query for each workspace to make it more efficient? 

4 Replies
Best Response confirmed by Cyb3rMonk (Contributor)
Solution

@Cyb3rMonk 

 

first, search should not be more performant than union, so the difference may be elsewhere. 

 

As to your question: I can't think of a way to avoid repeating the query, however, I can make it simpler:

 

let Filter = (T:(EventID:int))
{
T | where EventID == 4624
};
union (workspace("a").SecurityEvent | invoke Filter()),(workspace("b").SecurityEvent | invoke Filter())

 

While for a single condition it might not be much shorter, it is still more elegant, and will become much shorter if you have more conditions.

@Ofer_Shezaf I've asked the difference between "search in" and "union" in one of the MTP webinar and I was told that "search in" was more efficient (not the "search" itself). 

 

Thanks for the suggestion. Is it possible to create complex filters having where, extend, summarize, and other operators with the method you suggested? Where can I find documentation about this?

@Cyb3rMonk 

 

"search in" vs. "union": if you search in a small subset of tables and do "union *", then you might be faster. In general, search (and search in) is much slower: it usually implies "contains" across all fields which is the most expensive query. With union you can use any operator and more directly limit to fields.  

 

Well, you can do search in (SecurityAlert, SecurityEvent) _ResourceId:"127.0.0.1", but then it would be exactly like a simlar union.

 

As to my example: do whatever you like in the function. Just remember to declare any field from the source table you will reference. 

Thanks Ofer! Creating a function and doint an iteration looks very useful.