Log Analytics Query

%3CLINGO-SUB%20id%3D%22lingo-sub-214994%22%20slang%3D%22en-US%22%3ELog%20Analytics%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-214994%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Elet%20ips%20%3D%20()%3C%2FSPAN%3E%3CSPAN%3E%7B%3C%2FSPAN%3E%20%3CSPAN%3E%2210.0.0.2%22%3C%2FSPAN%3E%3CSPAN%3E%20%7C%20%3C%2FSPAN%3E%3CSPAN%3E%2211.0.0.3%22%3C%2FSPAN%3E%20%3CSPAN%3E%7D%3C%2FSPAN%3E%3CSPAN%3E%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Esearch%3C%2FSPAN%3E%3CSPAN%3E%20*%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20(%20StreamName_s%20!%3C%2FSPAN%3E%3CSPAN%3Econtains%3C%2FSPAN%3E%20%3CSPAN%3E%22clusters%22%20)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eorder%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%2C%20identity_s%2C%20CallerIPAddress%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20CallerIPAddress%20!%3C%2FSPAN%3E%3CSPAN%3Econtains%3C%2FSPAN%3E%3CSPAN%3E%20ips%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EWould%20you%20please%20guide%20me%20how%20I%20can%20create%20a%20list%20of%20IP%20addresses%20and%20define%20a%20query%20to%20filter%20out%20the%20results%20which%20contain%20the%20item%20of%20the%20list%3F%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-214994%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-215083%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215083%22%20slang%3D%22en-US%22%3E%3CP%3EMohsen%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou're%20very%20close%20in%20your%20query.%20There%20are%20two%20things%20you%20need%20to%20fix%3A%201)%20the%20way%20you%20define%20your%20IP%20set%2C%20and%202)%20using%20the%20!in%20operator%20instead%20of%20!contains.%20The%20updated%20query%20will%20look%20as%20follows%20(I've%20commented%20two%20of%20the%20lines%20out%20as%20I%20don't%20have%20any%20data%20with%20the%20%22streamname_s%22%20or%20%22identity_s%22%20columns%20-%20you%20should%20uncomment%20them)%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Elet%20ips%20%3D%20datatable(IP%3Astring)%5B%0A%20%20%20%20%2210.0.0.2%22%2C%20%0A%20%20%20%20%2211.0.0.3%22%20%0A%5D%3B%0Asearch%20*%0A%2F%2F%7C%20where%20(%20StreamName_s%20!contains%20%22clusters%22%20)%0A%7C%20order%20by%20TimeGenerated%0A%2F%2F%7C%20project%20TimeGenerated%2C%20identity_s%2C%20CallerIPAddress%0A%7C%20where%20CallerIPAddress%20!in%20(ips)%3C%2FPRE%3E%0A%3CP%3EUsing%20search%20*%2C%20however%2C%20is%20potentially%20misleading%20here%3A%20it%20will%20search%20across%26nbsp%3B%3CEM%3Eall%3C%2FEM%3E%20your%20data%20and%20return%20%3CEM%3Eany%20%3C%2FEM%3Erow%20where%20the%20CallerIPAddress%20column%20does%20not%20a%20value%20matching%20those%20two%20IPs.%20Practically%2C%20that%20means%20that%20any%20rows%20with%20an%20empty%20CallerIPAddress%20will%20also%20be%20returned%2C%20likely%20not%20the%20behaviour%20you're%20looking%20for.%20This%20means%20you'll%20likely%20want%20to%20add%20one%20further%20where%20statement%3A%3C%2FP%3E%0A%3CPRE%3Elet%20ips%20%3D%20datatable(IP%3Astring)%5B%0A%20%20%20%20%2210.0.0.2%22%2C%20%0A%20%20%20%20%2211.0.0.3%22%20%0A%5D%3B%0Asearch%20*%0A%2F%2F%7C%20where%20(%20StreamName_s%20!contains%20%22clusters%22%20)%0A%7C%20order%20by%20TimeGenerated%0A%2F%2F%7C%20project%20TimeGenerated%2C%20identity_s%2C%20CallerIPAddress%0A%3CFONT%20color%3D%22%230000ff%22%3E%3CSTRONG%3E%7C%20where%20isnotempty(CallerIPAddress)%3C%2FSTRONG%3E%3C%2FFONT%3E%0A%7C%20where%20CallerIPAddress%20!in%20(ips)%3C%2FPRE%3E%0A%3CP%3EI'd%20also%20urge%20you%2C%20for%20efficiency's%20sake%2C%20to%20use%20a%20particular%20table%20name%2C%20or%20a%20union%20of%20table%20names%20as%20your%20data%20source%2C%20rather%20than%20a%20search%20*.%20This'll%20ensure%20your%20queries%20run%20much%20quicker%2C%20and%20the%20results%20you%20see%20are%20much%20more%20focused.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFinally%2C%20check%20out%20the%20module%20on%20the%20%3CA%20href%3D%22https%3A%2F%2Fapp.pluralsight.com%2Fplayer%3Fcourse%3Dkusto-query-language-kql-from-scratch%26amp%3Bauthor%3Drobert-cain%26amp%3Bname%3D76e93c79-0bd9-4211-88a2-a8c397784301%26amp%3Bclip%3D0%26amp%3Bmode%3Dlive%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Elet%20command%3C%2FA%3E%20and%20the%20%3CA%20href%3D%22https%3A%2F%2Fapp.pluralsight.com%2Fplayer%3Fcourse%3Dkusto-query-language-kql-from-scratch%26amp%3Bauthor%3Drobert-cain%26amp%3Bname%3D1bac4cb8-345a-4abd-ac6d-8cd186a4645b%26amp%3Bclip%3D16%26amp%3Bmode%3Dlive%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ein%20%26amp%3B%20!in%20operators%3C%2FA%3E%20on%20our%20Pluralsight%20course%20(free%20to%20watch%20after%20a%20free%20account%20registration)%20if%20you%20want%20to%20learn%20a%20bit%20more!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable
let ips = (){ "10.0.0.2" | "11.0.0.3" };
search *
| where ( StreamName_s !contains "clusters" )
| order by TimeGenerated
| project TimeGenerated, identity_s, CallerIPAddress
| where CallerIPAddress !contains ips
 
Would you please guide me how I can create a list of IP addresses and define a query to filter out the results which contain the item of the list?
1 Reply

Mohsen,

 

You're very close in your query. There are two things you need to fix: 1) the way you define your IP set, and 2) using the !in operator instead of !contains. The updated query will look as follows (I've commented two of the lines out as I don't have any data with the "streamname_s" or "identity_s" columns - you should uncomment them):

 

let ips = datatable(IP:string)[
    "10.0.0.2", 
    "11.0.0.3" 
];
search *
//| where ( StreamName_s !contains "clusters" )
| order by TimeGenerated
//| project TimeGenerated, identity_s, CallerIPAddress
| where CallerIPAddress !in (ips)

Using search *, however, is potentially misleading here: it will search across all your data and return any row where the CallerIPAddress column does not a value matching those two IPs. Practically, that means that any rows with an empty CallerIPAddress will also be returned, likely not the behaviour you're looking for. This means you'll likely want to add one further where statement:

let ips = datatable(IP:string)[
    "10.0.0.2", 
    "11.0.0.3" 
];
search *
//| where ( StreamName_s !contains "clusters" )
| order by TimeGenerated
//| project TimeGenerated, identity_s, CallerIPAddress
| where isnotempty(CallerIPAddress)
| where CallerIPAddress !in (ips)

I'd also urge you, for efficiency's sake, to use a particular table name, or a union of table names as your data source, rather than a search *. This'll ensure your queries run much quicker, and the results you see are much more focused.

 

Finally, check out the module on the let command and the in & !in operators on our Pluralsight course (free to watch after a free account registration) if you want to learn a bit more!