SOLVED
Home

Getting Failed to resolve scalar expression. How to create column for null fields sent from our RP?

%3CLINGO-SUB%20id%3D%22lingo-sub-352954%22%20slang%3D%22en-US%22%3EGetting%20Failed%20to%20resolve%20scalar%20expression.%20How%20to%20create%20column%20for%20null%20fields%20sent%20from%20our%20RP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352954%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20we%20ingest%20data%20from%20our%20RP%20to%20LA%2C%20there%20are%20some%20fields%20and%20we%20explicitly%20set%20null%20values.%3C%2FP%3E%0A%3CP%3EThis%20is%20not%20for%20all%20scenarios.%20In%20other%20scenario%2C%20string%20values%20will%20be%20filled%20for%20those%20columns.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20eg%20-%20For%20Job%20A%2C%20field%201%20will%20be%20null.%3C%2FP%3E%0A%3CP%3EBut%20in%20case%20of%20Job%20B%2C%20field%201%20will%20be%20having%20string%20value.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJob%20A%20is%20very%20common%20and%20Job%20B%20is%20quite%20rare.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20since%20my%20setup%20has%20till%20now%20only%20those%20common%20jobs%2C%20I%20don't%20see%20field%201%20created%20in%20my%20LA%20log%20search.%20Though%20the%20json%20we%20send%20from%20our%20RP%20explicitly%20have%20null%20values%20for%20those%20fields.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20need%20to%20write%20a%20generalized%20query%20(for%20all%20jobs)%20and%20print%20all%20the%20job%20specific%20fields.%3C%2FP%3E%0A%3CP%3ESince%20the%20null%20field%20was%20not%20created%20yet%2C%20I%20get%20this%20error%20-%26nbsp%3B%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EFailed%20to%20resolve%20scalar%20expression%3C%2FFONT%3E%20when%20I%20query%20for%20that%20field.%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20how%20to%20handle%20this%20case%3F%20Is%20there%20a%20way%20to%20let%20LA%20know%20the%20schema%20upfront%20so%20that%20null%20field%20columns%20are%20also%20created.%20Any%20other%20pointers%20would%20be%20helpful.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks.%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%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-352954%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-353857%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20Failed%20to%20resolve%20scalar%20expression.%20How%20to%20create%20column%20for%20null%20fields%20sent%20from%20our%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353857%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EYou%20can%20do%20this%3C%2FP%3E%0A%3CDIV%20style%3D%22color%3A%20%23000000%3B%20background-color%3A%20%23fffffe%3B%20font-family%3A%20Consolas%2C%20'Courier%20New'%2C%20monospace%3B%20font-weight%3A%20normal%3B%20font-size%3A%2014px%3B%20line-height%3A%2016px%3B%20white-space%3A%20pre%3B%22%3E%0A%3CDIV%3E%0A%3CPRE%3EAzureDiagnostics%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(90d)%20%0A%7C%20project%20columnifexists(%22RecoveryJobDestination_s%22%2C'defaultValue')%20%3C%2FPRE%3E%0AFrom%20the%20example%20you%20can%20see%20how%20it%20will%20be%20for%20the%20other%20columns%20as%20well.%20The%20operator%20documentation%3C%2FDIV%3E%0A%3CDIV%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fcolumnifexists%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fcolumnifexists%3C%2FA%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353846%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20Failed%20to%20resolve%20scalar%20expression.%20How%20to%20create%20column%20for%20null%20fields%20sent%20from%20our%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353846%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%2F9172%22%20target%3D%22_blank%22%3E%40Stanislav%20Zhelyazkov%3C%2FA%3E%20%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20for%20the%20response.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20the%20query%3A%3C%2FP%3E%0A%3CDIV%20style%3D%22color%3A%20%23000000%3B%20background-color%3A%20%23fffffe%3B%20font-family%3A%20Consolas%2C%20'Courier%20New'%2C%20monospace%3B%20font-weight%3A%20normal%3B%20font-size%3A%2014px%3B%20line-height%3A%2016px%3B%20white-space%3A%20pre%3B%22%3E%0A%3CDIV%3E%0A%3CPRE%3EAzureDiagnostics%0A%7C%20where%20Category%20%3D%3D%20%22AzureBackupReport%22%20and%20OperationName%20%3D%3D%20%22Job%22%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(90d)%20%0A%7C%20project%20JobUniqueId_g%2C%20JobOperation_s%2C%20JobStatus_s%2C%20JobFailureCode_s%2C%20JobStartDateTime_s%2C%20JobDurationInSecs_s%2C%20%20JobOperationSubType_s%2C%20TimeGenerated%20%3CBR%20%2F%3E%2F%2F%2C%20RecoveryJobDestination_s%2C%20RecoveryJobRPDateTime_s%2C%20RecoveryJobRPLocation_s%2C%20RecoveryLocationType_s%20%3C%2FPRE%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CDIV%20style%3D%22color%3A%20%23000000%3B%20background-color%3A%20%23fffffe%3B%20font-family%3A%20Consolas%2C%20'Courier%20New'%2C%20monospace%3B%20font-weight%3A%20normal%3B%20font-size%3A%2014px%3B%20line-height%3A%2016px%3B%20white-space%3A%20pre%3B%22%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EIf%20I%20uncomment%20the%20last%20line%2C%20I%20will%20get%20error%20and%20it%20is%20obvious.%20Those%20columns%20were%20not%20created.%3CBR%20%2F%3EColumns%20%3CSPAN%20style%3D%22color%3A%20%23008000%3B%22%3ERecoveryJobDestination_s%2C%20RecoveryJobRPDateTime_s%2C%20RecoveryJobRPLocation_s%2C%20RecoveryLocationType_s%3C%2FSPAN%3E%20are%20not%20created%20%3CBR%20%2F%3Ealthough%20our%20RP%20send%20these%20fields%20as%20null.%20But%20it%20wont%20be%20null%20in%20all%20scenarios.%20In%20Restore%20job%20scenarios%2C%20these%20fields%3C%2FDIV%3E%0A%3CDIV%3Ewill%20be%20having%20string%20values.%20Since%20such%20operation%20is%20quite%20rare%20and%20I%20don't%20have%20those%20jobs%20triggered%20in%20my%20setup%2C%20these%3C%2FDIV%3E%0A%3CDIV%3Efields%20are%20not%20created.%3CBR%20%2F%3E%3CBR%20%2F%3ECould%20you%20please%20tell%20me%20how%20to%20handle%20this%3F%20When%20I%20query%20for%20these%20fields%2C%20I%20need%20to%20get%20null%20(if%20not%20present)%20and%3C%2FDIV%3E%0A%3CDIV%3Ecorresponding%20values%20(if%20present).%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EThanks.%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%20style%3D%22color%3A%20%23008000%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353805%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20Failed%20to%20resolve%20scalar%20expression.%20How%20to%20create%20column%20for%20null%20fields%20sent%20from%20our%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353805%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EIf%20that%20is%20the%20behavior%20of%20the%20API%20I%20do%20not%20think%20there%20is%20a%20way%20to%20avoid%20the%20behavior.%20Nevertheless%20can%20you%20share%20actually%20the%20query%20you%20use%3F%20Many%20things%20can%20be%20achieved%20with%20the%20query%20language%20but%20without%20knowing%20exactly%20what%20you%20use%20and%20trying%20to%20achieve%20it%20is%20hard%20to%20answer%20the%20question.%20Also%20those%20columns%20that%20you%20input%20and%20different%20what%20type%20they%20are%20string%2C%20integer%2C%20etc%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Vino55
Microsoft

Hi,

 

When we ingest data from our RP to LA, there are some fields and we explicitly set null values.

This is not for all scenarios. In other scenario, string values will be filled for those columns.

 

For eg - For Job A, field 1 will be null.

But in case of Job B, field 1 will be having string value. 

Job A is very common and Job B is quite rare.

 

But since my setup has till now only those common jobs, I don't see field 1 created in my LA log search. Though the json we send from our RP explicitly have null values for those fields.

I need to write a generalized query (for all jobs) and print all the job specific fields.

Since the null field was not created yet, I get this error - Failed to resolve scalar expression when I query for that field.

So how to handle this case? Is there a way to let LA know the schema upfront so that null field columns are also created. Any other pointers would be helpful. 

 

Thanks.

 

 

 

 

3 Replies

Hi,

If that is the behavior of the API I do not think there is a way to avoid the behavior. Nevertheless can you share actually the query you use? Many things can be achieved with the query language but without knowing exactly what you use and trying to achieve it is hard to answer the question. Also those columns that you input and different what type they are string, integer, etc?

Hi @Stanislav Zhelyazkov ,

 

Thanks for the response.

 

This is the query:

AzureDiagnostics
| where Category == "AzureBackupReport" and OperationName == "Job"
| where TimeGenerated >= ago(90d) 
| project JobUniqueId_g, JobOperation_s, JobStatus_s, JobFailureCode_s, JobStartDateTime_s, JobDurationInSecs_s,  JobOperationSubType_s, TimeGenerated 
//, RecoveryJobDestination_s, RecoveryJobRPDateTime_s, RecoveryJobRPLocation_s, RecoveryLocationType_s
 
If I uncomment the last line, I will get error and it is obvious. Those columns were not created.
Columns RecoveryJobDestination_s, RecoveryJobRPDateTime_s, RecoveryJobRPLocation_s, RecoveryLocationType_s are not created
although our RP send these fields as null. But it wont be null in all scenarios. In Restore job scenarios, these fields
will be having string values. Since such operation is quite rare and I don't have those jobs triggered in my setup, these
fields are not created.

Could you please tell me how to handle this? When I query for these fields, I need to get null (if not present) and
corresponding values (if present).
 
Thanks.
 
Solution

Hi,

You can do this

AzureDiagnostics
| where TimeGenerated >= ago(90d) 
| project columnifexists("RecoveryJobDestination_s",'defaultValue') 
From the example you can see how it will be for the other columns as well. The operator documentation
 
Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies