cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CTOUT Frequent Visitor
Frequent Visitor

Pagination of Log Analytics

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
CTOUT Frequent Visitor
Frequent Visitor

Re: Pagination of Log Analytics

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

 

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
1 REPLY 1
CTOUT Frequent Visitor
Frequent Visitor

Re: Pagination of Log Analytics

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

 

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