Wednesday, January 28, 2015

#42 : How to change modified date of file using Powershell?

I always had a technique in Unix which converts modified date of a file. But never tried the same in Windows. But it was again possible in Windows using MKS Toolkit or Cygwin. There are very rare situation where you need to modified date of file. I don't want to go depth of reason, let's see
how we can do it.

DESCRIPTION

Run the below command:
ls

Directory: C:\Users\admin\Downloads


Mode LastWriteTime Length Name
---- ------------- ------ ----
-a--- 9/22/2012 9:37 PM 4750740 Setup_BullzipPDFPrinter_9_0_0_1437.zip
-a--- 9/23/2012 10:10 PM 58158 webcam-toy-photo1.jpg
-a--- 9/23/2012 10:10 PM 58158 webcam-toy-photo2.jpg



Ok, you may notice the LastWriteTime column. This column can be simply used to change the date and time.

Let's rename webcam-toy-photo2.jpg with modified date as 9/23/1942 10:10 PM.

Run the below command:

ls | where { $_.Name -eq "webcam-toy-photo2.jpg" } | foreach { $_.LastWriteTime="9/23/1942 10:10 PM" }

Let's check the date again. The date is of 1942.

ls

Directory: C:\Users\admin\Downloads


Mode LastWriteTime Length Name
---- ------------- ------ ----
-a--- 9/22/2012 9:37 PM 4750740 Setup_BullzipPDFPrinter_9_0_0_1437.zip
-a--- 9/23/2012 10:10 PM 58158 webcam-toy-photo1.jpg
-a--- 9/23/1942 10:10 PM 58158 webcam-toy-photo2.jpg



Enjoy Scripting!!

Thursday, January 22, 2015

#41 : View Process Commandline with Powershell

Process can be viewed easily in Powershell with Get-Process command. This command is useful, but it has some shortcomings -
1. It does not show the command-line and arguments passed to the command.
2. It does not show the Path of command.

Due to these shortcomings, I started looking for some option by which we can get the list of processes with complete details. The search completed and it came with WMI and Powershell combination. Below is the single line which can solve this problem.

Get-WmiObject Win32_process |   select  ParentProcessId, ProcessId, Name, Commandline  | ft

Based on the above, you can write a script which could kill processes by specific application, but be careful, killing a operating system process might shutdown the machine.

Happy scripting!!

Wednesday, January 14, 2015

#40 : Reading, Updating and Inserting Data into Excel with Powershell

We often find ourself into a situation when we have to create or write into Excel sheet. This tasks can be done with two ways -
1. Using Excel COM object
2. Using Jet Engine

Using COM object is fairly simple and anyone can copy and paste from a list of sources. The drawback of using Excel COM object is that you need to have Excel application installed on the machine. But this condition is not applicable to production servers where mostly Excel and other Microsoft office tools are not installed due to security threats. Basically you will find them installed on your workstations. Considering these points I was looking for a good method to connect to Excel and update some records in Excel sheet.

Without wasting your time and mine too, let's look into code which can do it for you. I know, if you are good programmer, you don't have to break your head much. If you are a beginner, this blog is not that suitable (I must say). I am busy person and I don't have this only job (Sorry).

1. Sample Code to read data from Excel sheet -

Testing Preparations:
1. Create an Excel document. (If you don't have Microsoft Office in your workstation, you may ask your IT dept. to install OpenOffice or LiberOffice).
2. On first row of sheet as Name and insert some records-
Name
Som
Manu
Ravi
Akash

3. Save and close the doc. I assume, you saved it as F:\test.xls.

Code to Read data from Excel Sheet-

$strFileName ="F:\test.xls"
$strSheetName = 'Sheet1$'
$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"
$strQuery = "Select * from [$strSheetName]"

$strQuery = "Select * from [$strSheetName]"
$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn
$objConn.open()
$DataReader = $sqlCommand.ExecuteReader()

While($DataReader.read())
{
$ComputerName = $DataReader[0].Tostring() 
echo $ComputerName 
}  
$dataReader.close()
$objConn.close()

Code to Update data in Excel Sheet-
(Below code will update all names with Som)

$strFileName ="F:\test.xls"
$strSheetName = 'Sheet1$'
$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"

$strQuery = "Update [$strSheetName] set Name = 'Som' "
$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn
$objConn.open()
$sqlCommand.ExecuteNonQuery()
$objConn.Close()

Code to Insert data in Excel Sheet-
(Below code will insert one more row)

$strFileName ="F:\test.xls"
$strSheetName = 'Sheet1$'
$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"

$strQuery = "Insert into [$strSheetName] values ('Ravi')"
$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")

$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)

$sqlCommand.Connection = $objConn
$objConn.open()

$sqlCommand.ExecuteNonQuery()
$objConn.Close()

Hope, this will help you.

Enjoy scripting!!

Wednesday, January 7, 2015

#39 : How to write in Windows Event Log with Powershell?

Happy new year!

It has been long since I wrote something in this blog. Basically I got
busy with ASP.NET Programming and I am paying more attention to ASP.NET
these days. Ok, let's start with this.


GENERAL DESCRIPTION

Writing into Windows Event log requires two steps:

1. Creating a log entry about the application. This is mandatory to have
an entry for your application or script in Eventlog. The below statement
will fail if the source name already exists.

New-EventLog -LogName Application -Source "YourScriptName"


2. Writing into EventLog
It can be done with the below stateement:

Write-EventLog -logname Application -source "YourScriptName" -eventID 3001
-entrytype Information -message "The message you want" -category 1
-rawdata 10,20

IMPLEMENTATION

I would suggest it can be implemented with below concept:
I will create a single function in my script which will log from several
locations. So, this will be easier for you to use the same function
everywhere.


$SCRIPT_NAME="MyScriptName"

function log_this([string]$MESSAGE)
{

if ( !([System.Diagnostics.EventLog]::SourceExists($SCRIPT_NAME)) )
{
New-EventLog -LogName Application -Source $SCRIPT_NAME
}

Write-EventLog -logname Application -source $SCRIPT_NAME -eventID 3001
-entrytype Information -message $MESSAGE -category 1 -rawdata 10,20

}

log_this "Failed to peform something"


CMDLETS USED

Write-EventLog
New-EventLog


CONCLUSION

As you saw above, I have used [System.Diagnostics.EventLog]::SourceExists
method for find the existence of your application. This method is really
useful because, if you do not know whether your application/script source
name is entered or not, it will throw error.


with regards,
Som Dutt Tripathi

#112: How to handle xml document in Powershell?

 In PowerShell, you can handle XML data using various cmdlets and methods provided by the .NET Framework. Here's a basic guide on how to...