question

BigelmanOren-7120 avatar image
0 Votes"
BigelmanOren-7120 asked HimanshuSinha-MSFT commented

Kusto mapping of json

I am getting syntax error when running the following mapping command:

Failed to create mapping: Syntax error: . Query: '.create table AnswerXLogsV2 ingestion json mapping 'AX_JSON_Mapping' '[
{"column":"ts","Properties":{"path":"$.ts"}},
{"column":"uuid","Properties":{"path":"$.uuid"}},
{"column":"opcode","Properties":{"path":"$.opcode"}},
{"column":"response_code","Properties":{"path":"$.response_rcode"}},
{"column":"dns_server_ip","Properties":{"path":"$.dns_server_ip"}},
{"column":"user_ip","Properties":{"path":"$.user_ip"}},
{"column":"user_port","Properties":{"path":"$.user_port"}},
{"column":"customer","Properties":{"path":"$.customer"}},
{"column":"client_subnet_address","Properties":{"path":"$.client_subnet_address"}},
{"column":"client_subnet_source_netmask","Properties":{"path":"$.client_subnet_source_netmask"}},
{"column":"client_subnet_scope_netmask","Properties":{"path":"$.client_subnet_scope_netmask"}},
{"column":"acgid","Properties":{"path":"$.acgid"}},
{"column":"machineip","Properties":{"path":"$.machineip"}},
{"column":"backend","Properties":{"path":"$.backend"}},
{"column":"region","Properties":{"path":"$.region"}},
{"column":"qname","Properties":{"path":"$.qname"}},
{"column":"qclass","Properties":{"path":"$.qclass"}},
{"column":"request_ts","Properties":{"path":"$.request_ts"}},
{"column":"response_size","Properties":{"path":"$.response_size"}},
{"column":"response_protocol","Properties":{"path":"$.response_protocol"}},
{"column":"nx_domain","Properties":{"path":"$.nx_domain"}},
{"column":"num_answer_records","Properties":{"path":"$.num_answer_records"}},
{"column":"num_authority_records","Properties":{"path":"$.num_authority_records"}},
{"column":"num_additional_records","Properties":{"path":"$.num_additional_records"}},
{"column":"answer","Properties":{"path":"$.answer"}},
{"column":"customer_name","Properties":{"path":"$.customer_name"}},
{"column":"customer_status","Properties":{"path":"$.customer_status"}},
{"column":"config_id","Properties":{"path":"$.config_id"}},
{"column":"site_id","Properties":{"path":"$.site_id"}},
{"column":"policy_id","Properties":{"path":"$.policy_id"}},
{"column":"payload_ver","Properties":{"path":"$.payload_ver"}},
{"column":"is_alert","Properties":{"path":"$.is_alert"}},
{"column":"list_id","Properties":{"path":"$.list_id"}},
{"column":"category_id","Properties":{"path":"$.category_id"}},
{"column":"confidence_id","Properties":{"path":"$.confidence_id"}},
{"column":"action_id","Properties":{"path":"$.action_id"}},
{"column":"observed_ip_lists","Properties":{"path":"$.observed_ip_lists"}},
{"column":"observed_domain_lists","Properties":{"path":"$.observed_domain_lists"}},
{"column":"observed_aup_categories","Properties":{"path":"$.observed_aup_categories"}},
{"column":"listed_ip_address","Properties":{"path":"$.listed_ip_address"}},
{"column":"listed_security_domain_name","Properties":{"path":"$.listed_security_domain_name"}},
{"column":"listed_aup_domain_name","Properties":{"path":"$.listed_aup_domain_name"}},
{"column":"error","Properties":{"path":"$.error"}},
{"column":"event_type","Properties":{"path":"$.event_type"}},
{"column":"event_trigger","Properties":{"path":"$.event_trigger"}},
{"column":"policy_list_alert_bits","Properties":{"path":"$.policy_list_alert_bits"}},
{"column":"policy_return_code","Properties":{"path":"$.policy_return_code"}},
{"column":"client_token_enc","Properties":{"path":"$.client_token_enc"}},
{"column":"client_token_dec","Properties":{"path":"$.client_token_dec"}},
{"column":"sinkhole_id","Properties":{"path":"$.sinkhole_id"}},
{"column":"onramp","Properties":{"path":"$.onramp"}},
{"column":"threat_id","Properties":{"path":"$.threat_id"}},
{"column":"severity_id","Properties":{"path":"$.severity_id"}},
{"column":"onramp_type","Properties":{"path":"$.onramp_type"}},
{"column":"connection_id","Properties":{"path":"$.connection_id"}},
{"column":"policy_evaluation_source","Properties":{"path":"$.policy_evaluation_source"}},
{"column":"client_request_id","Properties":{"path":"$.client_request_id"}},
{"column":"internal_client_ip","Properties":{"path":"$.internal_client_ip"}},
{"column":"internal_hostname","Properties":{"path":"$.internal_hostname"}},
{"column":"aup_source","Properties":{"path":"$.aup_source"}},
{"column":"transport_type","Properties":{"path":"$.transport_type"}},
{"column":"sc_id","Properties":{"path":"$.sc_id"}},
{"column":"encrypted_machine_info","Properties":{"path":"$.encrypted_machine_info"}},
{"column":"encr_detail","Properties":{"path":"$.encr_detail"}},
{"column":"encrypted_internal_client_ip","Properties":{"path":"$.encrypted_internal_client_ip"}},
{"column":"encrypted_internal_hostname","Properties":{"path":"$.encrypted_internal_hostname"}},
{"column":"catalog_id","Properties":{"path":"$.catalog_id"}},
{"column":"application_id","Properties":{"path":"$.application_id"}},
{"column":"risk_id","Properties":{"path":"$.risk_id"}},
{"column":"avc_policy_action","Properties":{"path":"$.avc_policy_action"}}
]'', ActivityId='ac1aa02f-b0d1-4f81-b4a9-cbc842ee9440'


Can I get some guidance how to write the command correctly ? (I am using java for the execution)

Thanks

azure-data-explorer
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @BigelmanOren-7120 ,

Thanks for the ask and using the Microsoft Q&A platform .
Can you please share the java code which is throwing the error , that will help us to debug the issue .

Thanks
Himanshu

0 Votes 0 ·

@HimanshuSinha-MSFT see my code in bellow answer.
FYI, "Submit" button doesn't work with text + code block in the reply thread. Had to write it as a new answer

1 Vote 1 ·
BigelmanOren-7120 avatar image
0 Votes"
BigelmanOren-7120 answered

Hi, @HimanshuSinha-MSFT
Calling following code with:
AdxClient.createMapping("etp_qa", AdxClient.createAxMappingCommand)

 import com.microsoft.azure.kusto.data.auth.ConnectionStringBuilder
 import com.microsoft.azure.kusto.data.{Client, ClientFactory, ClientRequestProperties, KustoOperationResult}
    
 object AdxClient {
   val cluster = "https://etpkusto.eastus.kusto.windows.net"
   val clientId = "xxxxx"
   val clientSecret = "yyyy"
   val authorityId = "zzz"
   val table = "etp_db"
   val adxClient: Client = getAdxClient
   val mappingRef = "AX_JSON_Mapping"
    
   @throws[Exception]
   private[azure] def getAdxClient = {
     val csb = ConnectionStringBuilder.createWithAadApplicationCredentials(cluster, clientId, clientSecret, authorityId)
     ClientFactory.createClient(csb)
   }
    
   def createMapping(database: String, mappingCommand: String): Unit = {
     try getAdxClient.execute(database, mappingCommand)
     catch {
       case e: Exception =>
         System.out.println("Failed to create mapping: " + e.getMessage)
         return
     }
     System.out.println("Mapping created")
   }
    
   val createAxMappingCommand: String = s""".create table AnswerXLogsV2 ingestion json mapping '$mappingRef' """ +
    """'[
       |{"column":"ts","Properties":{"path":"$.ts"}},
       |{"column":"uuid","Properties":{"path":"$.uuid"}},
       |{"column":"opcode","Properties":{"path":"$.opcode"}},
       |{"column":"response_code","Properties":{"path":"$.response_rcode"}},
       |{"column":"dns_server_ip","Properties":{"path":"$.dns_server_ip"}},
       |{"column":"user_ip","Properties":{"path":"$.user_ip"}},
       |{"column":"user_port","Properties":{"path":"$.user_port"}},
       |{"column":"customer","Properties":{"path":"$.customer"}},
       |{"column":"client_subnet_address","Properties":{"path":"$.client_subnet_address"}},
       |{"column":"client_subnet_source_netmask","Properties":{"path":"$.client_subnet_source_netmask"}},
       |{"column":"client_subnet_scope_netmask","Properties":{"path":"$.client_subnet_scope_netmask"}},
       |{"column":"acgid","Properties":{"path":"$.acgid"}},
       |{"column":"machineip","Properties":{"path":"$.machineip"}},
       |{"column":"backend","Properties":{"path":"$.backend"}},
       |{"column":"region","Properties":{"path":"$.region"}},
       |{"column":"qname","Properties":{"path":"$.qname"}},
       |{"column":"qclass","Properties":{"path":"$.qclass"}},
       |{"column":"request_ts","Properties":{"path":"$.request_ts"}},
       |{"column":"response_size","Properties":{"path":"$.response_size"}},
       |{"column":"response_protocol","Properties":{"path":"$.response_protocol"}},
       |{"column":"nx_domain","Properties":{"path":"$.nx_domain"}},
       |{"column":"num_answer_records","Properties":{"path":"$.num_answer_records"}},
       |{"column":"num_authority_records","Properties":{"path":"$.num_authority_records"}},
       |{"column":"num_additional_records","Properties":{"path":"$.num_additional_records"}},
       |{"column":"answer","Properties":{"path":"$.answer"}},
       |{"column":"customer_name","Properties":{"path":"$.customer_name"}},
       |{"column":"customer_status","Properties":{"path":"$.customer_status"}},
       |{"column":"config_id","Properties":{"path":"$.config_id"}},
       |{"column":"site_id","Properties":{"path":"$.site_id"}},
       |{"column":"policy_id","Properties":{"path":"$.policy_id"}},
       |{"column":"payload_ver","Properties":{"path":"$.payload_ver"}},
       |{"column":"is_alert","Properties":{"path":"$.is_alert"}},
       |{"column":"list_id","Properties":{"path":"$.list_id"}},
       |{"column":"category_id","Properties":{"path":"$.category_id"}},
       |{"column":"confidence_id","Properties":{"path":"$.confidence_id"}},
       |{"column":"action_id","Properties":{"path":"$.action_id"}},
       |{"column":"observed_ip_lists","Properties":{"path":"$.observed_ip_lists"}},
       |{"column":"observed_domain_lists","Properties":{"path":"$.observed_domain_lists"}},
       |{"column":"observed_aup_categories","Properties":{"path":"$.observed_aup_categories"}},
       |{"column":"listed_ip_address","Properties":{"path":"$.listed_ip_address"}},
       |{"column":"listed_security_domain_name","Properties":{"path":"$.listed_security_domain_name"}},
       |{"column":"listed_aup_domain_name","Properties":{"path":"$.listed_aup_domain_name"}},
       |{"column":"error","Properties":{"path":"$.error"}},
       |{"column":"event_type","Properties":{"path":"$.event_type"}},
       |{"column":"event_trigger","Properties":{"path":"$.event_trigger"}},
       |{"column":"policy_list_alert_bits","Properties":{"path":"$.policy_list_alert_bits"}},
       |{"column":"policy_return_code","Properties":{"path":"$.policy_return_code"}},
       |{"column":"client_token_enc","Properties":{"path":"$.client_token_enc"}},
       |{"column":"client_token_dec","Properties":{"path":"$.client_token_dec"}},
       |{"column":"sinkhole_id","Properties":{"path":"$.sinkhole_id"}},
       |{"column":"onramp","Properties":{"path":"$.onramp"}},
       |{"column":"threat_id","Properties":{"path":"$.threat_id"}},
       |{"column":"severity_id","Properties":{"path":"$.severity_id"}},
       |{"column":"onramp_type","Properties":{"path":"$.onramp_type"}},
       |{"column":"connection_id","Properties":{"path":"$.connection_id"}},
       |{"column":"policy_evaluation_source","Properties":{"path":"$.policy_evaluation_source"}},
       |{"column":"client_request_id","Properties":{"path":"$.client_request_id"}},
       |{"column":"internal_client_ip","Properties":{"path":"$.internal_client_ip"}},
       |{"column":"internal_hostname","Properties":{"path":"$.internal_hostname"}},
       |{"column":"aup_source","Properties":{"path":"$.aup_source"}},
       |{"column":"transport_type","Properties":{"path":"$.transport_type"}},
       |{"column":"sc_id","Properties":{"path":"$.sc_id"}},
       |{"column":"encrypted_machine_info","Properties":{"path":"$.encrypted_machine_info"}},
       |{"column":"encr_detail","Properties":{"path":"$.encr_detail"}},
       |{"column":"encrypted_internal_client_ip","Properties":{"path":"$.encrypted_internal_client_ip"}},
       |{"column":"encrypted_internal_hostname","Properties":{"path":"$.encrypted_internal_hostname"}},
       |{"column":"catalog_id","Properties":{"path":"$.catalog_id"}},
       |{"column":"application_id","Properties":{"path":"$.application_id"}},
       |{"column":"risk_id","Properties":{"path":"$.risk_id"}},
       |{"column":"avc_policy_action","Properties":{"path":"$.avc_policy_action"}}
       |]'""".stripMargin
    
 }

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered HimanshuSinha-MSFT commented

Hello @BigelmanOren-7120

Seems like you are interested in using multiline string literals – check if adjusting to using 3 backticks (```) instead of single quotes(') works

 val createAxMappingCommand: String = s""".create table AnswerXLogsV2 ingestion json mapping '$mappingRef' """ +
  """'[
     |{"column":"ts","Properties":{"path":"$.ts"}},
     |{"column":"uuid","Properties":{"path":"$.uuid"}},
     |{"column":"risk_id","Properties":{"path":"$.risk_id"}},



     |]'""".stripMargin


Thanks
Himanshu


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes Thanks,
That did the trick

1 Vote 1 ·

Hello @BigelmanOren-7120 ,
It was great to know that you were able to get to a resolution . We expect you to keep using this forum and also motivate others to do that same . You can always help other community members by answering to their queries .
Thanks
Himanshu

0 Votes 0 ·