Getting Data from Elastic Stack with REST with Scroll

Hi Dear all, Im currently working with this function but I need to connect at ElasticSearch server using credentials (Basic username:password) , Do you know if this is possible?

I could not get a good result :(

thanks a lot for your help.

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

Hi 

im facing same issue

Hey guys!
Does anyone here have an idea how to do this with an Elasticsearch composite query?

The scroll method worked, however due to the large number of records, it is cumbersome to pull the data.

in composite queries there is no scroll_id parameter, there is only after_key, which is key and value