VMware Cloud Community
BrianGordon84
Contributor
Contributor
Jump to solution

Import-CSV and Date Issues

Ok, I've got a CSV file that I store a lot of information on VM's like requestor name, decommission date, server name, etc, etc.

I'm trying to create a script to automate the decommission process of auto generating an e-mail to the requestor with the server name and decom date to see if they need to extend the server lease or delete it. I'm having some issues with the Get-Date cmdlt though. I'd like to run this script once a week via windows scheduled task. I would like to target VM's whose decomission date is within 2 weeks.

There's something I'm missing here.

$Date = (Get-Date -DisplayHint Date).AddDays(14) everytime I do this it adds the Time...

$decom_info = Import-CSV Book.csv

foreach($project in $decom_info){

if($project.Decom_Date -ilt $Date){

send e-mail code

}

For some reason this is inaccurate though. I expect because the "Decom_Date" column is being pulled in as a string or something. Any ideas?

Reply
0 Kudos
1 Solution

Accepted Solutions
LucD
Leadership
Leadership
Jump to solution

The date field your read from the CSV file is indeed a string.

But you easily 'cast' this to a DateTime object.

See the following sample.

$t = "09/21/2010"
$t.GetType()
$s = [datetime]$t
$s.GetType()

Since there are some square brackets in the code and the forum SW doesn't like these, I attached the code

____________

Blog: LucD notes

Twitter: lucd22


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

View solution in original post

Reply
0 Kudos
8 Replies
LucD
Leadership
Leadership
Jump to solution

The date field your read from the CSV file is indeed a string.

But you easily 'cast' this to a DateTime object.

See the following sample.

$t = "09/21/2010"
$t.GetType()
$s = [datetime]$t
$s.GetType()

Since there are some square brackets in the code and the forum SW doesn't like these, I attached the code

____________

Blog: LucD notes

Twitter: lucd22


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
BrianGordon84
Contributor
Contributor
Jump to solution

Thanks Luc. You're awesome.

I never really used the .GetType() before, lol. That would have confirmed my thoughts but I didn't know how to convert the string to a datetime object.

Reply
0 Kudos
BrianGordon84
Contributor
Contributor
Jump to solution

I'm having another issue with this script because of the formatting of the csv file. I'm not sure if I'll be able to do what I want to here. I've attached the file so you can take a look at how its formatted. Any advice or ideas on this one?

Again, what I need to do is take a project, say 3-4 or 14 vm's (whatever really) and see if they are within 14 days of their scheduled decommission. Then send an auto generated e-mail to the owner listed in the document. I'm having problems with the blank cells and I'm just not sure if at this point it's possible to do with a csv.

Here's the code I have so far:

$date = (Get-Date -DisplayHint Date).AddDays(14)

$tracking_doc = Import-Csv Book2.csv

foreach($project in $tracking_doc)

{

if($project.Decom_Date -eq ""){}

else

{$Decom_Date = $project.Decom_Date}

if($project.Decom_Date -ilt $date){

Write-Host $project.project

Write-Host $project.VM_Name

Write-Host $project.Decom_Date

#Just checking to make sure it's grabbing the right vm's, which it is.

#Auto e-mail code, not finished

# $Outlook = New-Object -ComObject Outlook.Application

# $mail = $Outlook.CreateItem(0)

# $mail.Recipients.Add("ADD USER")

# $mail.Subject = "ADD SUBJECT"

# $mail.Body = $body

# $mail.Send()

}

}

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Your CSV looks ok.

What is in fact the problem you encounter ?

____________

Blog: LucD notes

Twitter: lucd22


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
BrianGordon84
Contributor
Contributor
Jump to solution

lol.

For example, if you look at the csv the project test. All of these VM's are past due for decommission. How do I grab all of these in the same e-mail without sending multiple e-mails?

The e-mail will be some sort of default template like:

Subject: $Project(test) servers to be decommissioned ($server1, $server2, etc)

Body:

Hi, $owner

Tracking documents show that server(s)

1. $server1

2. $server2

etc

are set to be decommissioned on 9/20/2010. Can we go ahead and retire them or do you need to extend the time?

Thanks,

I know I can get the second row, it's got the project name on it but rows 3-4 do not have the project name. Say row 3 has a different owner than row 2 so I've got to auto send out 2 e-mails addressed to different people but with the same information in it. I'm not sure how to get the project name from row 2 or any other project for that matter. I can see what I want to do but not sure if I'm explaining it correctly.

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

I think the best solution would be to use a Group-Object cmdlet on the mail address.

Something like this

$date = (Get-Date -DisplayHint Date).AddDays(14)
$tracking_doc = Import-Csv Book2.csv
$report = @()

foreach($project in $tracking_doc)
{
	if($project.Decom_Date -eq ""){}
	else
	{		$Decom_Date = [datetime]$project.Decom_Date}


	if($Decom_Date -ilt $date){
		$report += New-Object PSObject -Property @{
			VMname = $project.VM_Name
			DecomDate = $Decom_Date
			Email = $project.Owner
			Project = $project.Project
		}
	}
}	

$Outlook = New-Object -ComObject Outlook.Application
$report | Group-Object -Property Email | %{
	$mail = $Outlook.CreateItem(0)
	$mail.Recipients.Add($_.Name)
	$mail.Subject = "Servers to be decommissioned"
	$_.Group | %{
		$mail.Body +=("Project:" + $_.Project + " Guest" + $_.VMname | Out-String)
	}
	$mail.Send()
}

____________

Blog: LucD notes

Twitter: lucd22


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
BrianGordon84
Contributor
Contributor
Jump to solution

I see what you're doing here. Am I able to regroup the object more than once in the same script? Problem now is I can't get the Project title in the subject. The blank cells are causing some issues. Is there a way to ignore the blank cells? Take a look at the output I get from the additions you made.

Project:App_Dev Guestvm1

Project: Guestvm2

vm2 is apart of the App_Dev project but the project is blank there because of the blank cell on the csv row 3.

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Yes, you can group on multiple properties.

Have a look at my PowerCLI & vSphere statistics – Part 4 – Grouping post. It's about statistics but it should give you a good idea what you can do with the Group-Object cmdlet.

____________

Blog: LucD notes

Twitter: lucd22


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos