SOLVED

'where' operator: Failed to resolve table or column expression named 'SecurityEvent'

%3CLINGO-SUB%20id%3D%22lingo-sub-241234%22%20slang%3D%22en-US%22%3E'where'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'SecurityEvent'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241234%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhenever%20I%20attempt%20to%20run%20the%20following%20Log%20Analytic%20query%20in%20Azure%20Log%20Analytics%20I%20get%20the%20following%20error%3A%3C%2FP%3E%3CP%3E'where'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'SecurityEvent'%3C%2FP%3E%3CP%3EI%20think%20it's%20because%20I%20need%20to%20enable%20'SecurityEvent'%20in%20Log%20Analytics%20but%20I'm%20not%20sure.%20I%20was%20wondering%20if%20someone%20could%20provide%20a%20guide%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BSecurityEvent%3CBR%20%2F%3E%7C%20where%20AccountType%20%3D%3D%20%22User%22%20and%20EventID%20%3D%3D%204625%20and%20TimeGenerated%20%26gt%3B%20ago(6h)%3CBR%20%2F%3E%7C%20summarize%20IPCount%20%3D%20dcount(IpAddress)%2C%20makeset(IpAddress)%20by%20Account%3CBR%20%2F%3E%7C%20where%20IPCount%20%26gt%3B%205%3CBR%20%2F%3E%7C%20sort%20by%20IPCount%20desc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BAny%20ideas%20would%20be%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-241234%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241859%22%20slang%3D%22en-US%22%3ERe%3A%20'where'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'SecurityEvent'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241859%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Travis%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFantastic%20video%20..%20very%20informative.%20Thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%2C%20the%20video%20doesn't%20cover%20adding%20Security%20Policy%20to%20allow%20the%20the%20following%20query%20from%20being%20added%20with%20the%20error%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E'where'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'SecurityEvent'.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESecurityEvent%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20ago(30m)%3CBR%20%2F%3E%7C%20count%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241843%22%20slang%3D%22en-US%22%3ERe%3A%20'where'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'SecurityEvent'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241843%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%20about%20that.%26nbsp%3B%20My%20site%20is%20hosted%20in%20the%20Azure%20South%20Central%20region%20and%20that%20seems%20to%20be%20offline%20this%20morning.%26nbsp%3B%20Here%20is%20a%20link%20to%20the%20video%20in%20YouTube.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DOI2iUIh340U%26amp%3Blist%3DPLnWpsLZNgHzVXXyN9a0jm9xNNDrikHf8I%26amp%3Bindex%3D3%26amp%3Bt%3D0s%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DOI2iUIh340U%26amp%3Blist%3DPLnWpsLZNgHzVXXyN9a0jm9xNNDrikHf8I%26amp%3Bindex%3D3%26amp%3Bt%3D0s%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241725%22%20slang%3D%22en-US%22%3ERe%3A%20'where'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'SecurityEvent'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241725%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Travis%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20to%20access%20the%20link%20you%20provided%20for%20the%20first%20time%20today%2C%20but%20the%20site%20appears%20to%20be%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20provide%20another%20link%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20don't%20hear%20from%20you%20I'll%20submit%20another%20question%2C%20as%20I'm%20not%20sure%20if%20you'll%20see%20this%20once%20it%20has%20been%20answered.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241466%22%20slang%3D%22en-US%22%3ERe%3A%20'where'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'SecurityEvent'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241466%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Travis%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Travis%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241451%22%20slang%3D%22en-US%22%3ERe%3A%20'where'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'SecurityEvent'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241451%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Travis%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20haven't%20checked%20out%20the%20video%20yet%2C%20but%20just%20wanted%20to%20say%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20check%20it%20out%20later%20this%20afternoon.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241266%22%20slang%3D%22en-US%22%3ERe%3A%20'where'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'SecurityEvent'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241266%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22p1%22%3EI%20posted%20a%20video%20with%20a%20walkthrough%20on%20log%20collection%20setup.%20The%20quick%20version%20is%20to%20go%20into%20the%20Log%20Analytics%20workspace%20in%20Azure%2C%20Go%20to%20Workspace%20Overview%20and%20Add.%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%3C%2FSPAN%3EScroll%20down%20to%20the%20Security%20and%20Compliance%20solution.%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EYou%20could%20also%20try%20going%20into%20Logs%20(Preview)%20for%20Advanced%20Log%20Analytics%20and%20check%20what%20shows%20in%20the%20Schema.%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CA%20href%3D%22http%3A%2F%2Fwww.ciraltos.com%2Fazure-oms-step-by-step-log-collection-setup%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.ciraltos.com%2Fazure-oms-step-by-step-log-collection-setup%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241250%22%20slang%3D%22en-US%22%3ERe%3A%20'where'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'SecurityEvent'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241250%22%20slang%3D%22en-US%22%3E%3CP%3ETravis%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20reason%20I%20asked%20how%20to%20%22%3CSPAN%3E%26nbsp%3Badd%20the%20Security%20and%20Compliance%20solution%20to%20the%20log%20security%20events%3F%22%20is%20because%20I%20believe%20I%20have%20already%20added%20it.%20However%2C%20when%20I%20run%20the%20query%20I%20get%20the%20same%20error%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241249%22%20slang%3D%22en-US%22%3ERe%3A%20'where'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'SecurityEvent'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241249%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Travis%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20getting%20in%20touch.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20let%20me%20know%20how%20to%20add%20the%20Security%20and%20Compliance%20solution%20to%20the%20log%20security%20events%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%20I%20then%20be%20able%20to%20get%20a%20result%20from%20the%20script%20from%20Log%20Analytics%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241244%22%20slang%3D%22en-US%22%3ERe%3A%20'where'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'SecurityEvent'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241244%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20Security%20and%20Compliance%20solution%20has%20to%20be%20added%20to%20log%20security%20events.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-654681%22%20slang%3D%22en-US%22%3ERe%3A%20'where'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'SecurityEvent'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-654681%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F183334%22%20target%3D%22_blank%22%3E%40Carlton%20Patterson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20your%20response.%20You%20saved%20my%20blog%26nbsp%3B%3CA%20title%3D%22Pune%20Food%20Blog%22%20href%3D%22https%3A%2F%2Fpunefoodblog.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EPune%20Food%20Blog%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello Community,

 

Whenever I attempt to run the following Log Analytic query in Azure Log Analytics I get the following error:

'where' operator: Failed to resolve table or column expression named 'SecurityEvent'

I think it's because I need to enable 'SecurityEvent' in Log Analytics but I'm not sure. I was wondering if someone could provide a guide;

 

 SecurityEvent
| where AccountType == "User" and EventID == 4625 and TimeGenerated > ago(6h)
| summarize IPCount = dcount(IpAddress), makeset(IpAddress) by Account
| where IPCount > 5
| sort by IPCount desc

 

 Any ideas would be much appreciated.

 

Cheers

10 Replies
Highlighted

The Security and Compliance solution has to be added to log security events.

Highlighted

Hi Travis,

 

Thanks for getting in touch. 

 

Can you let me know how to add the Security and Compliance solution to the log security events?

 

Will I then be able to get a result from the script from Log Analytics?

 

Cheers

Highlighted

Travis,

 

The reason I asked how to " add the Security and Compliance solution to the log security events?" is because I believe I have already added it. However, when I run the query I get the same error

Highlighted
Solution

I posted a video with a walkthrough on log collection setup. The quick version is to go into the Log Analytics workspace in Azure, Go to Workspace Overview and Add.  Scroll down to the Security and Compliance solution.   

 

You could also try going into Logs (Preview) for Advanced Log Analytics and check what shows in the Schema.

 

http://www.ciraltos.com/azure-oms-step-by-step-log-collection-setup/

Highlighted

Hi Travis,

 

I haven't checked out the video yet, but just wanted to say thanks.

 

I will check it out later this afternoon.

 

Cheers

Highlighted
Highlighted

Hello Travis, 

 

I tried to access the link you provided for the first time today, but the site appears to be down.

 

Can you provide another link?

 

If I don't hear from you I'll submit another question, as I'm not sure if you'll see this once it has been answered.

 

Cheers

Highlighted

Sorry about that.  My site is hosted in the Azure South Central region and that seems to be offline this morning.  Here is a link to the video in YouTube. https://www.youtube.com/watch?v=OI2iUIh340U&list=PLnWpsLZNgHzVXXyN9a0jm9xNNDrikHf8I&index=3&t=0s

In this video I give a step by step overview of how to setup log collection for Azure OMS Log Analytics. I include setting up log collection for Azure and no...
Highlighted

Hi Travis,

 

Fantastic video .. very informative. Thanks

 

Unfortunately, the video doesn't cover adding Security Policy to allow the the following query from being added with the error:

 

'where' operator: Failed to resolve table or column expression named 'SecurityEvent'.

 

SecurityEvent
| where TimeGenerated > ago(30m)
| count

 

 

Highlighted

@Carlton Patterson 

 

Thank you so much for your response. You saved my blog Pune Food Blog