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 CPU utilization for last 3 months and i set the time range in portal option.

 

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
| where (ObjectName == "Processor" and CounterName == "% Processor Time")
| summarize CPUAvg = (avg(CounterValue)) by Computer
)
on Computer
| project Computer, CPUAvg

 

 

how can i filter the CPU utilization only for 12 hours\day in PST time zone for last three months.

 

Thanks in advance.

  • 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

      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 retrieve the data for PST time zone from March to June 2019.

       

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

       

      But it is still giving the output in local timezone.

    • Syed_Aman's avatar
      Syed_Aman
      Copper Contributor

      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
        Icon for Microsoft rankMicrosoft

        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