how can I do in KQL to compare if a value contains another value in the same table in KQL?

%3CLINGO-SUB%20id%3D%22lingo-sub-3325984%22%20slang%3D%22en-US%22%3Ehow%20can%20I%20do%20in%20KQL%20to%20compare%20if%20a%20value%20contains%20another%20value%20in%20the%20same%20table%20in%20KQL%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3325984%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20the%20default%20rule%20%22SharePointFileOperation%20across%20devices%20with%20previously%20unseen%20user%20agents%22%20and%20I%20need%20to%20enhance%20the%20rule%20so%20that%20if%20the%20%22USER_ID%22%20column%20contains%20a%20variable%20I%20created.%20This%20variable%20performs%20a%20replacement%20of%20the%20above%20name%20with%20regex%20and%20removes%20the%20values%20beyond%20the%20%22%40%22.%20I%20have%20tried%20to%20make%20a%20%22contain%22%20expression%20but%20it%20doesn't%20work...is%20there%20another%20way...%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20threshold%20%3D%205%3B%0Alet%20szSharePointFileOperation%20%3D%20%22SharePointFileOperation%22%3B%0Alet%20szOperations%20%3D%20dynamic(%5B%22FileDownloaded%22%2C%20%22FileUploaded%22%5D)%3B%0Alet%20starttime%20%3D%2014d%3B%0Alet%20endtime%20%3D%201d%3B%0Alet%20historicalActivity%20%3D%0A%20%20%20%20OfficeActivity%0A%20%20%20%20%7C%20where%20TimeGenerated%20between(ago(starttime)%20..%20ago(endtime))%0A%20%20%20%20%7C%20where%20RecordType%20%3D~%20szSharePointFileOperation%0A%20%20%20%20%7C%20where%20Operation%20in~%20(szOperations)%0A%20%20%20%20%7C%20where%20isnotempty(UserAgent)%0A%20%20%20%20%7C%20summarize%20historicalCount%20%3D%20count()%20by%20UserAgent%2C%20RecordType%2C%20Operation%3B%0Alet%20ReplacedData%20%3D%0A%20%20%20%20OfficeActivity%0A%20%20%20%20%7C%20where%20TimeGenerated%20between(ago(starttime)%20..%20ago(endtime))%0A%20%20%20%20%7C%20where%20RecordType%20%3D~%20szSharePointFileOperation%0A%20%20%20%20%7C%20where%20Operation%20in~%20(szOperations)%0A%20%20%20%20%7C%20extend%20replaced%3Dreplace_regex(UserId%2C%20%40'(%5B%5E%5C%5C%5D*)%40gmail.com'%2C%20%40'%5C1')%0A%20%20%20%20%7C%20project%20replaced%3B%0Alet%20recentActivity%20%3D%20OfficeActivity%0A%20%20%20%20%7C%20where%20RecordType%20%3D~%20szSharePointFileOperation%0A%20%20%20%20%7C%20where%20Operation%20in~%20(szOperations)%0A%20%20%20%20%7C%20where%20TimeGenerated%20%26gt%3B%20ago(endtime)%0A%20%20%20%20%7C%20where%20isnotempty(UserAgent)%0A%20%20%20%20%7C%20summarize%20min(Start_Time)%2C%20max(Start_Time)%2C%20recentCount%20%3D%20count()%20by%20UserAgent%2C%20RecordType%2C%20Operation%3B%0Alet%20RareUserAgent%20%3D%20recentActivity%0A%20%20%20%20%7C%20join%20kind%20%3D%20leftanti%20(historicalActivity)%20on%20UserAgent%0A%20%20%20%20%7C%20order%20by%20recentCount%20desc%2C%20UserAgent%0A%20%20%20%20%2F%2F%20More%20than%205%20downloads%2Fuploads%20from%20a%20new%20user%20agent%20today%0A%20%20%20%20%7C%20where%20recentCount%20%26gt%3B%20threshold%3B%0A%2F%2FOfficeActivity%20%0AOfficeActivity%20%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(endtime)%20%0A%7C%20where%20RecordType%20%3D~%20szSharePointFileOperation%20%0A%7C%20where%20Operation%20in~%20(szOperations)%0A%7C%20where%20isnotempty(UserAgent)%0A%7C%20where%20Site_Url%20contains%20(ReplacedData)%0A%7C%20join%20kind%3D%20inner%20(RareUserAgent)%0A%20%20%20%20on%20UserAgent%2C%20RecordType%2C%20Operation%20%20%20%20%0A%7C%20where%20Start_Time%20between(min_Start_Time%20..%20max_Start_Time)%0A%7C%20summarize%20StartTimeUtc%20%3D%20min(min_Start_Time)%2C%20EndTimeUtc%20%3D%20max(max_Start_Time)%0A%20%20%20%20by%0A%20%20%20%20RecordType%2C%0A%20%20%20%20Operation%2C%0A%20%20%20%20UserAgent%2C%0A%20%20%20%20UserType%2C%0A%20%20%20%20UserId%2C%0A%20%20%20%20ClientIP%2C%0A%20%20%20%20OfficeWorkload%2C%0A%20%20%20%20Site_Url%2C%0A%20%20%20%20OfficeObjectId%2C%0A%20%20%20%20UserAgentSeenCount%20%3D%20recentCount%0A%7C%20extend%0A%20%20%20%20timestamp%20%3D%20StartTimeUtc%2C%0A%20%20%20%20AccountCustomEntity%20%3D%20UserId%2C%0A%20%20%20%20IPCustomEntity%20%3D%20ClientIP%2C%0A%20%20%20%20URLCustomEntity%20%3D%20Site_Url%0A%7C%20extend%20replaced%3Dreplace_regex(UserId%2C%20%40'(%5B%5E%5C%5C%5D*)%40gmail.com'%2C%20%40'%5C1')%0A%7C%20order%20by%20UserAgentSeenCount%20desc%2C%20UserAgent%20asc%2C%20Operation%20asc%2C%20UserId%20asc%2C%20replaced%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20how%20to%20get%20this%20query%20to%20work%20correctly.%20Or%20another%20way%20to%20come%20up%20with%20a%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3ERegards.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3325984%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDetection%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKusto%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESIEM%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3330364%22%20slang%3D%22en-US%22%3ERe%3A%20how%20can%20I%20do%20in%20KQL%20to%20compare%20if%20a%20value%20contains%20another%20value%20in%20the%20same%20table%20in%20KQL%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3330364%22%20slang%3D%22en-US%22%3E%7C%20where%20Site_Url%20in%20(ReplacedData)%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20should%20use%20%22in%22%20rather%20than%20%22contains%22%20for%20the%20above%20line%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3330854%22%20slang%3D%22en-US%22%3ERe%3A%20how%20can%20I%20do%20in%20KQL%20to%20compare%20if%20a%20value%20contains%20another%20value%20in%20the%20same%20table%20in%20KQL%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3330854%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1210469%22%20target%3D%22_blank%22%3E%40Clive_Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20the%20problem%20is%20that%20I%20would%20need%20that%20the%20string%20is%20not%20equal%20but%20that%20it%20is%20with%20the%20sentence%20%22contains%22%20or%20similar%2C%20because%20when%20using%20%22IN%22%20I%20am%20indicating%20that%20the%20parameter%20is%20equal%20and%20I%20don't%20want%20it%20to%20be%20equal%20but%20to%20contain%20it%2C%20because%20all%20the%20parameters%20are%20not%20going%20to%20be%20equal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi everyone,

 

I used the default rule "SharePointFileOperation across devices with previously unseen user agents" and I need to enhance the rule so that if the "USER_ID" column contains a variable I created. This variable performs a replacement of the above name with regex and removes the values beyond the "@". I have tried to make a "contain" expression but it doesn't work...is there another way...?

 

let threshold = 5;
let szSharePointFileOperation = "SharePointFileOperation";
let szOperations = dynamic(["FileDownloaded", "FileUploaded"]);
let starttime = 14d;
let endtime = 1d;
let historicalActivity =
    OfficeActivity
    | where TimeGenerated between(ago(starttime) .. ago(endtime))
    | where RecordType =~ szSharePointFileOperation
    | where Operation in~ (szOperations)
    | where isnotempty(UserAgent)
    | summarize historicalCount = count() by UserAgent, RecordType, Operation;
let ReplacedData =
    OfficeActivity
    | where TimeGenerated between(ago(starttime) .. ago(endtime))
    | where RecordType =~ szSharePointFileOperation
    | where Operation in~ (szOperations)
    | extend replaced=replace_regex(UserId, @'([^\\]*)@gmail.com', @'\1')
    | project replaced;
let recentActivity = OfficeActivity
    | where RecordType =~ szSharePointFileOperation
    | where Operation in~ (szOperations)
    | where TimeGenerated > ago(endtime)
    | where isnotempty(UserAgent)
    | summarize min(Start_Time), max(Start_Time), recentCount = count() by UserAgent, RecordType, Operation;
let RareUserAgent = recentActivity
    | join kind = leftanti (historicalActivity) on UserAgent
    | order by recentCount desc, UserAgent
    // More than 5 downloads/uploads from a new user agent today
    | where recentCount > threshold;
//OfficeActivity 
OfficeActivity 
| where TimeGenerated > ago(endtime) 
| where RecordType =~ szSharePointFileOperation 
| where Operation in~ (szOperations)
| where isnotempty(UserAgent)
| where Site_Url contains (ReplacedData)
| join kind= inner (RareUserAgent)
    on UserAgent, RecordType, Operation    
| where Start_Time between(min_Start_Time .. max_Start_Time)
| summarize StartTimeUtc = min(min_Start_Time), EndTimeUtc = max(max_Start_Time)
    by
    RecordType,
    Operation,
    UserAgent,
    UserType,
    UserId,
    ClientIP,
    OfficeWorkload,
    Site_Url,
    OfficeObjectId,
    UserAgentSeenCount = recentCount
| extend
    timestamp = StartTimeUtc,
    AccountCustomEntity = UserId,
    IPCustomEntity = ClientIP,
    URLCustomEntity = Site_Url
| extend replaced=replace_regex(UserId, @'([^\\]*)@gmail.com', @'\1')
| order by UserAgentSeenCount desc, UserAgent asc, Operation asc, UserId asc, replaced

 

Any idea how to get this query to work correctly. Or another way to come up with a solution.

 

Thanks.

Regards.

2 Replies
| where Site_Url in (ReplacedData)

You should use "in" rather than "contains" for the above line

Hi @Clive_Watson 

 

But the problem is that I would need that the string is not equal but that it is with the sentence "contains" or similar, because when using "IN" I am indicating that the parameter is equal and I don't want it to be equal but to contain it, because all the parameters are not going to be equal.

 

Regards.