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

[Vino55]

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.

[#2]

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?

[#3]

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.

[#4 · Accepted 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

https://docs.microsoft.com/en-us/azure/kusto/query/columnifexists