/\/\o\/\/ PowerShelled
This blog has moved to http://ThePowerShellGuy.com Greetings /\/\o\/\/
$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
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
}
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
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
}
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
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
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)
$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 :
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
# /\/\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
$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 : Monad msh
