Windows – Extract Log On / Log Off Times in Excel
Recently, with the war and everything, prices for energy started to go up and up. So I started monitoring my time spent at the PC from startup to shutdown. I do this in order to also track my expenses when it comes to PC consumed energy.
In this topic I’m gonna address the following:
1. Do the math on how much power your PC draws every day
2. Find out the cost per kWh (kilo-Watt-hour)
3. Extract the power on and power off times of your PC
4. Make an Excel file to monitor everything
1. Do the math on how much power your PC draws every day
Power consumption varies with the things you do on a PC, it differs whether you listen to music, watch youtube, play a game, do some water flow simulations etc. So I’m gonna always refer to the max consumption my PC has, summing up the max wattage of my GPU, CPU etc.
A. Calculate, on average, how many hours a day I keep the PC on
When I go to work I use the PC less and when I’m at home all day long I use it more. So.
During time off, Saturday, Sunday, various national holidays and my yearly holiday, there are 2 x 52 days during weekends + 14 national holidays (some of which might be during weekends so it depends, but let’s say 7 days are during weekdays) + 22 days for my yearly holiday from the company. So the total days during time off 104 + 7 + 22 = 133 days.
The remaining days are workdays, so 365 – 133 = 232. I don’t take leap years into account.
133 days a year the PC stays on, on average, 13 hours a day. That’s a total of 1.729 hours a year
232 days a year the PC stays on, on average, 8 hours a day. That’s a total of 1.856 hours a year.
The total hours a year is almost 3.600 hours, which on average is about 10 hours a day.
This is a raw calculation and somewhat close to reality. I’m doing the math in my Excel file (see point 4) so in the past 3 months and on average my PC stayed on for 8,766 hours a day.
B. How much power does my PC consume
Method 1 – do it using a website calculator to help out
For this purpose I used the Expert calculator provided by https://outervision.com/power-supply-calculator
I have 1 x CPU, 1 x GPU, 2 x DDR4 8GB RAM modules, 1 x NVMe SSD, 3 x SATA HDD 7.2K RPM, 1 x DVD-RW optical drive, 1 x gaming keyboard, 1 x gaming mouse, 1 x USB 3.0 device (external gaming SSD), 1 x 200 mm CPU fan, 1 x 140 mm fan, 3 x 120 mm fans, 2 x LED 21 inches monitors.
The calculator doesn’t have an option for 10 hours a day, it’s either 8 or 16 hours a day. Better choose more than less, so I’ll pick 16 hours utilization time and 8 hours of gaming (on average, I play half the time I spend on the PC). The calculation results in a load wattage of 406 W per hour. On 8 hours a day with 4 hours gaming I get 394 W per hour. So, on average, 400 W per hour. But that’s not entirely true. See Method 3.
Method 2 – do it manually
For this purpose you must look for every component’s power consumption. So you don’t have to tear your PC apart, search your components’ technical specs online to find the max power draw. Of course, you can take the max power draw of your case with everything in it by considering the wattage of your PSU, or 70-80% of that. Could be an option but not very close to reality.
Method 3 – combine the two above – RECOMMENDED
The website from method 1 doesn’t know, for example, what’s the power of your monitor/s. For example, if I choose zero monitors it still recommends me 406 W per hour, because they can’t possibly know what monitors you have. Also they don’t take into account other peripherals like headphones or speakers.
So, I do the math with the above calculator for things I do know and that are taken into account – CPU, GPU, RAM, NVMe, HDD, optical drive, keyboard, mouse, USB devices, fans, and then add the monitors, speakers and/or headphones to that math. I averaged my total consumption to 430 W per hour, so 0,43 kW per hour. But in reality it’s less than that.
Considering the 8,766 hours a day from letter A, that’s about 3.77 kW per day.
Times 30 days a month that’s about 113 kWh. In reality, all the appliances in my home consume about 121 kWh per month. Besides PC, that’s light bulbs, TVs, washing machine, vacuuming appliances, phone charging, laptop etc.
Thus, PC consumption is maybe around 0,32 kWh which is a total of about 75 kW per month for the PC alone.
You can calculate the wattage of all the appliances in your home. TV has wattage written on the back, phone on adapter, laptop on adapter etc.
2. Find out the cost per kWh (kiloWatt-hour)
This is from your electricity provider and the info is found in your electricity invoice. Due to recent price increases, the price was capped by the state to 3 intervals of price. I’m located in the middle interval, over 100 kWh and less than 250 kWh per month. So the price right now is around 0,16 euros / kWh. In the invoice there are applied additional taxes, like monthly subscription in euros / day which now is around 7 euros / month, and then VAT of 19% to the whole shebang.
3. Extract the power on and power off times of your PC
For this you will need a powershell script (ps1 extension) ran through a shortcut file (lnk extension).
A. Creating the powershell script to extract logon logoff times
If you check the Event Viewer, you will notice that event id 7001 is for Logon and 7002 for Logoff. The following code will extract the the dates and times of the past 50 events and save them as in D:\Times.txt, then open the txt file.
Open Notepad and paste the following code, then save with the ps1 extension in a safe folder you won’t delete. I saved directly in D drive: D:\Get Login-Logon Times.ps1
$filter = @{
Logname = 'System'
ID = 7001, 7002
ProviderName = 'Microsoft-Windows-Winlogon'
}
Get-WinEvent -FilterHashtable $filter -MaxEvents 50 |
Select TimeCreated, ID |
Out-File D:\Times.txt
Start notepad D:\Times.txt
B. Creating the shortcut to the script
This is required because by default you can’t run a powershell script by double clicking it, for security reasons. And it’s better to leave it this way. I keep the shortcut in the same folder as the script.
Right click anywhere and select New – Shortcut. In the field paste the following line replacing the path to your ps1 script file: powershell.exe -ExecutionPolicy Bypass -WindowStyle hidden -Command "& 'D:\Get Login-Logon Times.ps1'
Click Next and give it a name and Finish.
C. Starting the script with Windows
Simply put the shortcut in the following folder: %USERPROFILE%\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup
4. Make an Excel file to monitor everything
I made an Excel file containing startup and shutdown times in order to monitor how many hours my PC stays open every day and to approximate the cost. Put a shortcut to the Excel file also in the folder above, so it starts together with the script.
I’m not gonna get into details because everyone can set it however they want, but it looks like in the following image. Plus I have a table to sum hours and cost / month but image would have gotten too wide.
I want to find a method to extract times directly to Excel, same as above, but I’m not there yet. A lot to learn! Will post the method as soon as I have it, test it and it works.