Log Analytics table growth

%3CLINGO-SUB%20id%3D%22lingo-sub-1024814%22%20slang%3D%22en-US%22%3ELog%20Analytics%20table%20growth%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1024814%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%20community!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20the%20following%20query%20to%20keep%20a%20close%20eye%20on%20my%20top%20tables%20in%20Log%20Analytics%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Esearch%20*%20%0A%7C%20summarize%20count()%20by%20%24table%0A%7C%20project%20Table%3D%24table%2C%20Count%3Dcount_%0A%7C%20top%205%20by%20Count%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20is%20great%2C%20but%20I'd%20also%20like%20to%20track%20the%20growth%20on%20a%20day-to-day%20basis%20so%20that%20I%20can%20graph%20it%20and%20catch%20when%20there%20is%20a%20big%20jump%20in%20consumption.%20Any%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1024814%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Monitor%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1025450%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20table%20growth%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1025450%22%20slang%3D%22en-US%22%3E%3CP%3EBookmarking%20this%20interesting%20ask%20as%20I%20have%20also%20no%20idea%20how%20to%20use%20date%20diff%20in%20let.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1025544%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20table%20growth%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1025544%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F151992%22%20target%3D%22_blank%22%3E%40Scott%20Allison%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Eunion%20withsource%20%3D%20tt%20*%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(31d)%20%0A%7C%20summarize%20count()%20by%20bin(TimeGenerated%2C1d)%2C%20Source%3Dtt%0A%7C%20render%20timechart%20title%20%3D%20%22Monthly%20growth%22%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA1WNsQqDQBAFe8F%25252FeFidwSakNm2qVPEHTl28A28X1j3EkI%25252FPmS7dFDNM5iiMPVrYJOtE6GGGS119sAdSwhATPYhJvdGMew%25252B%25252FiLtd5xans%25252BWUvMY3YZLM5lqMB8bI7i%25252Frit7h9Rv0ZmeoxDMprGhT8GqFbD3vzVPYwnpgUdktNHX1BU8UotKjAAAA%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158430i8DF2E449E712098D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1025978%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20table%20growth%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1025978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3BSir%2C%3CBR%20%2F%3EThis%20is%20might%20be%20a%20silly%20question%20but%20what%20is%20the%20meaning%20of%20this%20line%3CSTRONG%3E%20%5B%3C%2FSTRONG%3E%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSTRONG%3Eunion%20withsource%20%3D%20tt%20*%5D%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3EWhat%20is%20%3CSTRONG%3Ewithsource%20%3C%2FSTRONG%3Eand%3CSTRONG%3E%26nbsp%3Btt%20%3C%2FSTRONG%3Ehere%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1026161%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20table%20growth%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1026161%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F184511%22%20target%3D%22_blank%22%3E%40Gourav%20Kumar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Funionoperator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Funionoperator%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20simple%20terms%20as%20we%20are%20looking%20at%20multiple%20tables%20with%20the%20(*)%20wildcard%20-%20its%20assigning%20the%20name%20of%20each%20Table%20to%20%22tt%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3E%3CCODE%3E%22withsource%3C%2FCODE%3E%3DColumnName%3A%20If%20specified%2C%20the%20output%20will%20include%20a%20column%20called%26nbsp%3BColumnName%26nbsp%3Bwhose%20value%20indicates%20which%20source%20table%20has%20contributed%20each%20row.%20If%20the%20query%20effectively%20(after%20wildcard%20matching)%20references%20tables%20from%20more%20than%20one%20database%20(default%20database%20always%20counts)%20the%20value%20of%20this%20column%20will%20have%20a%20table%20name%20qualified%20with%20the%20database.%20Similarly%26nbsp%3B%3CSTRONG%3Ecluster%20and%20database%3C%2FSTRONG%3E%26nbsp%3Bqualifications%20will%20be%20present%20in%20the%20value%20if%20more%20than%20one%20cluster%20is%20referenced.%22%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%3CEM%3ESo%20I%20could%20have%20changed%20it%20to%20%22%3CSTRONG%3Eathing%3C%2FSTRONG%3E%22%20instead%2C%20but%20not%20to%20any%20reserved%26nbsp%3Bkeywords%20like%20%22%3CSTRONG%3ESource%3C%2FSTRONG%3E%22%26nbsp%3B%20-%20hence%20mapping%20it%20back%20to%20%3CSTRONG%3ESource%3C%2FSTRONG%3E%20at%20the%20end%20with%20%3CSTRONG%3ESource%3Dathing%3C%2FSTRONG%3E%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158465iF656F7E638E64959%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CEM%3EI%20only%20tend%20to%20use%20%22tt%22%20as%20that%20was%20the%26nbsp%3Bexample%26nbsp%3Bin%20the%20docs%20when%20I%20first%20learnt%20about%20it%20%3A)%3C%2Fimg%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1031178%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20table%20growth%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1031178%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20a%20lot%20Sir%20%2C%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Contributor

Greetings community!

 

I'm using the following query to keep a close eye on my top tables in Log Analytics:

search * 
| summarize count() by $table
| project Table=$table, Count=count_
| top 5 by Count

This is great, but I'd also like to track the growth on a day-to-day basis so that I can graph it and catch when there is a big jump in consumption. Any ideas?

 

Thanks!

 

5 Replies
Highlighted

Bookmarking this interesting ask as I have also no idea how to use date diff in let.

Highlighted

@Scott Allison 

 

union withsource = tt *
| where TimeGenerated >= ago(31d) 
| summarize count() by bin(TimeGenerated,1d), Source=tt
| render timechart title = "Monthly growth"

 

Go to Log Analytics and Run Query

clipboard_image_1.png

 

 

Highlighted

@Clive Watson Sir,
This is might be a silly question but what is the meaning of this line [

union withsource = tt *]
What is withsource and tt here
Highlighted

@Gourav Kumar 

 

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

 

In simple terms as we are looking at multiple tables with the (*) wildcard - its assigning the name of each Table to "tt"

 

"withsource=ColumnName: If specified, the output will include a column called ColumnName whose value indicates which source table has contributed each row. If the query effectively (after wildcard matching) references tables from more than one database (default database always counts) the value of this column will have a table name qualified with the database. Similarly cluster and database qualifications will be present in the value if more than one cluster is referenced."

So I could have changed it to "athing" instead, but not to any reserved keywords like "Source"  - hence mapping it back to Source at the end with Source=athing 

 

clipboard_image_0.png

I only tend to use "tt" as that was the example in the docs when I first learnt about it :)

Highlighted

Thanks a lot Sir ,@Clive Watson