Forum Discussion
Getting Data from Elastic Stack with REST with Scroll
- 9 years ago
There have been a few different threads like this but I believe the solution lies in creating a Power Query function that you call recursively. Here is an example of functions and recursion on a completely unrelated topic but might get you there:
- 9 years ago
Thanks for pointing me in the right direction.
For anyone else that runs into this problem, here's the first iteration of a working function:
let RecursiveElasticFetch = (queryUrl, scrollUrl, scrollId, counter) => let Counter = if (counter = null) then 0 else counter, Results = if (scrollId = null) then Json.Document(Web.Contents(queryUrl)) else Json.Document(Web.Contents(scrollUrl&scrollId, [Headers=[MyHeader=Text.From(Counter)]])), ParsedResults = Table.FromList(Results[hits][hits], Splitter.SplitByNothing(), null, null, ExtraValues.Error), Return = if (Counter < 10) then ParsedResults & RecursiveElasticFetch(queryUrl, scrollUrl, Results[_scroll_id], Counter+1) else ParsedResults in Return in RecursiveElasticFetchFor the parameters:
queryUrl - The query URL that kicks off the search: http://elasticsrch-dev:9200/intranet*/_search?scroll=1m&source={....}
scrollUrl - The URL used for subsequent searches (the scroll id is appended at the end): http://elasticsrch-dev:9200/_search/scroll?scroll=1m&scroll_id=
scrollId - Used to pass the scroll_id to subsequent calls. Leave it blank for the first call
counter - Used to limit the number of iterations. Leave it blank for the first call.
Hi
im facing same issue
An error occurred in the ‘RecursiveElasticFetch’ query. DataSource.Error: Web.Contents failed to get contents from 'https://redacted:9200/index-*stats*/_search?scroll=1m' (406): Not Acceptable
Details:
DataSourceKind=Web
DataSourcePath=https://redacted:9200/filebeat-*stats*/_search
Url=https://redacted:9200/index-*stats*/_search?scroll=1m
- nicpenning4 years ago
Helper I
What version of Elasticsearch are you using?
- Maggi0294 years ago
Helper II
- nicpenning4 years ago
Helper I
Also, I had this problem when using Self Signed certificates so you may need to allow that in PowerBi.
Re-posting here:I believe the (406): Not Acceptable issue was because I was using a self-signed certificate.
I replaced the self-signed certificate with a much higher level signed certificate and that seemed to work locally and at the gateway level.
Otherwise, you have to tell PowerBi to trust self-signed certs:
Update the file C:\Program Files\Microsoft Power BI Desktop\bin\Microsoft.Mashup.Container.NetFX45.exe.config to:
<configuration> <system.net> <settings> <servicePointManager checkCertificateName="false" checkCertificateRevocationList="false" /> </settings> </system.net> </configuration>Restart Power BI.
https://community.powerbi.com/t5/Power-Query/Self-Signed-Certificates-on-PowerBI/m-p/804777/highlight/true#M26981 - saranxpert2 years agoNew Member
Hi ,
Im getting below error and tried to add config as provided below in the config file , not sure what is teh issue
An error occurred in the ‘RecursiveElasticFetch’ query. DataSource.Error: Web.Contents failed to get contents from 'https://monitor.sre.np.aws.csaa.pri/sre-availability-details/_search?scroll=1m' (406): Not Acceptable
Details:
DataSourceKind=Web
DataSourcePath=https://monitor.sre.np.aws.csaa.pri/sre-availability-details/_search
Url=https://monitor.sre.np.aws.csaa.pri/sre-availability-details/_search?scroll=1m