Using Apple Mac Console Event Log Files to Report Login and Logout Times for Calculating Computer Usage

Technology > Apple

Summary. This document describes how to create a spreadsheet that will show when users have logged in and logged out, making it possible to calculate the amount of time spent working on the computer. These instructions are for experienced users.

1. From Applications > Utilities, open the Console program. Navigate under the FILES heading to find the secure.log and click on it. From the File menu, choose Save a Copy As and save a copy of the secure.log file to your desktop.
   1. You may want to save archived secure.log files as well to gather older secure.log event data. The archived files are named secure.log.0.bz2, secure.log.1.bz2, secure.log.3.bz2 and so on up to secure.log.7.bz2.
   2. To configure your computer to maintain a longer history of secure.log event data, read the document Preserving Apple Console Secure.log Events File for Usage Analysis – Preventing Turned Over Purged Erased Data.
   3. If you are later aggregating and combining all data, remember that the order from oldest to newest will be secure.log.7.bz2 (for example) as the oldest, and then from that count down to zero and then have the data from your current secure.log file.
2. Start Excel. Create a new workbook, then choose File > Import > Text File > Import, select the secure.log file, click the Get Data button.
   1. You will need to set the Enable option to All Files in order to open the the secure.log file.
3. From the Text Import Wizard – Step 1 of 3, select Fixed width, then click Next.
4. From the Text Import Wizard – Step 2 of 3, make sure the Date and Time are isolated in a single column by creating a column break after the time. Remove all other column break lines by double clicking on them. A single click sets a field break and a double click removes a field break line. Click next when ready.
5. From the Text Import Wizard – Step 3 of 3, make sure the first column with the date and time is identified as being Date format. The year is missing from the date, but choose MDY anyway. Click Finish.
6. From the Import Data dialog box, select New sheet and then press the OK button.
7. The system.log file will now be displayed with the date and time in the first column.
8. As previously mentioned, the dates imported into column A were only Month and Day, so the current year was presumed. This is likely incorrect for some of the the first records listed, so you’ll need to search and replace the current year and replace it with the previous year only for the range of dates up to December. You can do this by selecting all cells from A1 down through and including entries for the month of December. A quick way to select the cells is to click on cell A1, then scroll down to the last entry for December, then, while holding Shift, click on the last cell that has a December date. This will select the range. From the Edit Menu, choose Replace. Under Find What, put the current year (e.g. 2011). Under Replace with, put the previous year (e.g. 2010). Click the Replace All button. This will correct the dates in the first column for the selected cells. You’ll get a message indicating how many replacements were made.
   1. If you have data spanning multiple years, you may need to start from the present and work backwards to determine where the years begin and end.
   2. If you are later aggregating and combining all data, remember that the order from oldest to newest will be secure.log.7.bz2 (for example) as the oldest, and then from that count down to zero and then have the data from your current secure.log file.
9. Select the data in the second column by clicking in cell B1 then pressing Shift + Control + Down Arrow. Then from the Edit menu, choose Copy.
10. Open Microsoft Word, and create a new blank document. From the View menu, be sure to choose Draft. This will avoid pagination and other formatting that may slow down the process.
11. From the Edit menu, choose Paste Special, select Unformatted Text, then press the OK button.
12. From the Edit Menu, choose Find, and then select Replace.
13. Replace every instance of ]: with ]^t
    1. When choosing what to replace, be sure to type ]: with a space following the colon. Choose replace all. This will replace the colon and space with a tab. When you paste this data back into Excel (as described next), it will go into two columns because of the tab separator. When done with the search and replace, close the sidebar if needed.
14. From the Edit menu, choose Select All. This should select all the text. If it doesn’t, try clicking on a word first and then performing select all. Once everything is selected, from the Edit Menu choose Copy.
15. Return to Microsoft Excel.
16. Select Column B by left clicking on the B at the top of the column. Then from the Edit menu, choose Delete.
17. Click on cell B1 to select it.
18. From the Edit menu choose Paste Special, choose Text, and then press the OK button.
19. What was previously a single column of data will now be two columns of data.
20. Quit Microsoft Word now. There’s no need to save the document that was temporarily used to modify the data, and you can select No to save the information in the clipboard.
21. Sort alphabetically on Column C. To do so, click on C1 and then press the AtoZ sort button in the ribbon toiler above.
22. Delete all rows that don’t contain the statement “Killing auth hosts” or “Login Window done” stated in column C.
23. Column B is no longer needed, so delete it. To do this, select column B by clicking on the B at the top of the column. Then from the Edit menu choose Delete.
24. Insert three empty columns to the left of column A. Do this by selecting column A through C (click A then drag to C highlighting all three), and then from the Insert menu choose Columns.
25. In the new Column A, cell A1, type in the computer name. An easy way to copy the computer name down column a for all the rows is to click in cell D1 to select it. Then press Control + Down Arrow to quickly jump to the bottom of the last row of data in that column. Press Control + Left Arrow to jump quickly to the adjacent cell in Column A. Press Shift + Control + Up Arrow to select all cells in Column A from the last row to the first. Press Control + D to fill down and copy the computer name to all highlighted cells. Press Control + Up Arrow to jump to the top again.
26. Insert a row at the top to write in descriptive column headings. To do this, select row 1 by clicking on the 1 to the left of row 1. Then from the Insert menu, choose Rows. In cell A1, type Computer. In cell B1, type Minutes. In cell C1, type Hours. In cell D1 type Date and Time. In cell E1, type Details.
27. Select row 2 by clicking on the 2 to the left of row 2. Then press Shift + Control + Down Arrow to select all rows of data. Then from the Data menu choose Sort, and choose to sort on Column D which is the Date and Time. Then click the OK button. This will put all rows back in chronological order.
28. Cell E2 should contain the words “Login Windows done” and cell E3 should contain “Killing auth hosts.” If there are duplicate entries for any “Killing auth hosts” remove them. If cell E2 contains “Killing auth hosts” then delete that row so that the row with “Login Window done” is the first row of data. Place the following formula in cell B3 =(D3-D2)*1440
29. Place the following formula in cell C3 =B3/60
30. Select columns B and C by clicking the B at the top of the column, click and drag to select columns B and C, then right click on the selected columns, choose Format Cells, from the Number tab choose Category > Number and click the OK button for the cells to have numbers with 2 Decimal places.
31. Select row 2 by clicking on the 2 to the left of row 2. Then press Shift + Control + Down Arrow to select all rows of data. Then from the Data menu choose Sort, and choose to sort on Column E. Make sure there is no check in the box next to My list has headers. This will sort all records into two groups – login (“Login Window done”) and logout (“Killing auth hosts”).
32. In cells B2 and C2 you will see #VALUE!. This is the formula that needs to be copied all the way down for all rows that indicate “Killing auth hosts”. To do this, click on cell B2, then use the scroll bar to scroll down and find the last row of data with “Killing auto hosts” in the Details column. Shift and click on the cell in column C that corresponds to the last row stating “Killing auto hosts.” This will select all cells in columns B and C beginning with row 2 that contains the formulas. Press Control + D to fill (copy) the formula down to all the selected cells.
33. Select row 2 by clicking on the 2 to the left of row 2. Then press Shift + Control + Down Arrow to select all rows of data. Then from the Data menu choose Sort, and choose to sort on Column D which is the Date and Time. Then click the OK button. This will put all rows back in chronological order.
34. If you see duplicate line entries for logout showing “Killing auth host” delete the second (or more) of the duplicates. If you see duplicate line entries for login showing “Login Window done” then delete the first of the duplicates. They won’t significantly impact the data analysis, but it’s best to remove them anyway.

Now you’ll have a spreadsheet showing the time logged in for each user session. Standard Excel formulas can be used to calculate the SUM, AVERAGE, and COUNT of login sessions.