SOLVED

Skipping Records via Log Analytics API

%3CLINGO-SUB%20id%3D%22lingo-sub-327823%22%20slang%3D%22en-US%22%3ESkipping%20Records%20via%20Log%20Analytics%20API%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-327823%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20working%20with%20Power%20BI%20and%20Log%20Analytics%20to%20visualise%20the%20data%20that%20we%20are%20storing.%3C%2FP%3E%3CP%3EI've%20finally%20got%20Power%20BI%20to%20do%20everything%20I%20need%20it%20to%20before%20I%20realised%20that%20there%20appears%20to%20be%20no%20%22skip%22%20operator%20to%20allow%20me%20to%20paginate%20throughout%20the%20records.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20there%20used%20to%20be%20this%20function%2C%20certainly%20in%20OMS%2C%20so%20I'm%20perplexed%20that%20it%20has%20been%20overlooked%20from%20Log%20Analytics.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20an%20alternative%20operator%20that%20I%20could%20use%20to%20do%20the%20same%20thing%20or%20some%20workaround%20to%20perform%20record%20skipping%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%20for%20any%20and%20all%20assistance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChris%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-327823%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332366%22%20slang%3D%22en-US%22%3ERe%3A%20Skipping%20Records%20via%20Log%20Analytics%20API%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332366%22%20slang%3D%22en-US%22%3E%3CP%3EOk.%20I%20get%20the%20scenario%20better%20now.%20Not%20sure%20if%20I%20can%20give%20any%20more%20advice%20as%20I%20haven't%20done%20so%20much%20of%20data%20export%20to%20PowerBI%20and%20I%20would%20assume%20you%20are%20not%20using%20the%20default%20export%20option.%20My%20honest%20opinion%20is%20that%20Log%20Analytics%20is%20made%20to%20be%20the%20end%20point%20for%20your%20data%20or%20at%20least%20the%20point%20where%20you%20can%20summarize%20some%20results%20and%20send%20them%20to%20other%20services%20like%20PowerBI.%20Hoping%20that%20you%20will%20find%20solution%20for%20your%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332259%22%20slang%3D%22en-US%22%3ERe%3A%20Skipping%20Records%20via%20Log%20Analytics%20API%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332259%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Stanislav%20and%20apologies%20for%20the%20delay%20in%20responding%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20seem%20to%20remember%20reading%20this%20now%20that%20you've%20highlighted%20it%20for%20me.%26nbsp%3B%20However%2C%20I've%20seen%20so%20many%20contradicting%20examples%20of%20doing%20this%20(i.e.%20when%20skip%20seemed%20to%20still%20be%20an%20acceptable%20option)%20it's%20hard%20to%20keep%20up%20with%20the%20%22current%22%20revision.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20provide%20more%20information%2C%20I%20am%20looking%20at%20collecting%20roughly%2050%20million%20results%20to%20further%20analyse%20in%20PowerBI.%3C%2FP%3E%3CP%3EThis%20is%20seemingly%20something%20that%20Log%20Analytics%20does%20not%20want%2Fintend%20for%20me%20to%20do.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20presume%20the%20ways%20forward%20would%20be%3A%20-%3C%2FP%3E%3CP%3EA)%20Look%20into%20splitting%20this%20down%20into%20time%20based%20segments%20(still%20pulling%2050%20million%20records!)%3C%2FP%3E%3CP%3EB)%20Store%20the%20logs%20elsewhere%20that%20does%20not%20have%20such%20restrictions%2Flimits%2C%3C%2FP%3E%3CP%3EC)%20Do%20more%20filtering%20at%20the%20Log%20Analytics%20side%20of%20things%20and%20run%20multiple%20(smaller)%20queries%20to%20get%20what%20you%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20interested%20in%20what%20others%20have%20done%20for%20datasets%20this%20vast.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-328017%22%20slang%3D%22en-US%22%3ERe%3A%20Skipping%20Records%20via%20Log%20Analytics%20API%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-328017%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EI%20am%20not%20sure%20what%20exactly%20is%20your%20scenario%20but%20the%20official%20answer%20on%20this%20is%20located%20in%20take%20operator%20documentation%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Ftakeoperator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Ftakeoperator%3C%2FA%3E%3C%2FP%3E%0A%3CH2%20id%3D%22a-note-on-paging-through-a-large-resultset-or-the-lack-of-a-skip-operator%22%20class%3D%22heading-with-anchor%22%20id%3D%22toc-hId-1675273633%22%20id%3D%22toc-hId-1818203198%22%3EA%20note%20on%20paging%20through%20a%20large%20resultset%20(or%3A%20the%20lack%20of%20a%3CCODE%3Eskip%3C%2FCODE%3E%20operator)%3C%2FH2%3E%0A%3CP%3EKusto%20does%20not%20support%20the%20complementary%20%3CCODE%3Eskip%3C%2FCODE%3E%20operator.%20This%20is%20intentional%2C%20as%20%3CCODE%3Etake%3C%2FCODE%3E%20and%20%3CCODE%3Eskip%3C%2FCODE%3E%20together%20are%20mainly%20used%20for%20thin%20client%20paging%2C%20and%20have%20a%20major%20performance%20impact%20on%20the%20service.%20Application%20builders%20that%20want%20to%20support%20result%20paging%20are%20advised%20to%20query%20for%20several%20pages%20of%20data%20(say%2C%2010%2C000%20records%20at%20a%20time)%20and%20then%20display%20a%20page%20of%20data%20at%20a%20time%20to%20the%20user.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20that%20said%20paging%20is%20still%20possible%20but%20you%20will%20have%20to%20use%20take%20and%20slightly%20different%20approach.%20Probably%20is%20also%20good%20to%20do%20some%20sorting%20on%20time%20column%20take%2010%20000%20records.%20If%2010%20000%20records%20are%20return%20get%20the%20time%20of%20the%20last%20record%20and%20shorten%20the%20time%20range.%20If%20less%2010%20000%20records%20are%20returned%20you've%20got%20all%20records%20for%20that%20time%20frame.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Good Morning,

 

I've been working with Power BI and Log Analytics to visualise the data that we are storing.

I've finally got Power BI to do everything I need it to before I realised that there appears to be no "skip" operator to allow me to paginate throughout the records.

 

I believe there used to be this function, certainly in OMS, so I'm perplexed that it has been overlooked from Log Analytics.

 

Is there an alternative operator that I could use to do the same thing or some workaround to perform record skipping?


Thanks for any and all assistance!

 

Chris

3 Replies
Highlighted

Hi,

I am not sure what exactly is your scenario but the official answer on this is located in take operator documentation:

https://docs.microsoft.com/en-us/azure/kusto/query/takeoperator

A note on paging through a large resultset (or: the lack of a skip operator)

Kusto does not support the complementary skip operator. This is intentional, as take and skip together are mainly used for thin client paging, and have a major performance impact on the service. Application builders that want to support result paging are advised to query for several pages of data (say, 10,000 records at a time) and then display a page of data at a time to the user.

 

 

With that said paging is still possible but you will have to use take and slightly different approach. Probably is also good to do some sorting on time column take 10 000 records. If 10 000 records are return get the time of the last record and shorten the time range. If less 10 000 records are returned you've got all records for that time frame.

Highlighted

Thanks Stanislav and apologies for the delay in responding,

 

I seem to remember reading this now that you've highlighted it for me.  However, I've seen so many contradicting examples of doing this (i.e. when skip seemed to still be an acceptable option) it's hard to keep up with the "current" revision.

 

To provide more information, I am looking at collecting roughly 50 million results to further analyse in PowerBI.

This is seemingly something that Log Analytics does not want/intend for me to do.  

 

So I presume the ways forward would be: -

A) Look into splitting this down into time based segments (still pulling 50 million records!)

B) Store the logs elsewhere that does not have such restrictions/limits,

C) Do more filtering at the Log Analytics side of things and run multiple (smaller) queries to get what you want.

 

I'm interested in what others have done for datasets this vast.

Highlighted
Solution

Ok. I get the scenario better now. Not sure if I can give any more advice as I haven't done so much of data export to PowerBI and I would assume you are not using the default export option. My honest opinion is that Log Analytics is made to be the end point for your data or at least the point where you can summarize some results and send them to other services like PowerBI. Hoping that you will find solution for your problem.