/\/\o\/\/ PowerShelled

This blog has moved to http://ThePowerShellGuy.com Greetings /\/\o\/\/
$AtomFeed = ("Atom.xml")
$PreviousItems = (" and another source for MSH scripts is born "," VBscript hosting in MSH, Inputbox in Monad part 2 "," Some Monad Com hacks "," It's busy at the Monad front "," Monad Remoting "," How MSH Works "," Hosting an MSH runspace from Monad "," MSH get-WmiMethodHelp function Update 3 "," out-dataGrid function for Monad "," Windows "Monad" Shell (MSH) Beta 3.1 "," ")

Tuesday, March 28, 2006

 


working with CSV files in MSH (part one)



This post is about working with CSV files in MSH,

I wil again use OleDb connection and a dataset to work with as I did with :

Access :
scripting games Last Day MSH answers for event 7 and 8

Excel :
Using ADO to get data from Excel to Dataset in Monad.

SQL (SQL Provider) :
Getting and Working With SQL Server data in Monad

Also I'm going to use relations as I did in :

More DataTables and joining them in Monad

As from the import into the datatable all data is the same so also all other sources could be mixed. I will go on the joining example above (it will only show the row in the second table to combine the sources to a new CSV.

(See the Access example from the scripting games series also for a way to sum and group using the compute method of the dataset and more info about the select statement with expression see also Report MP3 count and size by User from MSH ) and in Some fun With Monads Add-Member, MP3 files and COM you can see another powerfull way to extend MSH objects Get-Member.

so you find the examples a bit chryptic you can find more info about the methods used in the scripts in those posts, also handy if your gonna play with datasets from monad could be this post : Strange behavour of get-member on DataTables , (it's not strange to me anymore as I'm used to it but is a bit confusing as you also can see in the NG as the topic keeps coming up.

a nice quote from Keith Hill about it just posted there :

What I am getting at is *perhaps* this is a case of what's logical
in the abstract verus useful on a daily basis.


--
Keith


but enough links, on with the CSV scripts ;-)

CSV :

for the example I take 3 CSV files to combine,

1) Computers in AD (selected on logonTime)
2) A list of Computers in SMS
3) A Inventory (CDMB) List in CSV format.

I will supply the way to get the first 2 files, but for if you not have SMS or AD I will supply samples of the 3 files below, and as mentioned you also can take other sources as input. :

Save the files with content in the folder c:\CsvExample :

ADComputers.csv

name,logon,description
PC42,"10/17/2005 11:02:32 AM",Foo
PC81,,monad
PC64,"3/13/2006 7:49:31 AM",Bar
PC80,"3/20/2006 7:49:31 AM",bar
PC76,"3/20/2006 8:14:59 AM",Mow
PC79,"3/20/2006 7:49:31 AM",niets

SmsResources.csv

name,ResourceID
PC42,62301
PC73,62370
PC64,62360
PC76,62380
PC80,62381
PC64,62390


Inventaris.csv

PC73,void
PC64,bar
PC76,mow
PC80,bar
PC81,monad
PC79,niets


Now we first start by declaring a helper function

# Connect CSV helper function

function connect-csv {
  Param ($Path = ".")
  $ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$path;Extended properties='text;HDR=Yes;FMT=Delimited'"
  $Conn = new-object System.Data.OleDb.OleDbConnection($connString)
  $conn.open()
  $Tables = $Conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null)
  $Tables | select TABLE_NAME


After you have loaded this helper function (just past the code in your MSH window)

you can connect to a Directory (database of CSV's) like this :

MSH>. connect-csv c:\CsvExample

TABLE_NAME
----------
ADComputers#csv
Inventaris#csv
SmsResources#csv


Note, that I start the function with a dot, so in the current scope so I keep the connection, an other way to do this is to return the connection.

as a helper I show the files we created :

but this only makes the connection

now we want to get the CSV files, I made another helper function for that :

# helper function to get a CSV file into a dataset :

function get-DataTable {
  Param ($name )
  $cmd = new-object System.Data.OleDb.OleDbCommand("Select * from [$name]",$Conn)
  $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
  $dt = new-object System.Data.dataTable
  [void]$da.fill($dt)
  $dt.TableName = $name.split("#")[0]
  return $dt


after we loaded that function (rember if you put it to a file you need to dot source it also when you run it to keep it available)

after you have loaded the helperfunction we can allready use it like this :

MSH>get-datatable ADComputers#csv

name                       logon                      description
----                       -----                      -----------
PC42                       10/17/2005 11:02:32 AM     Foo
PC81                                                  monad
PC64                       3/13/2006 7:49:31 AM       Bar
PC80                       3/20/2006 7:49:31 AM       bar
PC76                       3/20/2006 8:14:59 AM       Mow
PC79                       3/20/2006 7:49:31 AM       niets


But we also can do this :

MSH>import-csv ADComputers.csv | where {$_.logon -gt (get-date).AddDays(-30)}

name                       logon                      description
----                       -----                      -----------
PC64                       3/13/2006 7:49:31 AM       Bar
PC80                       3/20/2006 7:49:31 AM       bar
PC76                       3/20/2006 8:14:59 AM       Mow
PC79                       3/20/2006 7:49:31 AM       niets


MSH>import-csv ADComputers.csv | where {$_.logon -gt (get-date).AddDays(-30)} |export-csv -no c:\CsvExample\AdActive.csv


Note that on the Second line the output get's redirected to a New CSV file.
that only contains the computers that where actualy seen this Month.

this file we will use in the rest of your example.

now lets readin the rest of the CSV files :

# Get CSV files into DataTables

$AdActive = get-DataTable AdActive#csv
$Inventaris = get-DataTable Inventaris#csv
$SmsResources = get-DataTable SmsResources#csv

# Join all Tables in a DataSet

$ds = new-object System.Data.dataSet

$ds.merge($AdActive)
$ds.merge($Inventaris)
$ds.merge($SmsResources) 

# Add Relation from AD to SMS resource

$parent = $ds.tables.item("ADActive").columns.item('name')
$child = $ds.tables.item("SmsResources").columns.item('name')
$rel = new-object System.Data.DataRelation("Ad_SMS",$Parent,$child,$false)
$ds.Relations.add($rel) 


Now we are able to compare those 2 files :

MSH>$ds.tables.item("AdActive") | select name,{$_.getchildrows("Ad_sms").count}


name                                            $_.getchildrows("Ad_sms").count
----                                            -------------------------------
PC64                                                                          2
PC80                                                                          1
PC76                                                                          1
PC79                                                                          0

# more nice formatted and filtered not in SMS 
MSH>$ds.tables.item("AdActive") | select name,logon,@{e={$_.getchildrows("Ad_sms").count};n='count'} | where {$_.count -eq 0}

name                       logon                                          count
----                       -----                                          -----
PC79                       3/20/2006 7:49:31 AM                               0

# Count active and not in SMS

MSH>$ds.tables.item("AdActive") | select name,logon,@{e={$_.getchildrows("Ad_sms").count};n='count'} | where {$_.count -eq 1} | measure-Object


Count    : 2
Average  :
Sum      :
Max      :
Min      :
Property :


I will do the further combining (in a next post,
as it it getting a bit to much for one post.)

but you can already see how handy it it to compare

but I will add the script that created the AD listing (not it uses the replicated logontime, so it's 2 weeks acurate, if you need to check all domaincontrollers to be sure you can use another example on my blog (search for AD on blog).

# get all user or Computer logontimes in an OU from a AD
# /\/\o\/\/ 2006
# http://mow001.blogspot.com

$Root = New-Object DirectoryServices.DirectoryEntry 'LDAP://server/ou=computers,dc=domain, dc=COM'
$Searcher = New-Object DirectoryServices.DirectorySearcher
$Searcher.SearchRoot = $root
$searcher.PageSize = 900

$searchItem = "CN"
$searchValue = "*"
$searchClass = "User"
$SearchCat = "*"
$searcher.Filter = "(&($($searchItem)=$($searchValue))(objectClass=$($searchClass))(objectCategory=$($SearchCat)))"

$PropList = "CN","ObjectClass","ObjectCategory","distinguishedName","lastLogonTimestamp","description"
$PropList | foreach {[void]$searcher.PropertiesToLoad.Add($_)}


$logon = $searcher.findAll() | select @{e={$_.properties.cn};n='name'},
  @{e={[datetime]::FromFileTimeUtc([int64]$_.properties.lastlogontimestamp[0])};n='logon'},
  @{e={$_.properties.description};n='description'}

# export to CSV files :

$logon | export-csv -no c:\CsvExample\ADComputers.csv

# Only seen last Month :

$logon | where {$_.logon -gt (get-date).AddDays(-30)} | export-csv -no c:\CsvExample\AdActive.csv 


and the script that created the SMS listing (with some more properties ;-):

# get SMS Resources

$oq = new system.management.objectquery
$oq.QueryString = "select Name,NetbiosName,Obsolete,ResourceId,Client,ClientType from  SMS_R_System"
$mos = new-object system.management.ManagementObjectSearcher($oq)
$mos.scope.path = "\\sms001\root\sms\Site_mow"
$mos.get() | select [a-z]* | export-csv -no c:\CsvExample\SmsResources.csv


with the obsolete property in SMS we can see why one is double in SMS.

more on combining and more advanced select queries in next post.

enjoy your querying in MSH,

Greetings /\/\o\/\/

Tags :


Comments:
Anonymous Oisin Grehan
Great stuff Mow! These kind of building blocks examples really help the raw msh skills that make you a more effective scripter.

keep it up!

- Oisin G.
 
Post a Comment

Links to this post:

Create a Link



<< 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?