import custom Windows event viewer logs form csv into sql table

Question

I have a powershell command taking event viewer logs from a remote windows server and putting it into a csv file and then displaying it and additionally adding it into the database (sql server)

String command = "powershell.exe cd 'Path\\Output'; "
        + "$password = get-content 'Path\\Output\\"
        + serverClicked + "' | convertto-securestring; $username = 'administrator'; "
        + "$cred= New-Object System.Management.Automation.PSCredential($username, $password); "
        + "Invoke-Command -ComputerName " + serverClicked + " -Credential $cred -Authentication Default -ScriptBlock "
        + "{ Get-EventLog -LogName " + logName + " -EntryType " + entryType + " -Newest 50 | Select-Object -Property PSComputerName, TimeGenerated, EventID, Message, Source, EntryType}"
        + " | Out-File " + serverClicked + "." + logName + entryType + "." + curDate + ".csv; cat " + serverClicked + "." + logName + entryType + "." + curDate + ".csv";
System.out.println(command);

Process powerShellProcess;
powerShellProcess = Runtime.getRuntime().exec(command);
powerShellProcess.getOutputStream().close();
String line;
System.out.println("Output:");
BufferedReader stdout = new BufferedReader(new InputStreamReader(powerShellProcess.getInputStream()));
ShowInfoTextArea.setText("\n");

//------------------------------------
//Going to try adding the data to the database
DBConnect d = new DBConnect();
Connection con = d.getConnection();
Statement stmt = con.createStatement();
stmt.executeQuery("INSERT INTO EventViewer(TimeGenerated, EventID, Message, Source, EntryType, PSComputerName) VALUES ('" + timeGenerated + "', '" + eventID + "', '" + message + "', '" + source + "', '" + entryType2 + "', '" + pSComputerName + "');");
System.out.println("INSERT INTO EventViewer(TimeGenerated, EventID, Message, Source, EntryType, PSComputerName) VALUES ('" + timeGenerated + "', '" + eventID + "', '" + message + "', '" + source + "', '" + entryType2 + "', '" + pSComputerName + "');");
stmt.close();
stmt.close();
con.close();
//------------------------------------

This code runs a powershell command and takes the output into a csv file and then i need to take that output and put it into separate fields into the database setup as:

[TimeGenerated] [varchar](50) NOT NULL,
[EventID] [varchar](50) NOT NULL,
[Message] [varchar](500) NOT NULL,
[Source] [varchar](50) NOT NULL,
[EntryType] [varchar](50) NOT NULL,
[PSComputerName] [varchar](50) NOT NULL,
[LogName] [varchar] (50) NOT NULL

Either i need to figure out the Java to get it correctly into the database or a better way of doing it. The main problem i have faced is that the message part of the event is on multiple lines

Thanks for your help in advance

Answer 1

I cannot test this but I could see an issue with your output line

+ " | Out-File " + serverClicked + "." + logName + entryType + "." + curDate + ".csv; cat " + serverClicked + "." + logName + entryType + "." + curDate + ".csv";

Out-File is not going to make a proper CSV and could also be introducing encoding issues. Also it looks like you are reading the file back in. If that is the case we can skip that step entirely by just using ConvertTo-CSV which was designed to work with the object you are outputting from Get-EventLog. So I would change the last line of command to instead...

+ " |  ConvertTo-Csv -NoTypeInformation

That should get you a working copy of what you are looking for. If I am on the right track and you still have issues I will need you to debug for me.