Simple Windows Print Accounting using Event Viewer data

This post continues where my previous post titled Export events including Event Properties from Windows Event Viewer left off.

The data I’m going to work with was exported using eventquery.vbs and saved in a CSV-file, comma separated file and it is presented in the form shown below.

"Information","10","12.05.2009 13:24:48","Print","Servername","None","AD\username","Document 232, filename.pdf owned by username was printed on printername via port IP_192.168.0.254. Size in bytes: 279232; pages printed: 18"

I’m interested in the username, date/time printed and pages printed and will now show how I’ve accomplished that using some simple linux console commands.

awk -F, '{print $7 " " $3 " " $4 " " $NF}' Event_Viewer_System.csv | grep printername | awk '{print $1 "," $2 "," $3 "," $NF}' | sed 's/\"//g'|sort > PrintAccounting.csv

Result

username,14.05.2009,12:58:41,18
username,15.05.2009,09:24:13,2
username,15.05.2009,09:25:00,37
username,15.05.2009,09:30:03,2
username,15.05.2009,09:30:29,2
...

Where the fields contain username, date, time and the amount of printed pages.

A short description on whats being done

  • print out column 7, 3, 4 and last column where the separator is a comma (,) from the file Event_Viewer_System.csv
  • filter out the printer you are interested in
  • filter out again the data we are interested in
  • remove quote sign (“) from the list
  • sort the list ny username
  • redirect the output to a file

And that’s how you make a primitive print accounting system from data gathered in a Windows Server.
This particular example has been testen on data from a Windows 2003 Server, but I think it can be performed on other versions of Windows as well.

Exporting last name, first name and username from Active Directory using AdFind

AdFind is a Windows command line Active Directory query tool. It is a mixture of ldapsearch, search.vbs, ldp, dsquery, and dsget tools with a ton of other cool features thrown in for good measure.

This post describes how I managed to export

  • Last name (surname/sn)
  • First name (givenName)
  • Username (samaccountname)

from a Active Directory and save the result to a comma separated file (CSV)

adfind.exe -b ou=ActiveDirectory,dc=example,dc=com -f "objectClass=user" sn givenName  samaccountname -nodn -adcsv > exported_users.csv

The result of this command is as follows

"Last name","First name","username"

This is a nicely formatted csv file that makes it easy to work with.

What values are available and can be searched for in a Active Directory?
If you are uncertain on the name of what you are looking for, then this line comes handy

adfind.exe -b ou=ActiveDirectory,dc=example,dc=com -s subtree |more


Remember to use the | more at the end of the line because this command lists a lot of Active Directory content.

The result of this command can contain some of the following values

cn, sb,giveNnAME,  distinguishedname, instanceType, whenCreated, whenChanged, displayName, uSNCreated, memberOf, uSNChanged, department, homeMTA, proxyAddresses, homeMBD, mDBUseDefaults, mailNickName, name, objectGUID, userAccountControl, badPwdCount, codePage, countryCode, homeDirectory, homeDrive, badPasswordTime, lastLogoff, lastLogon, logonHours, pwdLastSet, primaryGroupID, userParameters, profileParh, objectSid, accountExpires, logonCount, sAMAccountname, SamaccountType, showInAddressBook, msNPAllowDialoin, dSCorePropagationData, lastLogonTimestamp, textEncodeORAddress, mail, middleName, msExchaPoliciesExcluded, msExchHomeServerName, msExchALObjectVersion, msExchMailboxSecurityDescriptor, msExchUserAccountControl, msExchMailboxGuid

The values above are usually assosiated with a useraccount.

Import csv data to mysql

This is a short note about how to import comma separated data, CSV, from a file into a mysql database from a shell.

  1. Log in to your mysql database and choose the database you are going to import into.
  2. Type in the following in the mysql console to import from the CSV file
    load data infile '/home/username/data.csv' into table program fields terminated by ';' lines terminated by '\r\n';

MDB tools to export (migrate) from mdb (Microsoft Access format) to mySQL

This convertion could be obtained by first converting the tables in the mdb-file into CSV-files (Comma Separated Values) using MDB Tools and then by importing the csv-files into MySQL using phpMyAdmin.

To export a given table from a mdb-file, we type:

mdb-export db.mdb customers >> customers.csv

In my case, since I got some danish characters, I need to force mdbtools to encode everything as ISO8859-1 instead of UTF-8, which is the default. This can be done by setting the environment variable MDB_ICONV to ISO8859-1

export MDB_ICONV="ISO8859-1"

The resulting CSV-files could be imported into MySQL using phpMyAdmin.