SOLVED
Home

Can any query be saved as a function ?

%3CLINGO-SUB%20id%3D%22lingo-sub-334260%22%20slang%3D%22en-US%22%3ECan%20any%20query%20be%20saved%20as%20a%20function%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334260%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20written%20a%20query%20that%20is%20a%20bit%20involved%2C%20it%20is%20multi-line%20and%20it%20contains%20a%20union%20and%20a%20call%20to%20lookup.%20It%20works%20well%20as%20a%20query%2C%20but%20if%20I%20save%20it%20as%20a%20function%20and%20then%20try%20to%20run%20that%2C%20I%20get%20an%20error%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%22%26nbsp%3B%3CSPAN%3E'lookup'%20operator%3A%20Failed%20to%20resolve%20table%20or%20column%20expression%20named%20'domain_specific'%20%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIs%20this%20a%20known%20limitation%20or%20is%20this%20just%20a%20matter%20of%20parsing%20and%20I%20can%20add%20parenthesis%20somewhere%20or%20something%20similar%20%3F%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-334260%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKusto%20language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359455%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20any%20query%20be%20saved%20as%20a%20function%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359455%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20opened%20a%20support%20case%20for%20this%20behavior%2C%20and%20the%20Kusto%20team%20acknowledged%20this%20is%20a%20bug.%20I%20was%20told%20it%20should%20be%20fixed%20around%20the%206th%20of%20March.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338569%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20any%20query%20be%20saved%20as%20a%20function%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338569%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20think%20this%20is%20some%20kind%20of%20a%20bug%20better%20log%20official%20case%20to%20MSFT.%20Seems%20the%20combination%20of%20using%20let%20with%20workspace()%20operator%20is%20causing%20the%20error.%20I%20for%20sure%20haven't%20seen%20this%20behavior%20documented.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338547%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20any%20query%20be%20saved%20as%20a%20function%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338547%22%20slang%3D%22en-US%22%3E%3CP%3EI%20meant%20I%20have%20successfully%20created%20a%20function%20that%20uses%20a%20different%20workspace%20and%20works%20correctly%2C%20both%20as%20a%20query%20and%20as%20a%20custom%20function%2C%20this%20one%20in%20particular%20%3A%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Eworkspace(%3C%2FSPAN%3E%3CSPAN%3E%22something%22%3C%2FSPAN%3E%3CSPAN%3E).Event_CL%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Edistinct%3C%2FSPAN%3E%3CSPAN%3E%20Bla%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20stn%3Dsubstring(Bla%2C%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E5%3C%2FSPAN%3E%3CSPAN%3E)%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EBut%2C%20since%20you%20encountered%20an%20error%2C%20I%20have%20tried%20your%20suggestion%20and%20indeed%20the%20complete%20query%20works%20if%20I%20remove%20the%20call%20to%20workspace.%20It%20seems%20the%20combination%20of%20workspace%20and%20lookup%20break%20something%2C%20which%20is%20interesting.%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ENot%20quite%20sure%20yet%20what%20to%20do%20with%20the%20knowledge%20though.%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338490%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20any%20query%20be%20saved%20as%20a%20function%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338490%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EI%20am%20not%20sure%20if%20I%20understand%20this%3A%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%0A%3CP%3EThe%20workspace%20command%20alone%20works%20in%20the%20scope%20of%20a%20function%2C%20I%20have%20tried%20all%20the%20variations.%20In%20this%20case%20the%20error%20is%20informative%2C%20it%20is%20only%20lookup%20that%20fails%20when%20run%20from%20inside%20a%20function.%3C%2FP%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CP%3EIn%20my%20case%20if%20you%20use%20the%20workspace()%20operator%20in%20query%20that%20works.%20That%20is%20expected%20behavior%20as%20that%20is%20the%20intention%20of%20the%20operator.%20When%20you%20save%20a%20query%20that%20has%20workspace()%20operator%20in%20it%20as%20function%20and%20you%20try%20to%20call%20the%20saved%20function%20you%20will%20get%20error.%20At%20least%20I%20got%20error.%20This%20means%20that%20using%20workspace()%20operator%20in%20a%20function%20is%20not%20supported.%20Have%20you%20tried%20saving%20the%20exact%20query%20you%20have%20but%20without%20using%20workspace()%20operator%20and%20than%20calling%20that%20function%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338485%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20any%20query%20be%20saved%20as%20a%20function%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338485%22%20slang%3D%22en-US%22%3EThe%20workspace%20command%20alone%20works%20in%20the%20scope%20of%20a%20function%2C%20I%20have%20tried%20all%20the%20variations.%20In%20this%20case%20the%20error%20is%20informative%2C%20it%20is%20only%20lookup%20that%20fails%20when%20run%20from%20inside%20a%20function.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338465%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20any%20query%20be%20saved%20as%20a%20function%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338465%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EYes%20by%20function%20I%20mean%20another%20custom%20function.%20Things%20like%20count()%2C%20summarize%2C%20extend%20are%20operators%20in%20the%20query%20language.%20I%20hope%20that%20makes%20it%20a%20little%20bit%20easier.%20I%20thing%20the%20problem%20comes%20that%20you%20are%20referring%20to%20another%20workspace%20in%20the%20function.%20As%20far%20as%20I%20understand%20you%20cannot%20use%20workspace()%20operator%20in%20functions.%20I%20do%20not%20think%20that%20is%20documented.%20It%20would%20be%20good%20if%20it%20is.%20Try%20to%20use%20the%20same%20query%20but%20without%20referencing%20another%20workspace.%20If%20it%20works%20than%20that%20is%20the%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338429%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20any%20query%20be%20saved%20as%20a%20function%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338429%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20mean%20my%20function%20can't%20use%20substring%20or%20extract%20for%20example%20%3F%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tested%20that%2C%20and%20it%20works.%20I%20guess%20you%20mean%20other%20custom%20functions%2C%20I%20haven't%20done%20that.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20shorter%20version%20of%20my%20query%20that%20also%20gets%20the%20same%20error%20(%20with%20names%20changed%20)%26nbsp%3B%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3Elet%20short%20%3D%20workspace(%22something%22).Event_CL%20%7C%20distinct%20TenantId_s%20%7C%20extend%20stn%3Dsubstring(TenantId_s%2C%201%2C%205)%3B%3CBR%20%2F%3Elet%20Parsed_logs%20%3D%20Whatever_CL%20%7C%20parse%20RawData%20with%20*%20%22%5B%5B%5B%5B%22%20x1%26nbsp%3B%22%5D%20%5B%22%26nbsp%3Bx2%26nbsp%3B%22%5D%20%5B%5D%20%5B%22%20x3%26nbsp%3B%22%5D%20%5B203%5D%20%5B%5D%20%5B%5D%20%5B%5D%20%5Bstring%5D%20%5B%22%20x4%26nbsp%3B%22%5D%20%5B%5D%20%5B1%5D%20%5B%5D%20%5B203%5D%20%5B%22%20x5%26nbsp%3B%22%20line%3A%22%20x6%26nbsp%3B%22%5D%22%20x7%20%7C%20project%26nbsp%3Bx1%2Cx2%2Cx3%2Cx4%2Cx5%2Cx6%2C%20compId%3B%3CBR%20%2F%3EParsed_logs%20%7C%20extend%20stn%20%3D%20extract(%22(yyy)(%5C%5Cd%7B5%7D).*%22%2C2%2C%20compId%20)%20%7C%20lookup%20kind%3Dleftouter%20short%26nbsp%3Bon%20stn%20%7C%20project-away%20stn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338276%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20any%20query%20be%20saved%20as%20a%20function%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338276%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3ECan%20you%20provide%20the%20query%20or%20may%20be%20some%20example%20to%20understand%20what%20kind%20of%20operators%20are%20you%20using%20there%3F%20Keep%20in%20mind%20that%20in%20function%20you%20cannot%20use%20another%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

I have written a query that is a bit involved, it is multi-line and it contains a union and a call to lookup. It works well as a query, but if I save it as a function and then try to run that, I get an error: 

'lookup' operator: Failed to resolve table or column expression named 'domain_specific' "

 

Is this a known limitation or is this just a matter of parsing and I can add parenthesis somewhere or something similar ? 

8 Replies

Hi,

Can you provide the query or may be some example to understand what kind of operators are you using there? Keep in mind that in function you cannot use another function.

You mean my function can't use substring or extract for example ? 

I have tested that, and it works. I guess you mean other custom functions, I haven't done that. 

 

This is a shorter version of my query that also gets the same error ( with names changed )  : 

let short = workspace("something").Event_CL | distinct TenantId_s | extend stn=substring(TenantId_s, 1, 5);
let Parsed_logs = Whatever_CL | parse RawData with * "[[[[" x1 "] [" x2 "] [] [" x3 "] [203] [] [] [] [string] [" x4 "] [] [1] [] [203] [" x5 " line:" x6 "]" x7 | project x1,x2,x3,x4,x5,x6, compId;
Parsed_logs | extend stn = extract("(yyy)(\\d{5}).*",2, compId ) | lookup kind=leftouter short on stn | project-away stn

Hi,

Yes by function I mean another custom function. Things like count(), summarize, extend are operators in the query language. I hope that makes it a little bit easier. I thing the problem comes that you are referring to another workspace in the function. As far as I understand you cannot use workspace() operator in functions. I do not think that is documented. It would be good if it is. Try to use the same query but without referencing another workspace. If it works than that is the problem.

The workspace command alone works in the scope of a function, I have tried all the variations. In this case the error is informative, it is only lookup that fails when run from inside a function.

Hi,

I am not sure if I understand this:

The workspace command alone works in the scope of a function, I have tried all the variations. In this case the error is informative, it is only lookup that fails when run from inside a function.

In my case if you use the workspace() operator in query that works. That is expected behavior as that is the intention of the operator. When you save a query that has workspace() operator in it as function and you try to call the saved function you will get error. At least I got error. This means that using workspace() operator in a function is not supported. Have you tried saving the exact query you have but without using workspace() operator and than calling that function?

I meant I have successfully created a function that uses a different workspace and works correctly, both as a query and as a custom function, this one in particular : 

workspace("something").Event_CL | distinct Bla | extend stn=substring(Bla, 1, 5);
 
But, since you encountered an error, I have tried your suggestion and indeed the complete query works if I remove the call to workspace. It seems the combination of workspace and lookup break something, which is interesting. 
 
Not quite sure yet what to do with the knowledge though.
Solution

If you think this is some kind of a bug better log official case to MSFT. Seems the combination of using let with workspace() operator is causing the error. I for sure haven't seen this behavior documented.

I have opened a support case for this behavior, and the Kusto team acknowledged this is a bug. I was told it should be fixed around the 6th of March. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies