Forum Discussion
Getting Data from Elastic Stack with REST with Scroll
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:
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.
Also, I added API Key functionality.
Query (you will need to modify this accordingly):
let
url = "https://CHANGEME:9200/weblogs/_search?scroll=1m",
scrollURL = "https://CHANGEME:9200/_search/scroll",
batchSize = "10000",
urlBody = Text.Combine({"{""size"":",batchSize,",""query"":{""match_all"":{}}}"}),
scrollBody = "{""scroll"":""1m"",""scroll_id"":""",
scrollID = null,
counter = null,
scrollEND = """}",
apiKey = "CHANGEME",
Source = RecursiveElasticFetch(url, scrollURL, batchSize, urlBody, scrollBody, scrollID, counter, scrollEND, apiKey),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "es"}}),
#"Expanded es" = Table.ExpandRecordColumn(#"Renamed Columns", "es", {"_index", "_type", "_id", "_score", "_source"}, {"es._index", "es._type", "es._id", "es._score", "es._source"})
in
#"Expanded es"
Function:
(Right click on the query name on the left hand side and then click create function)
let
RecursiveElasticFetch = (url, scrollURL, batchSize, urlBody, scrollBody, scrollID, counter, scrollEND, apiKey) =>
let
Results = if (scrollID = null) then
//Initial Query
Json.Document(Web.Contents(url, [Headers=[#"Authorization"=Text.Combine({"ApiKey ",apiKey,""}), #"Content-Type"="application/json"], Content = Text.ToBinary(urlBody)]))
else
//All other queries execute this to gather results from the scroll api even if the scroll id changes.
Json.Document(Web.Contents(scrollURL, [Headers=[#"Authorization"=Text.Combine({"ApiKey ",apiKey,""}), #"Content-Type"="application/json", MyHeader=Text.From(counter)], Content = Text.ToBinary(scrollBody&scrollID&scrollEND)])),
//If this is the first time the function runs, the counter should be null so this will dynamically calcuate how many times this function needs to run.
counter = if (counter = null) then
//Dynamically get the counter - Note: You can uncomment the next line for testing and then comment out the Number.RoundUp
7
//Number.RoundUp(Results[hits][total][value]/Number.FromText(batchSize))
else
counter,
//Store the hits from the ElasticSearch query into ParsedResults and if results already exist, append more results to generate the full table of events.
ParsedResults = Table.FromList(Results[hits][hits], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Return = if (counter > 0) then
ParsedResults & RecursiveElasticFetch(url, scrollURL, batchSize, urlBody, scrollBody, Results[_scroll_id], counter-1, scrollEND, apiKey)
else
ParsedResults
in
Return
in
RecursiveElasticFetch
Where can I find the API key in elasticsearch on AWS?
I am not that familar with the cloud but you should be able to create an API key in Elastic by following this:
https://www.elastic.co/guide/en/elasticsearch/reference/current/security-api-create-api-key.htmlYou can use Kibana to run the requests and map them to the indices you want the API key to work for.
For example, if you want PowerBi to query winlogbeat-* you need to add that in the index names.
So the API Key is for Elasticsearch access instead of username/password.
