microsoft / k2bridge Goto Github PK

View Code? Open in Web Editor NEW

58.0 19.0 14.0 5.42 MB

Enables Kibana to query Azure Data Explorer (ADX / Kusto)

License: Other

Dockerfile 0.52% C# 97.77% HCL 0.91% Mustache 0.12% Shell 0.68%

adx kusto adx-cluster

k2bridge's Introduction

K2Bridge

K2Bridge is a solution that enables Kibana to use Azure Data Explorer (ADX, or codename Kusto) as its backend database.

⚡ Breaking change notice !

This is a new version that adds support for visualization and for Kibana 7. To avoid a breaking change, we are changing the way we tag our images. The new images tags are: 6.8_latest and 7.16_latest, support Kibana 6.8 and Kibana 7.10 respectively. The image of '7.16_latest' supports Kibana OSS 7.10.2, and its internal Elasticsearch instance is 7.16.2. Action item: Use the provided helm chart to update the deployment with the new image tags.

Description

The K2Bridge solution is a proxy capable of communicating with the Kibana application and translate its queries to KQL, the query language of the Azure Data Explorer service. The solution currently targets the "Discover", "Visualzie" and "Dashboard" tabs in Kibana to enable users to quickly and interactively explore their data. It supports the filters and well as the search box in the screen with both simple term search and Lucene expressions.

How does it work

The K2Bridge is the endpoint exposed to clients and the one Kibana connects to. Internally, a small elasticsearch is being used to service metadata related requests (index-patterns, saved queries etc.). Note that no business data is actually saved in this internal instance and it can be considered as an implementation detail (could be removed in the future). The bridge accept each request and redirects business (data) requests to ADX and metadata requests to the metadata store.

Some differences to be aware of

The searching documentation provides insights to the similarities and differences between Elasticsearch and ADX as Kibana data sources.
Each document in Elasticsearch has a unique id usually noted in the "_id" field. This isn't inherently available for data stored in ADX and because Kibana expects it, K2Bridge generates a random number for this value. Please note that this is not a reproducible value and you shouldn't search for documents/items that have specific values.
We have used and tested the solution using Kibana OSS 7.10.2.

Installing

K2Bridge deploys to Kubernetes. Instructions are available here.

Connecting data

The application settings contains the credentials for a service principal, a reference to an ADX cluster and a default database within the cluster (adxDefaultDatabaseName).

The application surfaces the following data from ADX as indexes into Kibana:

Tables located in any database on the ADX cluster, regardless of the adxDefaultDatabaseName setting, provided the service principal has Viewer permissions on the table.
Functions located in the adxDefaultDatabaseName database only, provided:
- The service principal has Viewer permissions on the function.
- The function does not take any parameters.

ADX functions without parameters are similar in nature to views in relational databases. Through functions, you can perform preform calculations, joins as well as cross-database and cross-cluster queries and queries into Azure Monitor (Application Insights and Log Analytics), provided the service principal has adequate permissions on the external resources. For example:

.create function MyAzureMonitorConnectionFunction() {
    cluster('https://ade.loganalytics.io/subscriptions/<subscription-id>/resourceGroups/<resource-group-name>/providers/Microsoft.OperationalInsights/workspaces/<workspace-name>')
    .database('<workspace-name>')
    .['<tablename>']
}

Make sure you grant access to the service principal (that you created as part of the installation requirements) from your Log Analytics workspaces. To do so, go to your Log Analytics resource on the Azure portal, click on Access Control (IAM) and then Add. Click "Add role assignment", set the Role to Reader, and select the service principal created earlier.

Be mindful of the performance impact of such distributed queries, which can easily result into Kibana timeouts.

Prometheus Support

K2Bridge supports the Prometheus protocol for metrics reporting (like request time, query time and payload size). Supported exposition formats are the 0.0.4 text and protocol buffer formats.

More on the formats can be found at the Prometheus documentations.

K2Bridge would reply based on the content type header, so pointing your browser to: http://bridge-host/metrics/ will return a text representation of the metrics with their documentation.

Performance

You can find more about the performance test and capabilities in the Performance page.

Developing

Information on how to run Kibana and K2Bridge locally for development and testing can be found here.

Data Collection

The software may collect information about you and your use of the software and send it to Microsoft. Microsoft may use this information to provide services and improve our products and services. You may turn off the telemetry as described in the repository. There are also some features in the software that may enable you and Microsoft to collect data from users of your applications. If you use these features, you must comply with applicable law, including providing appropriate notices to users of your applications together with a copy of Microsoft's privacy statement. Our privacy statement is located at https://go.microsoft.com/fwlink/?LinkID=824704. You can learn more about data collection and use in the help documentation and our privacy statement. Your use of the software operates as your consent to these practices.

Data collection may be disabled by installing the K2 helm chart by setting the collectTelemetry field to false. e.g: '--set settings.collectTelemetry=false'

Attribution

Elasticsearch is a trademark of Elasticsearch BV, registered in the U.S. and in other countries. Kibana is a trademark of Elasticsearch BV, registered in the U.S. and in other countries.

Contributing

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.

When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.

k2bridge's People

Contributors

Stargazers

Watchers

Forkers

guregini shirazlavi grawcho taffywrinkle claudiusgonzo punnam hamzahamidi asafmah shirazlavi-msft dupuyjs cifuab kustonaut geekswagg gar0t0

k2bridge's Issues

Add support for buckets sub aggregations (split series)

Provide a solution to split an existing primary aggregation using another one (for instance, following terms aggregation is split again using month date histogram, so each carrier bucket provide a count metric for each month).

  "aggs": {
    "3": {
      "terms": {
        "field": "Carrier",
        "order": {
          "_key": "desc"
        },
        "size": 5
      },
      "aggs": {
        "4": {
          "date_histogram": {
            "field": "timestamp",
            "calendar_interval": "1M",
            "time_zone": "Europe/Paris",
            "min_doc_count": 1
          }
        }
      }
    }
  },

The concept is to apply a new query on each individual bucket produced by Terms aggregation. Need some additional investigation but we can probably reuse the work done on TopHits with BuildPartitionQuery.

Remove metadata from column names

In pull request #134, metadata table had been introduced to fix missing buckets. I suggest to use the same table to move remaining metadata used in column names.

This suggestion will be applied on:

Metric aggregations (Percentiles, Top Hits, Extended Stats)
Date Range (not covered by the PR mentioned before).

Add Support for `IngressClassName`

Above TLS in ingress.yaml add input for ingressClassName: ${{ .Values.ingress.className }} so ingress controller will pick up ingress and assign LB IP to it.

Installation doc: namespace k2bridge

In the installation doc (https://github.com/microsoft/K2Bridge/blob/master/docs/installation.md), both of the helm commands use the switch -n k2bridge. The doc did NOT mention that such namespace should exist. One either has to create such namespace before running the helm commands or face failure.

Two questions:

It does not seem to be necessary to use a specific namespace instead of the default. Is this the right understanding?
Regardless whether the namespace is necessary, the installation doc changes seems to be necessary?
I added the namespace and proceeded, Kibana portal is up and running but failed to create index pattern with an "internal server error":

An internal server error occurred
Error: An internal server error occurred
at http://20.85.26.253:5601/bundles/commons.bundle.js:1:1203245
at processQueue (http://20.85.26.253:5601/built_assets/dlls/vendors.bundle.dll.js:307:199687)
at http://20.85.26.253:5601/built_assets/dlls/vendors.bundle.dll.js:307:200650
at Scope.$digest (http://20.85.26.253:5601/built_assets/dlls/vendors.bundle.dll.js:307:210412)
at Scope.$apply (http://20.85.26.253:5601/built_assets/dlls/vendors.bundle.dll.js:307:213219)
at done (http://20.85.26.253:5601/built_assets/dlls/vendors.bundle.dll.js:307:132717)
at completeRequest (http://20.85.26.253:5601/built_assets/dlls/vendors.bundle.dll.js:307:136329)
at XMLHttpRequest.requestLoaded (http://20.85.26.253:5601/built_assets/dlls/vendors.bundle.dll.js:307:135225)

Implement support of Percentile Ranks aggregation

Percentile ranks aggregation

A multi-value metrics aggregation that calculates one or more percentile ranks over numeric values extracted from the aggregated documents. These values can be generated by a provided script or extracted from specific numeric or histogram fields in the documents.

Please see Percentiles are (usually) approximate and Compression for advice regarding approximation and memory use of the percentile ranks aggregation

Percentile rank show the percentage of observed values which are below certain value. For example, if a value is greater than or equal to 95% of the observed values it is said to be at the 95th percentile rank.

https://www.elastic.co/guide/en/elasticsearch/reference/7.10/search-aggregations-metrics-percentile-rank-aggregation.html

Add support for IPV4 range

Add support for IPV4 Range

Add a dedicated range aggregation for IP typed fields

"aggs": {
    "ip_ranges": {
      "ip_range": {
        "field": "ip",
        "ranges": [
          { "to": "10.0.0.5" },
          { "from": "10.0.0.5" }
        ]
      }
    }
  }

Response:

{
  ...

  "aggregations": {
    "ip_ranges": {
      "buckets": [
        {
          "key": "*-10.0.0.5",
          "to": "10.0.0.5",
          "doc_count": 10
        },
        {
          "key": "10.0.0.5-*",
          "from": "10.0.0.5",
          "doc_count": 260
        }
      ]
    }
  }
}

Reference : IP range aggregation

Useful Kusto functions:

Unable to create view/table with dashes

Hi. In our work we use a lot of index patterns with dashes, i.e. 'dev-sre'.
Because of the way Kusto works, you can't directly create view 'dev-sre', you have to create it as ['dev-sre'].
Even when you create view like that, Kusto Kibana can't use it then, returning an exception:

//creating a functions
.create function ['abc-dev'] { print 1}

//making sure it works
['abc-dev']
| take 10

Then you add it to index patterns as usual, and when you try to view it:

K2 Exception:

K2Bridge.KustoDAL.QueryException: Failed executing azure data explorer query
 ---> [0]Kusto.Data.Exceptions.SemanticException: Semantic error: 'abc-dev | getschema | project ColumnName, ColumnType=DataType' has the following semantic error: Scalar is not expected in the current context.
Timestamp=2020-03-17T07:32:39.5549754Z
ClientRequestId=K2Bridge.ExecuteQuery;6950ffc7-1a5c-4056-9288-31abe0f02abf
ActivityId=7a7e529a-077b-4dc3-abb6-6c806149a0b8
ActivityType=DN.RestClient.ExecuteQuery
MachineName=k2bridge-5c4597fb58-nqbk9
ProcessName=dotnet
ProcessId=1
ThreadId=14559
AppDomainName=K2Bridge
ActivityStack=(Activity stack: CRID=K2Bridge.ExecuteQuery;6950ffc7-1a5c-4056-9288-31abe0f02abf ARID=7a7e529a-077b-4dc3-abb6-6c806149a0b8 > DN.RestClient.ExecuteQuery/7a7e529a-077b-4dc3-abb6-6c806149a0b8)
MonitoredActivityContext=ActivityType=DN.RestClient.ExecuteQuery, Timestamp=2020-03-17T07:32:39.5403952Z, ParentActivityId=7a7e529a-077b-4dc3-abb6-6c806149a0b8, TimeSinceStarted=14.6528 [ms]

ErrorCode=SEM0062
ErrorReason=BadRequest
ErrorMessage=Scalar is not expected in the current context
DataSource=
DatabaseName=log
ClientRequestId=K2Bridge.ExecuteQuery;6950ffc7-1a5c-4056-9288-31abe0f02abf
ActivityId=7005969f-0bb0-4859-a4a9-d262371efe54
Text=abc-dev | getschema | project ColumnName, ColumnType=DataType
SemanticErrors=Scalar is not expected in the current context
   at Kusto.Cloud.Platform.Http.KustoHttpClient.ThrowKustoExceptionFromResponseMessageAsync(KustoExceptionContext exceptionContext, HttpResponseMessage responseMessage, ClientRequestProperties properties, Boolean shouldBuffer, Action`2 notify)
   at Kusto.Data.Net.Client.RestClient2.MakeHttpRequestAsyncImpl(String address, String csl, String ns, String databaseName, Boolean streaming, ClientRequestProperties properties, ServiceModelTimeoutKind timeoutKind, String clientRequestId, Stream body, StreamProperties streamProperties)
   at Kusto.Cloud.Platform.Utils.MonitoredActivity.InvokeAsync[TActivityType,TResult](TActivityType activityType, Func`1 func, String clientRequestId)
   at Kusto.Cloud.Platform.Utils.MonitoredActivity.InvokeAsync[TActivityType,TResult](TActivityType activityType, Func`1 func, String clientRequestId)
   at Kusto.Data.Net.Client.RestClient2.MakeHttpRequestAsync(ActivityType activityType, String baseAddress, String relativeAddress, String clientRequestIdPrefix, String ns, String databaseName, String csl, String addr, Boolean streaming, ClientRequestProperties properties, ServiceModelTimeoutKind timeoutKind, StreamProperties streamProperties)
   at Kusto.Data.Net.Client.RestClient2.ExecuteQueryAsync(String databaseName, String query, ClientRequestProperties properties)
   at K2Bridge.KustoDAL.CslQueryProviderExtensions.ExecuteMonitoredQueryAsync(ICslQueryProvider client, String query, ClientRequestProperties clientRequestProperties, Metrics metrics) in /app/K2Bridge/KustoDAL/CslQueryProviderExtensions.cs:line 50
   at K2Bridge.KustoDAL.KustoQueryExecutor.ExecuteQueryAsync(QueryData queryData, RequestContext requestContext) in /app/K2Bridge/KustoDAL/KustoQueryExecutor.cs:line 117
   --- End of inner exception stack trace ---
   at K2Bridge.KustoDAL.KustoQueryExecutor.ExecuteQueryAsync(QueryData queryData, RequestContext requestContext) in /app/K2Bridge/KustoDAL/KustoQueryExecutor.cs:line 117
   at K2Bridge.KustoDAL.KustoDataAccess.GetFieldCapsAsync(String indexName) in /app/K2Bridge/KustoDAL/KustoDataAccess.cs:line 76
   at K2Bridge.Controllers.FieldCapabilityController.Process(String indexName) in /app/K2Bridge/Controllers/FieldCapabilityController.cs:line 57
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Prometheus.HttpMetrics.HttpRequestDurationMiddleware.Invoke(HttpContext context)
   at Prometheus.HttpMetrics.HttpRequestCountMiddleware.Invoke(HttpContext context)
   at Prometheus.HttpMetrics.HttpInProgressMiddleware.Invoke(HttpContext context)
   at Serilog.AspNetCore.RequestLoggingMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)

Add support for Kibana 7

K2Bridge currently only supports Kibana 6. We want to support Kibana 7 (and Elasticsearch 7).

Identified gaps:

minor changes in _msearch, already resolved in branch spike/kibana7
Kibana 7 sends data retrieval searches (Discover tab) to _search rather than _msearch by default. In K2Bridge data retrieval is only implemented at the _msearch endpoint.
- Issue #8 would potentially solve this by implementing data retrieval in the _search endpoint.
- A partial workaround is to enable the courier:batchSearches option under Kibana Advanced settings, but that results in frequent UI errors related to the "timed_out" property. The cause for this seems to be that K2Bridge only returns results for the first request in an ndjson document.

In addition:

need to update the build pipeline to perform parallel test of ES 6 and ES7. Already resolved in branch spike/kibana7

View Single Document

As a Kibana User, I would like to "View Single Document" option.

Upgrade to Elastic newer version or latest 7.17

Hi,

It's possible to use the newes version of Elasticsearch "eck-elasticsearch" or use the latest version 7 (7.17.3) ?

Anyone deploy that in the latest version?

Best Regards
gar0t0

Filter aggregation fails when there are no matches

This bug is about the visualization part.
Reproduction:

create a new visualization graph
select filter aggregation (x axis)
select multiple values, that do no exists, so the results will be blank
it will fail since the generate KustoQL is not generating buckets when nothing is returned.

see complete thread here:
#114 (comment)

Document use of ingress in chart

Helm chart has an ingress flag, but it's not obvious how to use it; extra parameters must be set as well.

Syntax seems to be

 --set 'ingress.hosts[0].host=<HOST>' --set 'ingress.hosts[0].paths[0]=/'

Add support for dynamic columns

Dynamic columns, meaning json objects stored in ADX are partially supported.
One can view and search them by referencing properties directly in the search box, but cannot use UI features such as filtering

Lucene text range searches not supported yet

text range searches such as : title:{Aida TO Carmen}
are not supported currently

Convert Visitor partial class to async/await

We need to convert the Visitor class method to be async to support:

K2Bridge/Visitors/RangeClauseVisitor.cs

var t = ClauseFieldTypeProcessor.GetType(schemaRetriever, rangeClause.FieldName).Result;

Lucene highlighter failed when filter by text with `{}` symbols

Lucene highlighter failed when filter by text with { } symbols.

I am trying to use filter and it filters as expected:

{
  "query": {
    "match_phrase": {
      "Payload.@mt": "Lucene highlighter is enabled: {isHighlight}"
    }
  }
}

But Kibana did not highlight text and I can see next errors at K2Bridge log:

  | Payload.@l | Error
  | Payload.@mt | Failure creating highlighters for {value}
  | Payload.@t | Jan 19, 2022 @ 15:04:25.122
  | Payload.@x | Lucene.Net.QueryParsers.ParseException: Cannot parse 'Lucene highlighter is enabled: {isHighlight}': Encountered " "}" "} "" at line 1, column 43. Was expecting one of:     "TO" ...     <RANGEEX_QUOTED> ...     <RANGEEX_GOOP> ...       ---> Lucene.Net.QueryParsers.ParseException: Encountered " "}" "} "" at line 1, column 43. Was expecting one of:     "TO" ...     <RANGEEX_QUOTED> ...     <RANGEEX_GOOP> ...         at Lucene.Net.QueryParsers.QueryParser.Jj_consume_token(Int32 kind)    at Lucene.Net.QueryParsers.QueryParser.Term(String field)    at Lucene.Net.QueryParsers.QueryParser.Clause(String field)    at Lucene.Net.QueryParsers.QueryParser.Query(String field)    at Lucene.Net.QueryParsers.QueryParser.Parse(String query)    --- End of inner exception stack trace ---    at Lucene.Net.QueryParsers.QueryParser.Parse(String query)    at K2Bridge.KustoDAL.LuceneHighlighter.MakeValueHighlighter(QueryParser parser, String value, String highlightPreTag, String highlightPostTag) in /app/K2Bridge/KustoDAL/LuceneHighlighter.cs:line 171
  | Payload.ActionId | af9a55f3-a30f-4f19-a348-674aa037d0c3
  | Payload.ActionName | K2Bridge.Controllers.QueryController.SingleSearchAsync (K2Bridge)
  | Payload.ConnectionId | 0HMER3KCK6TR2
  | Payload.CorrelationId | 3f885b0f-be58-4021-be3d-02360c800848
  | Payload.RequestId | 0HMER3KCK6TR2:00000329
  | Payload.RequestPath | /log:InfraKustokibana/_search
  | Payload.SourceContext | K2Bridge.KustoDAL.KustoResponseParser

Unable to report k2bridge telemetry using Application Insight

I am using K2bridge with Client ID and Client Secret using collectTelemetry true and instrumentation key being set. The k2bridge is deployed in a nodepool with subnet from a private VNET. I need to understand if there are permissions needs to be given in order to collect telemetry.

Lucene complex range searches don't work

Searches such as field:>50 results in a generated KQL query:

(field == ">50")

Backslashes are not escaped properly

HI. Getting the syntax error here:

  "query": {
    "match": {
      "CallerFilePath": {
        "query": "D:\\BuildAgent\\work\\abc\\src\\abc\\Controllers\\ErrorLogApiController.cs",
        "type": "phrase"
      }
    }
  }
}

Discover: Syntax error: Query could not be parsed: I could not parse that, sorry.. Query: 'let fromUnixTimeMilli = (t:long) {datetime(1970 - 01 - 01) + t * 1millisec}; let _data = database("log").Cashhardware | where (CallerFilePath == "D:\BuildAgent\work\abc\src\abc\Controllers\ErrorLogApiController.cs") and (Timestamp >= fromUnixTimeMilli(1584037503141) and Timestamp <= fromUnixTimeMilli(1584642303141)); (_data | summarize count() by Timestamp = bin(Timestamp, 3h) | order by Timestamp asc | as aggs); (_data | order by Timestamp desc | limit 500 | as hits)'

It seems that it doesn't escape slashes in "D:\BuildAgent" filter.

Error Unable to cast object of type 'System.DBNull' to type 'System.SByte'.

We're having the following exception. latest K2

System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.SByte'.
   at K2Bridge.KustoDAL.HitsMapper.<>c.<.cctor>b__8_0(Object value) in /app/K2Bridge/KustoDAL/HitsMapper.cs:line 28
   at K2Bridge.KustoDAL.HitsMapper.ReadHit(DataRow row, QueryData query, LuceneHighlighter highlighter) in /app/K2Bridge/KustoDAL/HitsMapper.cs:line 73
   at K2Bridge.KustoDAL.HitsMapper.<>c__DisplayClass3_0.<MapAndAnalyzeRows>b__0(DataRow row) in /app/K2Bridge/KustoDAL/HitsMapper.cs:line 53
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at System.Collections.Generic.List`1.InsertRange(Int32 index, IEnumerable`1 collection)
   at K2Bridge.KustoDAL.KustoResponseParser.ReadResponse(QueryData query, IDataReader reader, TimeSpan timeTaken) in /app/K2Bridge/KustoDAL/KustoResponseParser.cs:line 181
   at K2Bridge.KustoDAL.KustoResponseParser.Parse(IDataReader reader, QueryData queryData, TimeSpan timeTaken) in /app/K2Bridge/KustoDAL/KustoResponseParser.cs:line 102

Data may be sensitive, will send you by email.

Does K2Bridge work with existing environments?

Is it possible to use K2Bridge to connect data from an existing Azure table (from a storage account) to an existing Kibana environment?

From what I have read so far and found online, it requires a new Azure Data Explorer cluster and database for K2Bridge work and as well a new Kibana environment.

Has anyone done the same setup I'm aiming to do?

Also, in the README.md it refers to supporting Kibana 7, does it support Kibana 8?

Add support for the 'Visualize' tab

Community Note

Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
If you are interested in working on this issue or have submitted a pull request, please leave a comment

Currently K2Bridge only supports the 'Discover' tab but we're interesting in learning if this is a valuable feature to include in the future.

Support meaningful error messages (Kibana)

Context:
Sometimes the search fails, whether due to a bug or a bad input from the user, e.g. when searching for an invalid field

NotARealColumn:something

The error Kibana displays is "Internal server error".

This issue suggest:
a. provide a more meaningful error
b. if possible wrap with user friendly error msg -> "invalid column try again"

Filtering according to real numbers value is not working

Description of the problem:
Kibana trims / rounds real values, users who would like to filter according to these values are not getting any results (or wrong results)

How to reproduce:

Given a kusto real field in a table
Kibana shows this value as a trimmed value / rounded:
Try to filter according to that field (In Kibana):
The generated part of the query is : 'and (CounterValue == 13.77)'
Results are not showing because this is not correct

Duplicate configuration setting

In the Helm chart, configuration values such as aadClientId (and others) are set twice:

In deployment.yaml:

        - name: aadClientId
          value: "{{ .Values.settings.aadClientId }}"

In configmap.yaml:

  aadClientId: "{{ .Values.settings.aadClientId }}"

Creating index pattern does not work in version 7.16_latest

There is an issue while creating Kibana index pattern while using the K2Bridge image version 7.16_latest, and also with the image 0.5.15164 that are the latest images at the moment.

When trying to create an index pattern there is an available list of all the tables on the ADX database, and when trying to create an index pattern for one of the tables there is a message: The indices which match this index pattern don't contain any time fields.
But, in the table timestamp as information exists in the field timestamp.
The index pattern fails to be created with an error: Error fetching fields for index pattern

Here are some of the screenshots:

From the k2bridge deployment logs, there are some that seem to be produced at the time of failed index pattern creation and that could be useful for troubleshooting the issue. If there are some other logs and information that could be useful, they can also be provided at request.

{"@t":"2023-06-28T10:49:52.8278841Z","@mt":"Translate params: header:{@header}, query:{@query}","@l":"Debug","header":"{\"index\":\"loggingAppDB:GELFLogs*\"}","query":{"Data":"{\"size\":0,\"aggs\":{\"indices\":{\"terms\":{\"field\":\"_index\",\"size\":200}}}}"},"SourceContext":"K2Bridge.ElasticQueryTranslator","ActionId":"b23454a6-cdea-452d-a72d-df33c221fd89","ActionName":"K2Bridge.Controllers.QueryController.SingleSearchAsync (K2Bridge)","RequestId":"0HMRNJV1G883V:00000016","RequestPath":"/loggingAppDB:GELFLogs*/_search","ConnectionId":"0HMRNJV1G883V","CorrelationId":"f2e4d341-2453-4235-ab27-834cc9ce6ad4"}
{"@t":"2023-06-28T10:49:52.8292286Z","@mt":"Failed to execute translate operation.","@l":"Error","@x":"System.ArgumentNullException: Query cannot be null (Parameter 'Query')\n   at K2Bridge.Ensure.ConstructMessageAndThrowArgumentOrNullArgument[T](T arg, String argName, String predefinedMessage, ILogger logger) in /app/K2Bridge/Ensure.cs:line 92\n   at K2Bridge.ElasticQueryTranslator.TranslateQuery(String header, String query) in /app/K2Bridge/ElasticQueryTranslator.cs:line 49","SourceContext":"K2Bridge.ElasticQueryTranslator","ActionId":"b23454a6-cdea-452d-a72d-df33c221fd89","ActionName":"K2Bridge.Controllers.QueryController.SingleSearchAsync (K2Bridge)","RequestId":"0HMRNJV1G883V:00000016","RequestPath":"/loggingAppDB:GELFLogs*/_search","ConnectionId":"0HMRNJV1G883V","CorrelationId":"f2e4d341-2453-4235-ab27-834cc9ce6ad4"}
{"@t":"2023-06-28T10:49:52.8295303Z","@mt":"Failed translating elasticsearch query","@l":"Error","SourceContext":"K2Bridge.Controllers.QueryController","ActionId":"b23454a6-cdea-452d-a72d-df33c221fd89","ActionName":"K2Bridge.Controllers.QueryController.SingleSearchAsync (K2Bridge)","RequestId":"0HMRNJV1G883V:00000016","RequestPath":"/loggingAppDB:GELFLogs*/_search","ConnectionId":"0HMRNJV1G883V","CorrelationId":"f2e4d341-2453-4235-ab27-834cc9ce6ad4"}
{"@t":"2023-06-28T10:52:37.5007653Z","@mt":"Listing tables matching '{@indexName}'","@l":"Debug","indexName":"*:*","SourceContext":"K2Bridge.KustoDAL.KustoDataAccess","ActionId":"1943a0e3-4938-4e84-9026-a2548e0b8332","ActionName":"K2Bridge.Controllers.IndexListController.Resolve (K2Bridge)","RequestId":"0HMRNJV1G885U:00000002","RequestPath":"/_resolve/index/*:*","ConnectionId":"0HMRNJV1G885U","CorrelationId":"fb1293f7-933a-4d7a-94d5-d800bf977afc"}
{"@t":"2023-06-28T10:52:37.5010166Z","@mt":"Calling adminClient.ExecuteControlCommand with the command: {@command}","@l":"Debug","command":{"Data":".show databases schema | where TableName != '' | distinct TableName, DatabaseName | search TableName: '*' | search DatabaseName: '*' |  project strcat(DatabaseName, \":\", TableName)"},"SourceContext":"K2Bridge.KustoDAL.KustoQueryExecutor","ActionId":"1943a0e3-4938-4e84-9026-a2548e0b8332","ActionName":"K2Bridge.Controllers.IndexListController.Resolve (K2Bridge)","RequestId":"0HMRNJV1G885U:00000002","RequestPath":"/_resolve/index/*:*","ConnectionId":"0HMRNJV1G885U","CorrelationId":"fb1293f7-933a-4d7a-94d5-d800bf977afc"}
{"@t":"2023-06-28T10:52:37.5504433Z","@mt":"Translate params: header:{@header}, query:{@query}","@l":"Debug","header":"{\"index\":\"*:*\"}","query":{"Data":"{\"size\":0,\"aggs\":{\"indices\":{\"terms\":{\"field\":\"_index\",\"size\":200}}}}"},"SourceContext":"K2Bridge.ElasticQueryTranslator","ActionId":"b23454a6-cdea-452d-a72d-df33c221fd89","ActionName":"K2Bridge.Controllers.QueryController.SingleSearchAsync (K2Bridge)","RequestId":"0HMRNJV1G883V:00000022","RequestPath":"/*:*/_search","ConnectionId":"0HMRNJV1G883V","CorrelationId":"7b3eb7ca-b770-4157-a3c6-2fec1c09c2eb"}
{"@t":"2023-06-28T10:52:37.5507401Z","@mt":"Failed to execute translate operation.","@l":"Error","@x":"System.ArgumentNullException: Query cannot be null (Parameter 'Query')\n   at K2Bridge.Ensure.ConstructMessageAndThrowArgumentOrNullArgument[T](T arg, String argName, String predefinedMessage, ILogger logger) in /app/K2Bridge/Ensure.cs:line 92\n   at K2Bridge.ElasticQueryTranslator.TranslateQuery(String header, String query) in /app/K2Bridge/ElasticQueryTranslator.cs:line 49","SourceContext":"K2Bridge.ElasticQueryTranslator","ActionId":"b23454a6-cdea-452d-a72d-df33c221fd89","ActionName":"K2Bridge.Controllers.QueryController.SingleSearchAsync (K2Bridge)","RequestId":"0HMRNJV1G883V:00000022","RequestPath":"/*:*/_search","ConnectionId":"0HMRNJV1G883V","CorrelationId":"7b3eb7ca-b770-4157-a3c6-2fec1c09c2eb"}
{"@t":"2023-06-28T10:52:37.5510253Z","@mt":"Failed translating elasticsearch query","@l":"Error","SourceContext":"K2Bridge.Controllers.QueryController","ActionId":"b23454a6-cdea-452d-a72d-df33c221fd89","ActionName":"K2Bridge.Controllers.QueryController.SingleSearchAsync (K2Bridge)","RequestId":"0HMRNJV1G883V:00000022","RequestPath":"/*:*/_search","ConnectionId":"0HMRNJV1G883V","CorrelationId":"7b3eb7ca-b770-4157-a3c6-2fec1c09c2eb"}

Error if a table or a function (view) does not contain a field mentioned in the search query

If you mistype or use non-existent field in the search query, you get an error:

Add support for Significant Terms aggregation

Reference documentation: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-significantterms-aggregation.html

"An aggregation that returns interesting or unusual occurrences of terms in a set." In Kibana, it can only be applied to text fields.

According to the documentation, this aggregation is primarily used with a "parent-level aggregation to segment the data ready for analysis". In other terms, it is typically a sub-aggregation of another bucket aggregation, like Terms or a Histogram.

This makes this issue dependant on support for sub-bucket aggregations (#145).

Before implementing, we need to identify how to perform a similar query in Kusto. According to the doc, Significant Terms "are the terms that have undergone a significant change in popularity measured between a foreground and background set. [...] In the simplest case, the foreground set of interest is the search results matched by a query and the background set used for statistical comparisons is the index or indices from which the results were gathered."

Sample request:

  "aggs": {
    "2": {
      "histogram": {
        "field": "AvgTicketPrice",
        "interval": 100,
        "min_doc_count": 1
      },
      "aggs": {
        "3": {
          "significant_terms": {
            "field": "DestCountry",
            "size": 3
          }
        }
      }
    }

Response: (extract)

  "aggregations": {
    "2": {
      "buckets": [
        {
          "3": {
            "doc_count": 749,
            "bg_count": 13059,
            "buckets": [
              {
                "key": "IT",
                "doc_count": 243,
                "score": 0.2552994319244096,
                "bg_count": 2371
              },
              {
                "key": "US",
                "doc_count": 172,
                "score": 0.11694192970564077,
                "bg_count": 1987
              },
              {
                "key": "CH",
                "doc_count": 79,
                "score": 0.10476945913799716,
                "bg_count": 691
              }
            ]
          },
          "key": 100,
          "doc_count": 749
        },
        {
          "3": {
            "doc_count": 1067,
            "bg_count": 13059,
            "buckets": [
              {
                "key": "IT",
                "doc_count": 241,
                "score": 0.0551183926043845,
                "bg_count": 2371
              },
              {
                "key": "CH",
                "doc_count": 83,
                "score": 0.03656787843506986,
                "bg_count": 691
              },
              {
                "key": "US",
                "doc_count": 185,
                "score": 0.02418926301801488,
                "bg_count": 1987
              }
            ]
          },
          "key": 200,
          "doc_count": 1067
        },

Column _id should be used if available from Kusto

Kibana uses _id field for several features:

Consistent UI when using auto refresh in Discover tab
View Single Document/View Surrounding Documents buttons

Currently we generate our _id fields from Filebeat with add_id processor. https://www.elastic.co/guide/en/beats/filebeat/current/add-id.html

In order to have Kibana experience consistent with elastic, we have to use _id from Kusto if the table contains it.

Exists filter does not work with fields with special symbols in names

Exists filter does not work with fields with special symbols in names.

We have index with column Payload.@l.
It works good, if we use it to filter rows by value, for example:

{
  "query": {
    "match_phrase": {
      "Payload.@l": "Error"
    }
  }
}

But we get an error, if we try to filter rows based on this column existence:

{
  "exists": {
    "field": "Payload.@l"
  }
}

Sample error message:

[0]Kusto.Data.Exceptions.SyntaxException: Syntax error: A recognition error occurred.. Query: 'let _data = database("log").InfraKustokibana | where (['Env'] == "dev-sre") and (['Timestamp'] >= todatetime("2022-01-17T08:13:05.1150000Z") and ['Timestamp'] <= todatetime("2022-01-19T08:13:05.1150000Z")) and (['Payload'].['@l'] == "Error") and (isnotnull(Payload.@l));
let _extdata = _data
| extend ['2'] = bin(['Timestamp'], 1h);
let _summarizablemetrics = _extdata
| summarize count() by ['2']
| order by ['2'] asc;
(_summarizablemetrics
| as aggs);
(_data | count | as hitsTotal);
(_data | order by ['Timestamp'] desc | limit 500 | as hits)'
Timestamp=2022-01-19T08:13:05.7408043Z
ClientRequestId=K2Bridge.ExecuteQuery;94de483b-bfaa-4638-aa69-2a16e608d0dc
ActivityId=7776d65d-9297-48b4-be3d-6e1095f30c1b
ActivityType=DN.RestClient.ExecuteQuery
MachineName=k2bridge-555bb5ffb4-8swdq
ProcessName=dotnet
ProcessId=1
ThreadId=311
AppDomainName=K2Bridge
ActivityStack=(Activity stack: CRID=K2Bridge.ExecuteQuery;94de483b-bfaa-4638-aa69-2a16e608d0dc ARID=7776d65d-9297-48b4-be3d-6e1095f30c1b > DN.RestClient.ExecuteQuery/7776d65d-9297-48b4-be3d-6e1095f30c1b)
MonitoredActivityContext=(ActivityType=DN.RestClient.ExecuteQuery, Timestamp=2022-01-19T08:13:05.2680582Z, ParentActivityId=7776d65d-9297-48b4-be3d-6e1095f30c1b, TimeSinceStarted=472.759 [ms])ErrorCode=SYN0002
ErrorReason=BadRequest
ErrorMessage=A recognition error occurred.
DataSource=https://<REDACTED>.westeurope.kusto.windows.net/v1/rest/query
DatabaseName=log
ClientRequestId=K2Bridge.ExecuteQuery;94de483b-bfaa-4638-aa69-2a16e608d0dc
ActivityId=e3241ef1-b0ff-4392-bb4f-00d6a1c6725e
Text=let _data = database("log").InfraKustokibana | where (['Env'] == "dev-sre") and (['Timestamp'] >= todatetime("2022-01-17T08:13:05.1150000Z") and ['Timestamp'] <= todatetime("2022-01-19T08:13:05.1150000Z")) and (['Payload'].['@l'] == "Error") and (isnotnull(Payload.@l));
let _extdata = _data
| extend ['2'] = bin(['Timestamp'], 1h);
let _summarizablemetrics = _extdata
| summarize count() by ['2']
| order by ['2'] asc;
(_summarizablemetrics
| as aggs);
(_data | count | as hitsTotal);
(_data | order by ['Timestamp'] desc | limit 500 | as hits)
ParseErrors=A recognition error occurred.
Line=1
CharacterPositionInLine=266
Token=l

   at Kusto.Cloud.Platform.Http.KustoHttpClient.ThrowKustoExceptionFromResponseMessageAsync(KustoExceptionContext exceptionContext, HttpResponseMessage responseMessage, ClientRequestProperties properties, Boolean shouldBuffer, Action`2 notify)
   at Kusto.Data.Net.Client.RestClient2.MakeHttpRequestAsyncImpl(String address, String csl, String ns, String databaseName, Boolean streaming, ClientRequestProperties properties, ServiceModelTimeoutKind timeoutKind, String clientRequestId, Stream body, StreamProperties streamProperties)
   at Kusto.Cloud.Platform.Utils.MonitoredActivity.InvokeAsync[TActivityType,TResult](TActivityType activityType, Func`1 func, String clientRequestId)
   at Kusto.Cloud.Platform.Utils.MonitoredActivity.InvokeAsync[TActivityType,TResult](TActivityType activityType, Func`1 func, String clientRequestId)
   at Kusto.Data.Net.Client.RestClient2.MakeHttpRequestAsync(ActivityType activityType, String baseAddress, String relativeAddress, String clientRequestIdPrefix, String ns, String databaseName, String csl, String addr, Boolean streaming, ClientRequestProperties properties, ServiceModelTimeoutKind timeoutKind, StreamProperties streamProperties)
   at Kusto.Data.Net.Client.RestClient2.ExecuteQueryAsync(String databaseName, String query, ClientRequestProperties properties)
   at K2Bridge.KustoDAL.CslQueryProviderExtensions.ExecuteMonitoredQueryAsync(ICslQueryProvider client, String query, ClientRequestProperties clientRequestProperties, Metrics metrics) in /app/K2Bridge/KustoDAL/CslQueryProviderExtensions.cs:line 40
   at K2Bridge.KustoDAL.KustoQueryExecutor.ExecuteQueryAsync(QueryData queryData, RequestContext requestContext) in /app/K2Bridge/KustoDAL/KustoQueryExecutor.cs:line 117

Looks like, it should be Payload["@l"] instead of Payload.@l in query.

Doublequotes escaping is broken

Hi. It seems that double-quotes escape in filters doesnt'work:
Repro:
Get field and value with double-quotes, click "+", receive error.

The error itself:

Error: Request to Elasticsearch failed: {"error":{"root_cause":[{"type":"SyntaxException","reason":"Syntax error: Query could not be parsed: I could not parse that, sorry.. Query: 'let fromUnixTimeMilli = (t:long) {datetime(1970 - 01 - 01) + t * 1millisec};\nlet _data = database(\"database\").Table | where (message == \"Health check \"LF\" completed after 35.422ms with status Unhealthy and '\"{\\\"Url\\\":\\\"http://url\\\"}\"'\") and (Timestamp >= fromUnixTimeMilli(1584385027085) and Timestamp <= fromUnixTimeMilli(1584385927085));\n(_data | summarize count() by Timestamp = bin(Timestamp, 30s)\n| order by Timestamp asc | as aggs);\n(_data | order by Timestamp desc | limit 500 | as hits)'","index_uuid":"index","index":"index"}],"type":"QueryException","reason":"Failed executing azure data explorer query","phase":"query"},"status":500}