Pagination of Log Analytics

[Anonymous]

Hi all,

I'm having some difficulties when attempting to paginate Log Analytics.

I've based my workings off of Power Query Pagination Example and have modified it to suit my needs/understanding.

I have the Token, GetCount functions working and I've modified other aspects which appear to work.

The main issue I'm having at present is using a variable that has other variables included.  Power BI does not give the desired outcome, even though the input "appears" correct.

An excerpt below: -

let
//Queries
    PaginationQuery         = "{""query"": ""LogSource | where TimeGenerated > ago(30d) | sort by TimeGenerated asc nulls last | take " & Take & " | top " & Top & """}",

//MiscVariables
    ResultsPerPage          = 50,
    Take                    = Text.From(1 * ResultsPerPage),
    Top                     = Text.From(ResultsPerPage),

//Format API call
    GetJson =
        let Options = [Content=Text.ToBinary(PaginationQuery), Headers=[Authorization="Bearer "&Token,#"Content-Type"="application/json"], RelativePath=DataRelativePath],
            RawData = Web.Contents(DataBaseURL, Options),
            Document = Json.Document(RawData)
        in Document,

in
    GetJson

This results in a Bad Request message.  Whereas if I simply replace the Query with: -

"{""query"": ""LogSource | where TimeGenerated  > ago(5m) ""}"

It works as expected.

I've tried various ways of string/text manipulation but I get the same outcome.

Any ideas?

[Anonymous · Accepted solution]

Some confusion over the query itself led to this problem.

E.g. take and limit are the same function (limit is an alias for take).

Top is seperate but uses additional parameters and effectively does the same as take/limit without having to do a seperate sort.

However they do not appear to work together to produce paginated results 😞

e.g.

Datasource | where TimeGenerated > datetime(2018-12-31) | top 20 by TimeGenerated asc nulls last | limit 10

gives you the same 10 results as

Datasource | where TimeGenerated > datetime(2018-12-31) | top 10 by TimeGenerated asc nulls last | limit 20