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

Occasional Contributor

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.

4 Replies

@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

 

 

 

 

@Clive Watson  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.

@Clive Watson  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 

 

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