Inline Free Text Query

The Free Text Inline Query is useful when handling text data that is not tabular, yet has a structure. Two common examples for such scenarios are log lines and json objects.

The free-text query presents the data as a table with a single string Column containing the input data broken into lines. KQL can be then used to break the text line into individual columns with different types.

Note!

Free Text Processing is not limited to this form of Quick Action. External Data and Temporary Table quick actions will also present this format if triggered on files with an unrecognized structured data suffix such as .txt or .log.

Parsing Free Text into a Table

• Copy the below text logs, then use Klipboard to paste them to a free text query

Hadoop Log Snippet taken from LogApi/LogHub on GitHub

2015-10-18 18:04:50,208 INFO [AsyncDispatcher event handler] org.apache.hadoop.mapreduce.v2.app.job.impl.TaskAttemptImpl: attempt_1445144423722_0020_m_000003_0 TaskAttempt Transitioned from RUNNING to SUCCESS_CONTAINER_CLEANUP
2015-10-18 18:04:50,208 INFO [ContainerLauncher #0] org.apache.hadoop.mapreduce.v2.app.launcher.ContainerLauncherImpl: Processing the event EventType: CONTAINER_REMOTE_CLEANUP for container container_1445144423722_0020_01_000005 taskAttempt attempt_1445144423722_0020_m_000003_0
2015-10-18 18:04:50,208 INFO [ContainerLauncher #0] org.apache.hadoop.mapreduce.v2.app.launcher.ContainerLauncherImpl: KILLING attempt_1445144423722_0020_m_000003_0
2015-10-18 18:04:50,208 INFO [ContainerLauncher #0] org.apache.hadoop.yarn.client.api.impl.ContainerManagementProtocolProxy: Opening proxy : MSRA-SA-41.fareast.corp.microsoft.com:7109
2015-10-18 18:04:50,286 INFO [IPC Server handler 25 on 62270] org.apache.hadoop.mapred.TaskAttemptListenerImpl: Progress of TaskAttempt attempt_1445144423722_0020_m_000002_0 is : 0.3673702
2015-10-18 18:04:50,286 INFO [IPC Server handler 16 on 62270] org.apache.hadoop.mapred.TaskAttemptListenerImpl: Progress of TaskAttempt attempt_1445144423722_0020_m_000009_0 is : 0.667
2015-10-18 18:04:50,755 INFO [AsyncDispatcher event handler] org.apache.hadoop.mapreduce.v2.app.job.impl.TaskAttemptImpl: attempt_1445144423722_0020_m_000003_0 TaskAttempt Transitioned from SUCCESS_CONTAINER_CLEANUP to SUCCEEDED
2015-10-18 18:06:05,825 WARN [LeaseRenewer:msrabi@msra-sa-41:9000] org.apache.hadoop.ipc.Client: Address change detected. Old: msra-sa-41/10.190.173.170:9000 New: msra-sa-41:9000
2015-10-18 18:06:05,825 WARN [LeaseRenewer:msrabi@msra-sa-41:9000] org.apache.hadoop.hdfs.LeaseRenewer: Failed to renew lease for [DFSClient_NONMAPREDUCE_1537864556_1] for 68 seconds.  Will retry shortly ...
2015-10-18 18:06:05,888 INFO [RMCommunicator Allocator] org.apache.hadoop.ipc.Client: Retrying connect to server: msra-sa-41:8030. Already tried 0 time(s); retry policy is RetryUpToMaximumCountWithFixedSleep(maxRetries=10, sleepTime=1000 MILLISECONDS)
2015-10-18 18:06:05,934 ERROR [RMCommunicator Allocator] org.apache.hadoop.mapreduce.v2.app.rm.RMContainerAllocator: ERROR IN CONTACTING RM. 
2015-10-18 18:06:06,169 INFO [IPC Server handler 2 on 62270] org.apache.hadoop.mapred.TaskAttemptListenerImpl: Progress of TaskAttempt attempt_1445144423722_0020_m_000001_0 is : 0.37551183
2015-10-18 18:06:06,294 INFO [IPC Server handler 18 on 62270] org.apache.hadoop.mapred.TaskAttemptListenerImpl: Progress of TaskAttempt attempt_1445144423722_0020_m_000002_0 is : 0.38137424

• Save the sample to a new file with a .log suffix. Copy the file and run the free text query on it.
• In the resulting KQL Query replace the | take 100 KQL line with the below KQL snippet, and run the query again.
• Try filtering in all warning and error lines.

| parse Line with Timestamp:string "," Millisecond:string " " Level:string " [" Prcoess:string "] " Coponent:string ": " Content:string
| extend Timestamp = strcat(Timestamp, ".", Millisecond)
| extend Timestamp = todatetime(Timestamp)
| project-away Line, Millisecond

Parsing JSON data to a Table

Here's the same logs reformatted into json lines format.

• Copy the below text logs, then use Klipboard to paste them to a free text query

{"Timestamp":"2015-10-18T18:04:50.2080000Z","Level":"INFO","Prcoess":"AsyncDispatcher event handler","Coponent":"org.apache.hadoop.mapreduce.v2.app.job.impl.TaskAttemptImpl","Content":"attempt_1445144423722_0020_m_000003_0 TaskAttempt Transitioned from RUNNING to SUCCESS_CONTAINER_CLEANUP"}
{"Timestamp":"2015-10-18T18:04:50.2080000Z","Level":"INFO","Prcoess":"ContainerLauncher #0","Coponent":"org.apache.hadoop.mapreduce.v2.app.launcher.ContainerLauncherImpl","Content":"Processing the event EventType: CONTAINER_REMOTE_CLEANUP for container container_1445144423722_0020_01_000005 taskAttempt attempt_1445144423722_0020_m_000003_0"}
{"Timestamp":"2015-10-18T18:04:50.2080000Z","Level":"INFO","Prcoess":"ContainerLauncher #0","Coponent":"org.apache.hadoop.mapreduce.v2.app.launcher.ContainerLauncherImpl","Content":"KILLING attempt_1445144423722_0020_m_000003_0"}
{"Timestamp":"2015-10-18T18:04:50.2080000Z","Level":"INFO","Prcoess":"ContainerLauncher #0","Coponent":"org.apache.hadoop.yarn.client.api.impl.ContainerManagementProtocolProxy","Content":"Opening proxy : MSRA-SA-41.fareast.corp.microsoft.com:7109"}
{"Timestamp":"2015-10-18T18:04:50.2860000Z","Level":"INFO","Prcoess":"IPC Server handler 25 on 62270","Coponent":"org.apache.hadoop.mapred.TaskAttemptListenerImpl","Content":"Progress of TaskAttempt attempt_1445144423722_0020_m_000002_0 is : 0.3673702"}
{"Timestamp":"2015-10-18T18:04:50.2860000Z","Level":"INFO","Prcoess":"IPC Server handler 16 on 62270","Coponent":"org.apache.hadoop.mapred.TaskAttemptListenerImpl","Content":"Progress of TaskAttempt attempt_1445144423722_0020_m_000009_0 is : 0.667"}
{"Timestamp":"2015-10-18T18:04:50.7550000Z","Level":"INFO","Prcoess":"AsyncDispatcher event handler","Coponent":"org.apache.hadoop.mapreduce.v2.app.job.impl.TaskAttemptImpl","Content":"attempt_1445144423722_0020_m_000003_0 TaskAttempt Transitioned from SUCCESS_CONTAINER_CLEANUP to SUCCEEDED"}
{"Timestamp":"2015-10-18T18:06:05.8250000Z","Level":"WARN","Prcoess":"LeaseRenewer:msrabi@msra-sa-41:9000","Coponent":"org.apache.hadoop.ipc.Client","Content":"Address change detected. Old: msra-sa-41/10.190.173.170:9000 New: msra-sa-41:9000"}
{"Timestamp":"2015-10-18T18:06:05.8250000Z","Level":"WARN","Prcoess":"LeaseRenewer:msrabi@msra-sa-41:9000","Coponent":"org.apache.hadoop.hdfs.LeaseRenewer","Content":"Failed to renew lease for [DFSClient_NONMAPREDUCE_1537864556_1] for 68 seconds.  Will retry shortly ..."}
{"Timestamp":"2015-10-18T18:06:05.8880000Z","Level":"INFO","Prcoess":"RMCommunicator Allocator","Coponent":"org.apache.hadoop.ipc.Client","Content":"Retrying connect to server: msra-sa-41:8030. Already tried 0 time(s); retry policy is RetryUpToMaximumCountWithFixedSleep(maxRetries=10, sleepTime=1000 MILLISECONDS)"}
{"Timestamp":"2015-10-18T18:06:05.9340000Z","Level":"ERROR","Prcoess":"RMCommunicator Allocator","Coponent":"org.apache.hadoop.mapreduce.v2.app.rm.RMContainerAllocator","Content":"ERROR IN CONTACTING RM. "}
{"Timestamp":"2015-10-18T18:06:06.1690000Z","Level":"INFO","Prcoess":"IPC Server handler 2 on 62270","Coponent":"org.apache.hadoop.mapred.TaskAttemptListenerImpl","Content":"Progress of TaskAttempt attempt_1445144423722_0020_m_000001_0 is : 0.37551183"}
{"Timestamp":"2015-10-18T18:06:06.2940000Z","Level":"INFO","Prcoess":"IPC Server handler 18 on 62270","Coponent":"org.apache.hadoop.mapred.TaskAttemptListenerImpl","Content":"Progress of TaskAttempt attempt_1445144423722_0020_m_000002_0 is : 0.38137424"}

• In the resulting KQL Query replace the | take 100 KQL line with the below KQL snippet, and run the query again.
• Try filtering in all warning and error lines.

| extend Line=todynamic(Line)
| evaluate bag_unpack(Line)

Set a Predefined Free Text Parsing Macro

You can configure Klipboard to automatically append free-text quick action queries with a predefined KQL macro. This is useful when working with a text or files with a common structure.

• Create and test your macro KQL
• Open Settings
• Paste the KQL suffix into the Append KQL to Free Text Queries text box
• Click Save

Related Topics

• Inline Query
• External Data Query
• Temporary Table Query