/\/\o\/\/ PowerShelled

---

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) :



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 :



*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 : Monad msh

---