One nice thing about PowerShell is that it can talk to databases without a predefined ODBC connection. That makes them a lot more portable! I approve. However, I had trouble finding out how to set up and read data. So here is what I have.
##### Key variables
$SQLServerName="sqlserver"
$SQLDatabase="YourDatabaseInTheServer"
##### Start the database connection and set up environment
$DbString="Driver={SQL Server};Server=$SQLServerName;Database=$SQLDatabase;"
$DBConnection=New-Object System.Data.Odbc.OdbcConnection
$DBCommand=New-Object System.Data.Odbc.OdbcCommand
$DBConnection.ConnectionString=$DbString
$DBConnection.Open()
$DBCommand.Connection=$DBConnection
$InsertStatement="INSERT into Mbox_DB (MBServer, MBStore) values ('$MBServer', '$MBStore')"
$DBCommand.CommandText=$InsertStatement
$DBResult=$DBCommand.ExecuteNonQuery()
$SelectStatement="SELECT MBDBID From Mbox_DB WHERE (MBServer=$MBServer) AND (MBStore=$MBStore)"
$DBComand.CommandText=$SelectStatement
$DBResult=$DBCommand.ExecuteScalar()
$Date=get-date
$PurgeDate=$Date.AddMonths(-3)
$SelectStatement="SELECT Users From LastLogon WHERE (DateTime < '$PurgeDate')"
$DBCommand.CommandText=$SelectStatement
$DBResult=$DBCommand.ExecuteReader()
$UserTable=New-Object system.data.datatable
$UserTable.load($DBResult)
Yes, this is part of a larger script I'm writing. When that finishes, I'll probably post it too.
##### Key variables
$SQLServerName="sqlserver"
$SQLDatabase="YourDatabaseInTheServer"
##### Start the database connection and set up environment
$DbString="Driver={SQL Server};Server=$SQLServerName;Database=$SQLDatabase;"
$DBConnection=New-Object System.Data.Odbc.OdbcConnection
$DBCommand=New-Object System.Data.Odbc.OdbcCommand
$DBConnection.ConnectionString=$DbString
$DBConnection.Open()
$DBCommand.Connection=$DBConnection
$InsertStatement="INSERT into Mbox_DB (MBServer, MBStore) values ('$MBServer', '$MBStore')"
$DBCommand.CommandText=$InsertStatement
$DBResult=$DBCommand.ExecuteNonQuery()
$SelectStatement="SELECT MBDBID From Mbox_DB WHERE (MBServer=$MBServer) AND (MBStore=$MBStore)"
$DBComand.CommandText=$SelectStatement
$DBResult=$DBCommand.ExecuteScalar()
$Date=get-date
$PurgeDate=$Date.AddMonths(-3)
$SelectStatement="SELECT Users From LastLogon WHERE (DateTime < '$PurgeDate')"
$DBCommand.CommandText=$SelectStatement
$DBResult=$DBCommand.ExecuteReader()
$UserTable=New-Object system.data.datatable
$UserTable.load($DBResult)
Yes, this is part of a larger script I'm writing. When that finishes, I'll probably post it too.
SysAdmin1138:
I addiver at this post from here: http://serverfault.com/posts/154094/revisions
where you mentioned a larger script that (if I read correctly) will upload events from wevtutil to an ODBC connection. Where can I get a copy of that script?
Thanks,
-Mark
That script was custom designed for the environment it is in (as are most scripts) and isn't terribly generalizeable. However, I do go into some of the specifics of that script in a few other ServerFault postings:
- http://serverfault.com/a/154094/3038
That answer has the main guts of how to get going. It details how to dump the logs, how to loop through the resulting XML file, and how to access elements within that XML file.
I leave how to collate the extracted information into a datastructure for upload to the database as an exercise for the reader.