Forum Discussion

Syed_Aman's avatar
Syed_Aman
Copper Contributor
Jul 03, 2019

CPU utilization for VMs in past 3 months in different time zone (PST) for Specific working hours

Hi Team,   I need a help in getting the average CPU utilization for VMs in last 3 months in different time zone (PST) only for specific time range.   i have written the query to fetch the average...
azure monitor
Custom Logs and Custom Fields
CliveWatson's avatar
CliveWatson
Former Employee
Jul 03, 2019

Syed_Aman 

 

Line 1, change to how many days to go back  - I used 7days

Line 9 & 10 - I assumed working hours of  9:00 thru 17:00 

 

let goBackDays = 7d;
Heartbeat
| where SubscriptionId != ''
| summarize by TenantId, SubscriptionId, Computer, ResourceGroup=tolower(ResourceGroup), ResourceId=tolower(ResourceId)
//| where ResourceGroup == "azrg-oc-ame-tds-vm"
| join kind=inner
(
Perf
| extend startOfHours = startofday(now() -goBackDays) + 9hr   // from 9am, nn days ago
| extend endOfHours =   startofday(now())     + 17hr          // to 5pm 
| where TimeGenerated between (startOfHours .. endOfHours)
| where (ObjectName == "Processor" and CounterName == "% Processor Time")
| summarize CPUAvg = (avg(CounterValue)), startHr = min(startOfHours), endHr = max(endOfHours) by Computer
)
on Computer
| project Computer , CPUAvg, startHr, endHr

Run and test it here:

Go to Log Analytics and Run Query

 

 

 

 

Syed_Aman's avatar
Syed_Aman
Copper Contributor
Jul 04, 2019

CliveWatson  Thanks a lot for the below query.

 

I had tested it in my environment but it is giving me the data in local timezone (IST) and i need to retrive the data for PST time zone from March to June 2019.

 

I tried changing the setting in Azure log Analytics workspace for timezone to UTC- 7:00 Pacific time.

 

But it is still giving the output in local timezone.

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Jul 04, 2019

    Syed_Aman 

     

    Go to Log Analytics and Run Query

     

    let goBackDays = 7d;
    Heartbeat
    | where SubscriptionId != ''
    | summarize by TenantId, SubscriptionId, Computer, ResourceGroup=tolower(ResourceGroup), ResourceId=tolower(ResourceId)
    | join kind=inner
    (
    Perf
    | extend UTCstartOfHours = startofday(now() -goBackDays) + 9hr // from 9am, nn days ago
    | extend UTCendOfHours = startofday(now()) + 17hr // to 5pm
    | where TimeGenerated between (UTCstartOfHours .. UTCendOfHours)
    | where (ObjectName == "Processor" and CounterName == "% Processor Time")
    | summarize CPUAvg = (avg(CounterValue)), startHr = min(UTCstartOfHours), endHr = max(UTCendOfHours) by Computer, TimeGenerated
    )
    on Computer
    | extend PSTTimestampStart = startHr - 7h
    | extend PSTTimestampEnd = endHr - 7h
    | project Computer , CPUAvg, startHr, endHr, PSTTimestampStart, PSTTimestampEnd
     

Resources