n1ckml007
New Member
Posts:15
 |
| 31 Jul 2008 04:41 PM |
|
Hey,
I have an array built off a dataset of email addresses: $email = @($ds.Tables[0] | foreach { $_.email.trimend() }) | sort
the data set contains properties $_.last_name + $_.first_name
I need to compare this to an array of email addresses that is built from Exchange contacts:
$exchange_email = @(get-mailcontact -Organizationalunit "blah" | foreach {$_.externalEmailAddress} | foreach {$_.smtpaddress}) | sort
My issue is now I need to also compare the names of the contacts. Exchange uses the lastname, firstname format:
get-mailcontact -Organizationalunit "blah" | gm
Name Property System.String Name {get;set;}
So I either need to create a new datarow in the orginal table that joins the last_name first_name properties, or creat a new object to do the comparision, any suggestions?
(the get-member cmdlet seems to only work on [psobject]s )
|
|
|
|
|
marco.shaw
Co-Community Director
Basic Member
Posts:181
|
| 01 Aug 2008 03:54 AM |
|
I don't use Exchange regularly, but I have an Exchange VM to try to help out here and there. I can't seem to figure out how get-mailcontact works exactly. Can you provide an example of what the output string is?
What I gather, is if we can take that string, and re-arrange it for you, then you'll be all set? |
|
|
|
|
n1ckml007
New Member
Posts:15
|
| 01 Aug 2008 01:42 PM |
|
get-mailcontact is used for see what you have for mail-enabled contacts. A contact is used in this case, so that people with off-network email addresses, can still be included in distribution lists. What I'm trying to do is keep in sync a SQL database with the mailcontact OU.
get-mailcontact -Organizationalunit "blah" | foreach {$_.name} | sort
returns the "name" property, which is formatted last,name 1st name:
Bucky, Lori
Doe, John
Edwards, Ed
get-mailcontact -Organizationalunit "blah" | foreach {$_.externalEmailAddress} | foreach {$_.smtpaddress}
returns a list of email addresses.
lori@aol.com
John@ISP.com
ed@ed.com
So my first problem is I need to be able to combine the properties of:
$ds.Tables[0] | gm
last_name Property System.String last_name {get;set;}
first_name Property System.String first_name {get;set;}
To a format that matches how Exchange stores names.
|
|
|
|
|
n1ckml007
New Member
Posts:15
|
| 01 Aug 2008 01:44 PM |
|
properties of "$ds.Tables [ 0 ] | gm", "[ 0 ]" is getting encoded as 'Y] in this web form |
|
|
|
|
marco.shaw
Co-Community Director
Basic Member
Posts:181
|
| 01 Aug 2008 01:52 PM |
|
Posted By n1ckml007 on 08/01/2008 5:42 AM
So my first problem is I need to be able to combine the properties of:
$ds.Tables[0] | gm
last_name Property System.String last_name {get;set;}
first_name Property System.String first_name {get;set;}
To a format that matches how Exchange stores names.
So, you can't simply do something like this:
...|forach-object{$_.last_name+", "+$_.first_name}
This would combine the properties in the format you seem to require?
Sorry, if I'm missing something.
|
|
|
|
|
n1ckml007
New Member
Posts:15
|
| 01 Aug 2008 02:19 PM |
|
cool, that give me something to work with.
Next: Would I then make a new object with the "add-member" cmdlet, with the email address as the key property and the name as a property? Or is there a way to add / edit a row in the $ds.table[ 0 ] ?
|
|
|
|
|
marco.shaw
Co-Community Director
Basic Member
Posts:181
|
| 01 Aug 2008 02:32 PM |
|
Posted By n1ckml007 on 08/01/2008 6:19 AM
cool, that give me something to work with.
Next: Would I then make a new object with the "add-member" cmdlet, with the email address as the key property and the name as a property? Or is there a way to add / edit a row in the $ds.table[ 0 ] ?
Can you provide all of your SQL code? I'm assuming you're using SMO to work with the DB? You want to compare you're entries, then, if required, update the record back in the DB table?
I'm not a SQL/SMO expert, but I'm sure we can figure it out together... |
|
|
|
|
n1ckml007
New Member
Posts:15
|
| 01 Aug 2008 02:53 PM |
|
not sure what SMO is, I'm using an SQL query:
SELECT * FROM master_view
WHERE email like '%@%'
and what I think is a little .NET with PoSH code:
$conn = new-object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "server=*******;database=vip;UID=*******;PWD=*****"
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.CommandText = get-content h:\scripts\sql.txt
$cmd.Connection = $conn
$adapter = new-object System.Data.SqlClient.SqlDataAdapter
$adapter.SelectCommand = $cmd
$ds = new-object System.Data.DataSet
$nRecs = $adapter.Fill($ds)
$conn.close()
|
|
|
|
|
n1ckml007
New Member
Posts:15
|
| 01 Aug 2008 03:09 PM |
|
Sorry I didn't answer all of you questions. What I want to do is:
Read the contacts from the SQL DB, combine the $_.last_name and $_.first_name properties of the System.Data.DataSet object ( $ds.table[ 0 ] ) into a new property with the format lastname, firstname.
I don't want to change the SQL DB, the point of this script is to update the Exchange OUs to match what's in the SQL DB.
The things that I care about in order are:
email addresses (if I need to make a hash-table type object this would be my key)
name (in the exchange format (lastname, firstname) stored as one property)
DLs (distribution lists) (not worrying about this for now)
So what I don't know is:
Is there a way to update the properties of the System.Data.DataSet object ( $ds.table[ 0 ] ), or do I need to create a new object, using the add-member cmdlet?
|
|
|
|
|
marco.shaw
Co-Community Director
Basic Member
Posts:181
|
| 01 Aug 2008 03:28 PM |
|
Posted By n1ckml007 on 08/01/2008 7:09 AM
So what I don't know is:
Is there a way to update the properties of the System.Data.DataSet object ( $ds.table[ 0 ] ), or do I need to create a new object, using the add-member cmdlet?
Sorry, I can't help you anymore right now, and will have to leave this to someone more familiar with SQL related scripting.
Now, as to whether you need to create a new object, I can't tell you for sure. You need to connect back to your database to possibly edit a value. I don't think System.Data.DataSet will help you with this alone, since DataSet is for in memory only, and is basically disconnected from the DB. |
|
|
|
|
n1ckml007
New Member
Posts:15
|
| 01 Aug 2008 03:39 PM |
|
This sounds promising:
http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx
"to add rows to a DataTable, you must first use the NewRow method to return a new DataRow object. The NewRow method returns a row with the schema of the DataTable, as it is defined by the table's DataColumnCollection. The maximum number of rows that a DataTable can store is 16,777,216. For more information, see Adding Data to a DataTable."
Hopefully I can do something with this method:
http://msdn.microsoft.com/en-us/library/system.data.datatable.newrow.aspx
"DataTable.NewRow" |
|
|
|
|
n1ckml007
New Member
Posts:15
|
| 01 Aug 2008 06:41 PM |
|
Some progress:
$full_name = new-object "System.Data.DataColumn" ("full_name", [string])
$ds.Tables[ 0 ].Columns.add($full_name)
$ds.Tables[ 0 ] | gm
outputs:
full_name Property System.String full_name {get;set;}
as a property, now I just need to figure out how to populate that column / rows
This blog entry pointed me in the right direction:
http://www.pluralsight.com/community/blogs/dan/archive/2006/10/29/41389.aspx |
|
|
|
|
n1ckml007
New Member
Posts:15
|
| 12 Aug 2008 03:49 PM |
|
I actually ended up using the SELECT blah AS renamed_blah in my SQL query to solve this issue. |
|
|
|
|