/\/\o\/\/ PowerShelled

This blog has moved to http://ThePowerShellGuy.com Greetings /\/\o\/\/
$AtomFeed = ("Atom.xml")
$PreviousItems = (" MSH Get-MSDN Function "," Getting and Working With SQL Server data in Monad "," MSH Cluster Watcher script "," Monad NewYears Clock "," Using ADO to get data from Excel to Dataset in Monad. "," Monad Really Does Rock "," Passing (not so) mandatory Parameters to WMI metho... "," Saving a WMI instance to a XML-file from MSH "," MSH Orphan share remover Tool "," MSH Welcome screen "," ")

Friday, December 30, 2005

 


More DataTables and joining them in Monad



After the Posts about getting data from Excel and SQL, and working with the DataTables and DataSets from MSH.

Now something about Relations between DataTables, it's also possible to "Join" the dataTables you have in Monad.

Let say you have the following Situation,

You have 2 DataTables :

One with Computers and one with users.
To Create this situation, you can past the following Code into you MSH console (or make it a script first by saving to a file) :

$dt = new system.data.dataTable
$dt.TableName = "tblComputer"

$dt.Columns.add("cmp_Name",[system.type]"string")
$dt.Columns.add("cmp_User",[system.type]"string")

$row = $dt.NewRow();$row.cmp_Name = "Computer1";$row.cmp_User = "Mow";$dt.rows.add($row)
$row = $dt.NewRow();$row.cmp_Name = "Computer2";$row.cmp_User = "Monad";$dt.rows.add($row)
$row = $dt.NewRow();$row.cmp_Name = "Computer3";$row.cmp_User = "Monad";$dt.rows.add($row)
$row = $dt.NewRow();$row.cmp_Name = "Computer4";$row.cmp_User = "Mow";$dt.rows.add($row)
$row = $dt.NewRow();$row.cmp_Name = "Computer5";$row.cmp_User = "Foo";$dt.rows.add($row)
$row = $dt.NewRow();$row.cmp_Name = "Computer6";$row.cmp_User = "Bar";$dt.rows.add($row)

$dtComputer = $dt

$dt = new system.data.dataTable
$dt.TableName = "tblUser"

$dt.Columns.add("usr_Name",[system.type]"string")
$dt.Columns.add("usr_Dep",[system.type]"string")

$row = $dt.NewRow();$row.usr_Name = "Mow";$row.usr_Dep = "Dep1";$dt.rows.add($row)
$row = $dt.NewRow();$row.usr_Name = "Monad";$row.usr_Dep = "Dep1";$dt.rows.add($row)
$row = $dt.NewRow();$row.usr_Name = "Foo";$row.usr_Dep = "Dep2";$dt.rows.add($row)
$row = $dt.NewRow();$row.usr_Name = "Bar";$row.usr_Dep = "Dep2";$dt.rows.add($row)

$dtUser = $dt


This is just to create the 2 datatables for the Example, you can also get them from another source as SQL server , MsAcsess, Excel etc. (as long as you can do ODBC) as explained in former posts, since you have them all in DataTables after the import from then on, they are all the same now so no matter where you got the data from, as long as you have a field to "Join on" you can create relations between them, is that Cool or Not ?, but I talk a bit ahead now, we still got to do that ;-)

Hence,let's get back to the Example :

If You did run the former code, the result should be that you have 2 Datasets that you can look at like this :
MSH>$dtcomputer

cmp_Name cmp_User
-------- --------
Computer1 Mow
Computer2 Monad
Computer3 Monad
Computer4 Mow
Computer5 Foo
Computer6 Bar


MSH>$dtUser

usr_Name usr_Dep
-------- -------
Mow Dep1
Monad Dep1
Foo Dep2
Bar Dep2

Normaly you should have some more fields but for the example this will do.

Now you can answer questions from Monad like,
What are Mow's Computers, or how Many Users Dep2 got :
MSH>$dtcomputer.select("cmp_User = 'Mow'")

cmp_Name cmp_User
-------- --------
Computer1 Mow
Computer4 Mow


MSH>$dtUser.select("usr_Dep = 'Dep2'").count
2


But now You boss does ask How Many Computers does Dep1 use ?
Oops, there is no Department foeld in the Computer Table.

In this case you can figure this out, by counting by head but imagine that there are 1000 computers and 800 Users how to go on ?.

You need to combine the info of the 2 tables.
Here the Relations come to the resque.

In this Case we see That the UserName is know in both Tables,
In tblUser as usr_Name and in tblComputer as cmp_User.

so if we "join" those to field we can get from a User to his Computers.

To make a relation between to DateTables they have to be in the same dataset so we add them to a dataset and make the relation :

# Create A DataSet and add the 2 Tables

$ds = new-object system.data.dataset
$ds.merge($dtUser)
$ds.merge($dtComputer)

# Create a Relation Based on the Username

$Parent = $ds.tables.item("tblUser").columns.item("usr_Name")
$Child = $ds.tables.item("tblComputer").columns.item("cmp_User")
$rel = new-object System.Data.DataRelation("UserToComputer",$Parent,$child,$false)

# Add the realation to the DataSet
 
$ds.Relations.add($rel)


*Note* the $false means "No Constrains", as we use this only for a query.

Now we have a Relation added to the DataSet, we can use it to Combine the data from
both Tables.

Hence, we are now able to answer the Question our boss asked.

MSH>$ds.tables.item("tbluser").select("usr_dep = 'Dep1'") | foreach {$_.GetChildRows("UserToComputer")}

cmp_Name cmp_User
-------- --------
Computer1 Mow
Computer4 Mow
Computer2 Monad
Computer3 Monad


MSH>($ds.tables.item("tbluser").select("usr_dep = 'Dep1'") | foreach {$_.GetChildRows("UserToComputer")}).count
4


Very Cool, you boss does say .... Get this software deployed to them ;-)

and start thinking getWmiObject -computer $_.cmp_name win32_product ...
......invokeMethod ... Install ...
Get-WmiMethodHelp..
....

OK you say, you want it as a oneliner , or can I create the script ?

Have fun

greetings /\/\o\/\/

Tags :


Comments: Post a Comment



<< Home

Archives

October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   May 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006   December 2006  

$Links = ("PowerShell RC1 Docs"," PowerShell RC1 X86"," PowerShell RC1 X64"," Monad GettingStarted guide"," Monad Progamming Guide"," Monad SDK"," Monad videos on Channel 9"," MSH Community Workspace"," scripts.readify.net "," MonadSource"," www.reskit.net"," PowerShell Blog"," Under The Stairs"," computerperformance powershell Home"," proudlyserving"," MSH on wikipedia"," MSHWiki Channel 9"," Keith Hill's Blog"," Precision Computing"," PowerShell for fun"," MSH Memo (Japanese)"," monadblog")

find-blog -about "PowerShell","Monad" | out-Technorati.
find-blog -contains "","" | out-Technorati.
Google
 
Web mow001.blogspot.com

This page is powered by Blogger. Isn't yours?