Azure Virtual Machine runtime calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-191753%22%20slang%3D%22en-US%22%3EAzure%20Virtual%20Machine%20runtime%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-191753%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3EI%20currently%20have%20an%20azure%20automation%20runbook%20that%20executes%20a%20get-azurermvm%20command%20against%20all%20the%20subscriptions%20in%20our%20tenant.%26nbsp%3B%20This%20script%20compiles%20a%20table%2C%20that%20lists%20the%20vmSize%20and%20Status%20of%20running%20%2F%20deallocated%20as%20well%20as%20other%20pertinent%20information.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20out%20put%20is%20then%20formatted%20to%20JSON%20and%20posted%20to%20the%20log%20analytics%20Rest%20API%20where%20I%20have%20a%20custom%20Log%20called%20RunningVMs_CL%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20would%20like%20to%20be%20able%20to%20do%20is%20calculate%20any%20VM%20running%20more%20than%208%20hours%20and%20up%20to%2040%20hours%20and%20be%20able%20to%20alert%20on%20it%20when%20it%20reaches%20above%208%20hours%20of%20runtime%20per%20day%20or%20more%2C%20and%20then%20when%20it%20reaches%2040%20hours%20of%20total%20run%20time.%26nbsp%3B%20The%2040%20hours%20might%20be%20a%20bit%20difficult%20to%20check%20as%20logs%20are%20only%2031%20days%20old%20at%20max.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBeing%20new%20to%20Log%20Analytics%20language%20I'm%20struggling%20to%20find%20the%20right%20commands%20to%20use%20to%20facilitate%20at%20least%20the%208%20hour%20calculation%2C%20any%20tips%20on%20how%20I%20should%20approach%20this%20query%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3Ejohn%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-191753%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECustom%20Logs%20and%20Custom%20Fields%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-193725%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Virtual%20Machine%20runtime%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-193725%22%20slang%3D%22en-US%22%3E%3CP%3EGreat!%20you%20can%20read%20on%20changing%20retention%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Flog-analytics%2Flog-analytics-manage-cost-storage%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%26nbsp%3Band%20make%20sure%20you%20can%20easily%20manage%20your%20costs%20without%20concerns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-193444%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Virtual%20Machine%20runtime%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-193444%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Noa!%26nbsp%3B%20Did%20not%20know%20that%20functionality%20was%20added%20to%20the%20portal...greatly%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-193380%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Virtual%20Machine%20runtime%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-193380%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20John%2C%3C%2FP%3E%0A%3CP%3ERetention%20is%20up%20to%20you.%20To%20configure%2C%20%22Usage%20and%20estimated%20costs%22%20on%20the%20Log%20Analytics%20workspace%20menu%2C%20and%20in%20it%20%22Data%20volume%20management%22%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F34063i77CED901D4353C04%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22retention.png%22%20title%3D%22retention.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-193349%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Virtual%20Machine%20runtime%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-193349%22%20slang%3D%22en-US%22%3E%3CP%3ENoa%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20this%20I'll%20adjust%20it%20for%20my%20table.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%2040%20hour%20requirement%20is%20a%20lifetime%20calculation%20of%20the%20VM%20runtime.%26nbsp%3B%20The%20customer%20I'm%20working%20with%20is%20providing%20a%20SaaS%20app%20%22evaluation%22%20to%20a%20customer%20which%20they%20only%20want%20to%20allow%20them%20to%20use%20it%20NO%20more%20than%2040%20hours%20total.%26nbsp%3B%20It's%20likely%20that%20they'd%20reach%20this%20in%20a%20week%2C%20but%20it's%20also%20likely%20they'd%20reach%20it%20in%2040%20days%20if%20they%20only%20use%20the%20VMs%20for%20an%20hour%20a%20day...Log%20analytics%20keeps%20data%20for%2031%20days%3F%20so%20if%20I%20calculated%20off%20of%20the%20data%20retained%20there%20could%20be%20a%20scenario%20where%20the%20usage%20is%2031%20hours%20and%20never%20reaches%2040.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ejohn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-193342%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Virtual%20Machine%20runtime%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-193342%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20John%2C%3C%2FP%3E%0A%3CP%3EThe%20exact%20query%20depends%20on%20your%20specific%20table%20structure%2C%20which%20I%20don't%20have%20(I%20see%20you%20provided%20the%20field%20names%20but%20I%20need%20access%20to%20the%20actual%20table%20to%20create%20a%20working%20example).%3C%2FP%3E%0A%3CP%3EI've%20created%20an%20example%20query%20based%20on%20the%20Heartbeat%20table%2C%20you%20can%20adjust%20it%20to%20meet%20your%20custom%20logs%3A%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CPRE%3EHeartbeat%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(7d)%0A%7C%20summarize%20heartbeats_per_hour%3Dcount()%20by%20bin(TimeGenerated%2C%201h)%2C%20Computer%0A%7C%20extend%20state_per_hour%3Diff(heartbeats_per_hour%26gt%3B0%2C%20true%2C%20false)%0A%7C%20summarize%20total_running_hours%3Dcountif(state_per_hour%3D%3Dtrue)%20by%20Computer%0A%7C%20where%20total_running_hours%20%26gt%3B%208%3C%2FPRE%3E%0A%3CP%3Eyou%20can%20also%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA22Quw7CMAxFdyT%252BwWMiZYAJlnZhgA9gr1LqNJGaBDmOeIiPpw8hKOps3%252BPje0JNXKPm9eoFN4uEcHYejxiQNGMDJeg2il0jh4WUvdfkngj2k0vVFamyMVNxiTmwkFA%252FoHZBzDgKtlYqOER%252FzYw0wPDOGBpI3M%252B%252FEGeMWICXGwVMGRUY3SWUMNfhyLqrKIfgQjsG0qTjjPg7UAyYUfJXZnp9AdMXsH8D4E%252FPEiUBAAA%253D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Erun%20it%20on%20our%20demo%20environment%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3Ebasically%2C%20this%20query%20finds%20computers%20that%20have%20been%20running%20for%20more%20than%208%20hours%20(total)%20over%20the%20last%207%20days.%20I%20am%20not%20sure%20why%2040%20hours%20would%20be%20more%20complicated%2C%20can%20you%20explain%20what%20you%20meant%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%2C%3C%2FP%3E%0A%3CP%3ENoa%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-193079%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Virtual%20Machine%20runtime%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-193079%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20organization%20is%20just%20now%20starting%20with%20Log%20Analytics%20and%20I've%20been%20looking%20at%20how%20we%20can%20use%20it%20to%20measure%20out%20Azure%20VM%20utilization.%20In%20particular%2C%20we'd%20like%20to%20check%20for%20any%20machines%20that%20might%20be%20very%20underutilized.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20started%20with%20just%20looking%20at%20the%20normal%20things%20like%20Processor%2C%20Memory%2C%20Disk%20usages.%20Comparing%20that%20to%20the%20hardware%20profile%20of%20VM%20might%20be%20interesting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20some%20other%20folks%20can%20share%20what%20they%20have%20done%20in%20this%20space%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20is%20there%20an%20easy%20way%20to%20get%20the%20schema%20of%20virtual%20machines%20or%20really%20any%20of%20the%20Log%20Analytics%20name%20spaces%3F%26nbsp%3B%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-SUB%20id%3D%22lingo-sub-191757%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Virtual%20Machine%20runtime%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-191757%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20fields%20I%20have%20to%20work%20with%20on%20this%20are%3C%2FP%3E%3CP%3ELocation_s%20-%20string%20%2F%20region%20name%3C%2FP%3E%3CP%3EState_s%20-%20string%20%2F%20status%20of%20the%20vm%20%2F%20running%20or%20deallocated%3C%2FP%3E%3CP%3Etype_s%20-%20string%20%2F%20hardwareprofile.vmsize%20(e.g.%20nv24%2C%20f2%2C%20etc)%3C%2FP%3E%3CP%3Etimegenerated%20-%20automatically%20created%20during%20ingestion%20of%20the%20log%3C%2FP%3E%3CP%3Evmname_s%20-%20string%20%2F%20vmname%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethere%20are%20other%20fields%2C%20but%20not%20relevant%20to%20the%20query%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hello All,

I currently have an azure automation runbook that executes a get-azurermvm command against all the subscriptions in our tenant.  This script compiles a table, that lists the vmSize and Status of running / deallocated as well as other pertinent information. 

 

The out put is then formatted to JSON and posted to the log analytics Rest API where I have a custom Log called RunningVMs_CL

 

What I would like to be able to do is calculate any VM running more than 8 hours and up to 40 hours and be able to alert on it when it reaches above 8 hours of runtime per day or more, and then when it reaches 40 hours of total run time.  The 40 hours might be a bit difficult to check as logs are only 31 days old at max.  

 

Being new to Log Analytics language I'm struggling to find the right commands to use to facilitate at least the 8 hour calculation, any tips on how I should approach this query?

Thanks

john

 

7 Replies
Highlighted

The fields I have to work with on this are

Location_s - string / region name

State_s - string / status of the vm / running or deallocated

type_s - string / hardwareprofile.vmsize (e.g. nv24, f2, etc)

timegenerated - automatically created during ingestion of the log

vmname_s - string / vmname

 

there are other fields, but not relevant to the query needed.

Highlighted

My organization is just now starting with Log Analytics and I've been looking at how we can use it to measure out Azure VM utilization. In particular, we'd like to check for any machines that might be very underutilized.

 

I started with just looking at the normal things like Processor, Memory, Disk usages. Comparing that to the hardware profile of VM might be interesting.

 

Maybe some other folks can share what they have done in this space?

 

Also, is there an easy way to get the schema of virtual machines or really any of the Log Analytics name spaces? 

 

Thanks

 

Highlighted

Hi John,

The exact query depends on your specific table structure, which I don't have (I see you provided the field names but I need access to the actual table to create a working example).

I've created an example query based on the Heartbeat table, you can adjust it to meet your custom logs:

 
Heartbeat
| where TimeGenerated > ago(7d)
| summarize heartbeats_per_hour=count() by bin(TimeGenerated, 1h), Computer
| extend state_per_hour=iff(heartbeats_per_hour>0, true, false)
| summarize total_running_hours=countif(state_per_hour==true) by Computer
| where total_running_hours > 8

you can also run it on our demo environment.

basically, this query finds computers that have been running for more than 8 hours (total) over the last 7 days. I am not sure why 40 hours would be more complicated, can you explain what you meant?

 

HTH,

Noa

Highlighted

Noa,

 

Thanks for this I'll adjust it for my table.  

 

The 40 hour requirement is a lifetime calculation of the VM runtime.  The customer I'm working with is providing a SaaS app "evaluation" to a customer which they only want to allow them to use it NO more than 40 hours total.  It's likely that they'd reach this in a week, but it's also likely they'd reach it in 40 days if they only use the VMs for an hour a day...Log analytics keeps data for 31 days? so if I calculated off of the data retained there could be a scenario where the usage is 31 hours and never reaches 40.

 

john

Highlighted

Hey John,

Retention is up to you. To configure, "Usage and estimated costs" on the Log Analytics workspace menu, and in it "Data volume management":

retention.png

Highlighted

Thanks Noa!  Did not know that functionality was added to the portal...greatly appreciated!

Highlighted

Great! you can read on changing retention here and make sure you can easily manage your costs without concerns.