i am trying to fetch the machine list which does not contain letter " l " in 8th position.

%3CLINGO-SUB%20id%3D%22lingo-sub-2282492%22%20slang%3D%22en-US%22%3Ei%20am%20trying%20to%20fetch%20the%20machine%20list%20which%20does%20not%20contain%20letter%20%22%20l%20%22%20in%208th%20position.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282492%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20fetch%20the%20machine%20list%20which%20does%20not%20contain%20letter%20%22%20l%20%22%20in%208th%20position.%3C%2FP%3E%3CP%3Ecan%20i%20use%20Kusto%20regex%20to%20achieve%20this.%20I%20only%20see%20matches%20regex%20command.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282855%22%20slang%3D%22en-US%22%3ERe%3A%20i%20am%20trying%20to%20fetch%20the%20machine%20list%20which%20does%20not%20contain%20letter%20%22%20l%20%22%20in%208th%20posit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282855%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1034873%22%20target%3D%22_blank%22%3E%40deepak198486%3C%2FA%3E%26nbsp%3BYou%20can%20also%20try%20just%20plain%20indexof()%20to%20see%20if%20it%20is%20faster%20than%20using%20the%20regex.%3C%2FP%3E%3CP%3E%7C%20indexof(VM%2C%22l%22%2C8%2C1)%3D%3D-1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282806%22%20slang%3D%22en-US%22%3ERe%3A%20i%20am%20trying%20to%20fetch%20the%20machine%20list%20which%20does%20not%20contain%20letter%20%22%20l%20%22%20in%208th%20posit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282806%22%20slang%3D%22en-US%22%3EThanks%3CBR%20%2F%3EI%20used%20the%20not%20operator%20and%20it%20worked.%3CBR%20%2F%3EMeanwhile%20below%20is%20the%20query%3CBR%20%2F%3ESecurityRecommendation%3CBR%20%2F%3E%7C%20where%20RecommendationName%20%3D%3D%20%22Disk%20encryption%20should%20be%20applied%20on%20virtual%20machines%22%3CBR%20%2F%3E%7C%20where%20RecommendationState%20in%20(%22NotApplicable%22%2C%20%22Unhealthy%22)%3CBR%20%2F%3E%7C%20extend%20field%20%3D%20split(AssessedResourceId%2C%20'%2F')%3CBR%20%2F%3E%7C%20extend%20VM%20%3D%20tostring(field%5B8%5D)%3CBR%20%2F%3E%7C%20extend%20resourceGroups%20%3D%20tostring(field%5B4%5D)%3CBR%20%2F%3E%7C%20extend%20subscriptions%20%3D%20tostring(field%5B2%5D)%3CBR%20%2F%3E%7C%20where%20not(VM%20matches%20regex%20%22%5E.......%5Bl%5D.*%22)%20%3D%3D%20true%3CBR%20%2F%3E%7C%20project%20VM%2C%20RecommendationDisplayName%2C%20resourceGroups%2C%20subscriptions%2C%20TimeGenerated%3CBR%20%2F%3E%7C%20extend%20HostCustomEntity%20%3D%20VM%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282708%22%20slang%3D%22en-US%22%3ERe%3A%20i%20am%20trying%20to%20fetch%20the%20machine%20list%20which%20does%20not%20contain%20letter%20%22%20l%20%22%20in%208th%20posit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282708%22%20slang%3D%22en-US%22%3EThere%20is%20also%20substring.%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-gb%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fsubstringfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-gb%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fsubstringfunction%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3Elet%20name_%20%3D%20%22thisisanZme%22%3B%3CBR%20%2F%3Eprint%20a%3D%20substring(name_%2C8%2C%201)%3CBR%20%2F%3E%7C%20where%20a%20!%3D%20%22Z%22%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282704%22%20slang%3D%22en-US%22%3ERe%3A%20i%20am%20trying%20to%20fetch%20the%20machine%20list%20which%20does%20not%20contain%20letter%20%22%20l%20%22%20in%208th%20posit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282704%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1034873%22%20target%3D%22_blank%22%3E%40deepak198486%3C%2FA%3E%26nbsp%3BThere%20is%20the%20indexof_regex%20command%20(%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Findexofregexfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eindexof_regex()%20-%20Azure%20Data%20Explorer%20%7C%20Microsoft%20Docs%3C%2FA%3E)%20that%20could%20work%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282654%22%20slang%3D%22en-US%22%3ERe%3A%20i%20am%20trying%20to%20fetch%20the%20machine%20list%20which%20does%20not%20contain%20letter%20%22%20l%20%22%20in%208th%20posit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282654%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1034873%22%20target%3D%22_blank%22%3E%40deepak198486%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20post%20the%20complete%20query%3F%20We%20will%20be%20able%20to%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20you%20tried%20using%20the%20%22%7C%20where%20not%22%20xxxxxxx%20feature%3F%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282566%22%20slang%3D%22en-US%22%3ERe%3A%20i%20am%20trying%20to%20fetch%20the%20machine%20list%20which%20does%20not%20contain%20letter%20%22%20l%20%22%20in%208th%20posit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282566%22%20slang%3D%22en-US%22%3EI%20have%20the%20regex%20which%20identifies%20it%20and%20get%20the%20results%20but%20i%20want%20the%20results%20not%20matching%20the%20regex.%20Is%20there%20any%20command%20or%20function%20to%20achieve%20this%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2290041%22%20slang%3D%22en-US%22%3ERe%3A%20i%20am%20trying%20to%20fetch%20the%20machine%20list%20which%20does%20not%20contain%20letter%20%22%20l%20%22%20in%208th%20posit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2290041%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3EI%20tried%20using%20indexof(VM%2C%22l%22%2C8%2C1)%3D%3D-1%20but%20it%20is%20not%20giving%20the%20expected%20result.%3CBR%20%2F%3EI%20even%20tried%20with%20indexof_regex()%20but%20it%20also%20not%20giving%20the%20expected%20result.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2291430%22%20slang%3D%22en-US%22%3ERe%3A%20i%20am%20trying%20to%20fetch%20the%20machine%20list%20which%20does%20not%20contain%20letter%20%22%20l%20%22%20in%208th%20posit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2291430%22%20slang%3D%22en-US%22%3ESorry%2C%20forgot%20the%20indexof%20starts%20its%20indexed%20with%20zero%20so%20it%20would%20would%20indexOf(VM%2C%22l%22%2C7%2C1)%20but%20in%20my%20testing%20it%20doesn't%20seem%20to%20be%20faster.%3C%2FLINGO-BODY%3E
Occasional Contributor

I am trying to fetch the machine list which does not contain letter " l " in 8th position.

can i use Kusto regex to achieve this. I only see matches regex command.

9 Replies
I have the regex which identifies it and get the results but i want the results not matching the regex. Is there any command or function to achieve this

@deepak198486 

 

Can you post the complete query? We will be able to help.

 

Have you tried using the "| where not" xxxxxxx feature?   

There is also substring. https://docs.microsoft.com/en-gb/azure/data-explorer/kusto/query/substringfunction



let name_ = "thisisanZme";
print a= substring(name_,8, 1)
| where a != "Z"
Thanks
I used the not operator and it worked.
Meanwhile below is the query
SecurityRecommendation
| where RecommendationName == "Disk encryption should be applied on virtual machines"
| where RecommendationState in ("NotApplicable", "Unhealthy")
| extend field = split(AssessedResourceId, '/')
| extend VM = tostring(field[8])
| extend resourceGroups = tostring(field[4])
| extend subscriptions = tostring(field[2])
| where not(VM matches regex "^.......[l].*") == true
| project VM, RecommendationDisplayName, resourceGroups, subscriptions, TimeGenerated
| extend HostCustomEntity = VM

@deepak198486 You can also try just plain indexof() to see if it is faster than using the regex.

| indexof(VM,"l",8,1)==-1     

Hi
I tried using indexof(VM,"l",8,1)==-1 but it is not giving the expected result.
I even tried with indexof_regex() but it also not giving the expected result.
Sorry, forgot the indexof starts its indexed with zero so it would would indexOf(VM,"l",7,1) but in my testing it doesn't seem to be faster.
it works but regex is faster
00:01.2-using regex
00:02.7-using indexof