Forum Discussion
Syed_Aman
Jul 03, 2019Copper Contributor
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.
- CliveWatson
Microsoft
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_AmanCopper 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_AmanCopper 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
Microsoft
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