Share via

Kusto mapping of json

Bigelman, Oren 106 Reputation points
2021-07-08T12:30:41.257+00:00

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
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
527 questions
Accepted answer
  1. Bigelman, Oren 106 Reputation points
    2021-07-09T19:14:28.94+00:00

    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  
  
}
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. HimanshuSinha-msft 19,476 Reputation points Microsoft Employee
    2021-07-09T22:32:23.233+00:00

    Hello @Bigelman, Oren

    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

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.