locked
Convert a perl script to use in powershell instead RRS feed

  • Question

  • Hi, can anyone offer help/suggestions on converting a perl script into powershell?

    Or something that does the same as the below perl is supposed to do e.g. event log entries to SQLite database?

    Thank you

     #!usr/bin/perl
    
    use DBI;
    use Win32::EventLog; #v0.076 used for development
    use strict;
    use warnings;
    
    my $server="\\\\192.168.1.2"; #put UNC name or IP address here
    
    #creates/opens SQLite DB
    my $dbh = DBI->connect("dbi:SQLite:dbname=EventLogData.sql","","");
    
    #Removes Table Data if it already exists and creates new Table Data
    $dbh->do("DROP TABLE Data");
    $dbh->do( "CREATE TABLE Data (Record INTEGER PRIMARY KEY, EventLog,Server,Time,Source,Message,EventID);" );
    
    my %type = (1 => "ERROR",
    2 => "WARNING",
    4 => "INFORMATION",
    8 => "AUDIT_SUCCESS",
    16 => "AUDIT_FAILURE");
    
    
    $Win32::EventLog::GetMessageText = 1;
    my $i=0;
    #processes System, Appication, and Security Logs
    #inserts each event log record into SQLite DB
    for my $eventlog ("System", "Application", "Security") {
       my $handle = new Win32::EventLog($eventlog, $server)
       or die "Unable to open system log:$^En";
       $handle->GetNumber(my $recs)
            or die "Can't get number of EventLog records\n";
       $handle->GetOldest(my $base)
            or die "Can't get number of oldest EventLog record\n";
       my $j=0;
       while ($j < $recs) {
            $handle->Read(EVENTLOG_FORWARDS_READ|EVENTLOG_SEEK_READ,
                                      $base+$j,
                                      my $hashRef)
                    or die "Can't read EventLog entry #$j\n";
                    Win32::EventLog::GetMessageText($hashRef);
                    my $time=scalar localtime($hashRef->{TimeGenerated});
                    my $source=$type{$hashRef->{EventType}};
                    my $message=$hashRef->{Message};
                    my $eventID=($hashRef->{EventID}& 0xffff);
                    my $sql= 'INSERT INTO Data(Record, EventLog,Server,Time,Source,Message,EventID) VALUES (?,?,?,?,?,?,?)';
                    my $insert=$dbh->prepare($sql);
                    $insert->execute($i,$eventlog, $server,$time,$source,$message,$eventID);
            $j++;
            $i++;
    
    }}
    
    $dbh->disconnect();


    Darren Rose

    Saturday, August 26, 2017 3:47 PM

Answers

  • A quick look tells me that the perl code is getting records from the Windows Event logs and inserting them into a database and into a new table.  The rest is pretty easy to understand and I do not code in perl.

    The help I posted is about half of what you need.  The rest would require that you learn how to use a database with the Net framework.  If you are a VB.Net programmer you already know how to do this.

    For this trivial exercise I suggest converting to VB.Net then to PowerShell as this will show you how PowerShell is like VB.Net.  (more like C#).

    Once you have a VB.Net version then search for examples of the steps in VB.Net done in PowerSHell.

    # in nutshell

    1. drop and create db table
    2. get and enumerate eventlog records
    3. insert records into DB.


    \_(ツ)_/

    • Marked as answer by wingers Sunday, August 27, 2017 10:36 PM
    Saturday, August 26, 2017 5:25 PM

All replies

  • Saturday, August 26, 2017 4:03 PM
  • Please read this first: This forum is for scripting questions rather than script requests  
    Also find scripts here: http://gallery.technet.microsoft.com


    \_(ツ)_/

    Which is why I posted on the Windows Server > Windows Powershell forum not the Scripting > The Official Scripting Guys Forum! to which your first link refers too

    Sorry....


    Darren Rose

    Saturday, August 26, 2017 4:35 PM
  • Please understand that this is a forum for questions about scripting with PowerShell and not a free script conversion forum.  You are free to learn PowerShell and do the conversion.  Post here with specific questions.

    Please read all of the link posted to set your expectations: https://social.technet.microsoft.com/Forums/scriptcenter/en-US/c47b1bc2-f7fd-4d2e-8ff2-e8a81ce090d4/this-forum-is-for-scripting-questions-rather-than-script-requests?forum=ITCG

    If this is critical then consider contacting a consultant to do this for you.

    To access the event log with PowerShell start here:

    help Get-WinEvent -full

    Search for examples of using the event log.


    \_(ツ)_/


    • Edited by jrv Saturday, August 26, 2017 4:56 PM
    Saturday, August 26, 2017 4:55 PM
  • Okay fine - wasn't asking for advice, not someone to do it for me - hence asking a question on a forum for powershell questions!

    I work with VB.NET normally so not an expert on powershell and definitely don't know perl - again hence asking for ADVICE

    I fully understand the Get-Event command as used it before

    I have been on this forum nearly as many years as you have so don't need to read posts to set my expectations! - simply using a forum to ask a question

    Not to worry will go elsewhere


    Darren Rose

    Saturday, August 26, 2017 5:03 PM
  • If you are a programmer with experience then you should find it easy to convert the perl to PowerShell.  Start by reviewing the syntax of Perl then spend a weekend learning the basics of PowerShell.  You should be able to convert that trivial perl quite easily and it will improve your skills as a programmer.

    As a programmer I have had to do this dozens of times.

    With conversion we would not look for a line-by-line conversion.  We would try to understand what the perl does then research how to do the same thing with PowerShell.

    Start by finding out what the perl does and write it down in plain English.  Next search for scripts that do most of what you want.

    This will help you learn a basic skill of a programmer which is how to technically approach a problem when you have little understanding the technologies currently in use.  This will increase your skills as a technical programmer beyond that of a VB.Net coder.


    \_(ツ)_/

    Saturday, August 26, 2017 5:17 PM
  • A quick look tells me that the perl code is getting records from the Windows Event logs and inserting them into a database and into a new table.  The rest is pretty easy to understand and I do not code in perl.

    The help I posted is about half of what you need.  The rest would require that you learn how to use a database with the Net framework.  If you are a VB.Net programmer you already know how to do this.

    For this trivial exercise I suggest converting to VB.Net then to PowerShell as this will show you how PowerShell is like VB.Net.  (more like C#).

    Once you have a VB.Net version then search for examples of the steps in VB.Net done in PowerSHell.

    # in nutshell

    1. drop and create db table
    2. get and enumerate eventlog records
    3. insert records into DB.


    \_(ツ)_/

    • Marked as answer by wingers Sunday, August 27, 2017 10:36 PM
    Saturday, August 26, 2017 5:25 PM
  • okay fair point - thank you

    Yes I know how to do database etc in vb.net and eventually would do this from vb anyway, so can skip powershell and go straight to vb.net

    Just need to learn more about SQLite then as used Microsoft SQL previously, but wanted to try SQLite instead this time round

    Cheers


    Darren Rose

    Saturday, August 26, 2017 5:27 PM
  • SQLLite -> SQLServer...same thing different driver.  Both use the same ANSI SQL syntax and both can use the OLEDB or ODBC provider.  Build for SQLServer then change the driver.  Be careful to use only ANSI constructs.

    The plus with ADO.Net is that it is mostly vendor agnostic if you use OLEDB or ODBC.


    \_(ツ)_/

    Saturday, August 26, 2017 5:32 PM
  • As an example of how to learn any technology:

    I searched for "powershell sqllite" and clicked the first link returned.  It is 99% of the answer to your question.

    https://www.red-gate.com/simple-talk/sql/t-sql-programming/using-sqlite-powershell-sql-server/

    There are hundreds of examples listed in the search results along with many discussions about using SQLLite from PowerShell.

    The most important advice I can give is that you should spend more time analyzing you problem,  Ask simple questions and search for answers (research).  After you have collected information then you will be able to ask very targeted questions in a technical forum and you will likely get good answers.


    \_(ツ)_/

    Saturday, August 26, 2017 5:51 PM
  • Dissected Perl script and re-wrote it using website below as a guide on using SQLite with powershell and it is now working for me

    https://social.technet.microsoft.com/wiki/contents/articles/30562.powershell-accessing-sqlite-databases.aspx

    Thank you for the suggestions and


    Darren Rose

    Sunday, August 27, 2017 10:36 PM
  • And you learned how to go about converting almost anything.

    \_(ツ)_/

    Sunday, August 27, 2017 10:55 PM
  • Do you know much about SQLite? or does anyone else here who could help

    As above got powershell script working, but it is very slow to insert lots of data compared to SQL Server where I used bulkcopy

    How can I improve speed of the INSERT

    # import the assemblies 
    Add-Type -Path "C:\Program Files\System.Data.SQLite\2015\bin\System.Data.SQLite.dll"
    
    # choose log
    $LogName = "Application"
    
    # Registry key to store last run date & time
    $RegKey = "HKCU:\Software\Test\SQLite Events"
    
    # Initialise LastRun variable, make it old enough that all events will be collected on first run
    # Always use ISO 8601 format
    [datetime]$LastRunExeDll = "1977-01-01T00:00:00"
    
    if(Test-Path $RegKey){
        # Registry key exists, check LastRun value
        $LastRunValue = (Get-ItemProperty -Path $RegKey -Name $LogName -ErrorAction SilentlyContinue).$LogName
        if($LastRunValue -ne $null){
            $LastRunExeDll = $LastRunValue
        }
    }else{
        # Registry key does not exist, create it, then set the NewsID value and run full script
        Write-Host "Registry key not present"
        New-Item -Path $RegKey -Force | Out-Null
    }
    
    # connect to database
    $con = New-Object -TypeName System.Data.SQLite.SQLiteConnection
    $con.ConnectionString = "Data Source=D:\test.db"
    $con.Open()
    
    # table creation
    $sql = $con.CreateCommand()
    # delete table if already exists
    $sql.CommandText = "DROP TABLE Data"
    $adapter = New-Object -TypeName System.Data.SQLite.SQLiteDataAdapter $sql
    $data = New-Object System.Data.DataSet
    [void]$adapter.Fill($data)
    # create new table
    $sql.CommandText = "CREATE TABLE Data (Time,EventLog,Server,Source,Message,EventID INTEGER);"
    $adapter = New-Object -TypeName System.Data.SQLite.SQLiteDataAdapter $sql
    $data = New-Object System.Data.DataSet
    [void]$adapter.Fill($data)
    
    # Get the events logged since LastRun date & time
    Write-Host ("Collecting events from "+(Get-Date -Date $LastRunExeDll -Format s))
    $Events = Get-WinEvent -FilterHashtable @{logname=$LogName; starttime=$LastRunExeDll} -ErrorAction SilentlyContinue
    Write-Host ("Found "+$Events.Count+" events")
    
    # process data
     try{
        if($Events.Count -gt 0){
            # create connection and command
            $sql = $con.CreateCommand()
            $sql.CommandText = "INSERT INTO Data (Time,EventLog,Server,Source,Message,EventID) VALUES (@time, @eventlog, @server,  @source, @message, @eventID)" 
            # loop through data
            foreach($Event in $Events){
                $sql.Parameters.AddWithValue("@eventlog", $LogName) | Out-Null
                $sql.Parameters.AddWithValue("@server", $Event.MachineName) | Out-Null
                $sql.Parameters.AddWithValue("@time", $Event.TimeCreated) | Out-Null
                $sql.Parameters.AddWithValue("@source", $Event.ProviderName) | Out-Null
                $sql.Parameters.AddWithValue("@message", $Event.Message) | Out-Null
                $sql.Parameters.AddWithValue("@eventID", $Event.ID) | Out-Null
                $sql.ExecuteNonQuery() | Out-Null 
            }
        # Create/update the LastRun value - assuming all the above has worked - in ISO 8601 format
        New-ItemProperty -Path $RegKey -Name $LogName -Value (Get-Date -Format s) -Force | Out-Null
        Write-Host "Data uploaded to SQLite"
           }
        }
    catch{
        Write-Host "Problem uploading data to SQLite"
        Write-Error $error[0]
    }
    
    # dispose of your command and close the connection to the database
    $sql.Dispose()
    $con.Close()
    Write-Host "Connection closed to SQLite"


    Darren Rose

    Monday, August 28, 2017 12:43 PM
  • SQLLite does not support bulk copy which is SQLServer specific.

    Post in a SQLLite forum to learn how to use SQLLte efficiently.

    See: https://www.google.com/search?newwindow=1&q=sqlite+bulk+insert&oq=sqllite+bulk&gs_l=psy-ab.1.0.0i10k1l4.19932.20718.0.24916.4.4.0.0.0.0.226.852.0j1j3.4.0....0...1.1.64.psy-ab..0.4.848.CrcN7kTxsEo


    \_(ツ)_/

    Monday, August 28, 2017 6:17 PM