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
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