I’ve got a problem, I’m reading out 3 tables from a SQL Server Database and sending them to a simple text file. I need a way to read in the text file and have structured data so that I can “make it so”.
Three tables are:
Devices
Groups
Device to Group Mappings
Each Device has a device ID, and each group has a group ID, the Device to group mapping does exactly that. There is no way to export these discovery/inventory ranges, they have to be recreated from scratch. When talking about this at scale manually recreating a 1000 of these isn’t going to happen(not by me)
I’ve tried numerous things and I’d like to stick to powershell on this one. I’m sure pandas could make quick work of this however I want something that can be used everywhere <3 powershell.
$device = Import-csv -delimiter ":" -path …
$device = get-content …
I can reference $device[n] and get the line however this is not good because I don't need the line as the index. I need these to be more PowerShell like.
I'd like to be able to do things like
$device.ID
This would allow me to correlate the three tables in such a way that I can use the built in features of the product to script back the discovery/inventory ranges recreating all my groups nicely
Example of device ID1 from text file created. If there is a way to read in the text file and have each device be its own powershell object where you can reference each object by index say device ID or Address the rest should be easy.
Id : 1
Address : 10.0.147.2
AddressType : 1
SNMPGetCommunity : public
SNMPSetCommunity :
CIMUserName :
CIMPassword :
IPMIUserName :
IPMIPassword :
IPMIKGKey :
IPMIRetry : 2
IPMITimeout : 5
EnabledState : 1
SNMPRetry : 2
SNMPTimeout : 4
SubnetType :
SubnetMask : 255.255.255.0
Protocols : 64
DeviceTypeMask : 1
ICMPRetry : 1
ICMPTimeout : 1000
EMCUserName :
EMCPassword :
EMCPort :
WSMANUserName : root
WSMANPassword : {1, 0, 0, 0…}
WSMANCertPath :
WSMANPort : 443
WSMANTimeout : 60
WSMANRetries : 3
WSMANBitSettings : 7
BladeDiscovery : 0
NameDisplay :
SSHUserName :
SSHPassword :
SSHPort : 22
SSHTimeout : 3
SSHRetries : 3
RowVersion : {0, 0, 0, 0…}
Any pointers in the right direction would be great
You could use Export-CliXml and Import-CliXml. Export-CliXml creates an XML-based representation of an object and stores it in a file. Import-CliXml brings back the object from the saved file.
Thanks for the reply Daniel however I was not successful and ended up with the same thing I have been since the beginning. Text that looks nice on the console but is useless for processing.
Example lets take sample “Device to Group Mappings” table. Notice that unlike the other two tables that are like this
[obj1.header1]
[obj1.header2]
[obj1.header3]
[obj2.header1]
[obj2.header2]
…
This one is has more traditional headers already.
[header1][header2][header3]
Id IdDiscoveryConfiguration IdDiscoveryConfigurationGroup
102 58 1
1 1 2
2 2 2
3 3 3
4 4 3
5 5 4
6 6 4
16 16 7
86 43 7
87 44 7
88 45 7
89 46 7
90 47 7
91 48 7
92 49 7
93 50 7
94 51 7
95 52 7
96 53 9
103 59 9
maybe I’m missing its not so easy in powershell however python this is super straight forward.
import pandas as pd
data_frame = pd.read_table(‘path’, sep=‘\s+’)
df.shape
(20, 3)
df.Id or df.IdDiscoveryConfiguration
which will list out everything nicely and putting in simple comparison logic to rebuild stuff is simple once you can reference the data.
Powershell leaves me with a (20,1) shape which is not going to work.
I have the MOL books and I’m not really sure if I missed anything in there that would help me. Again I want this all in powershell so I can give it away for others instead of them having to send me stuff everytime to get this bit of processing done.
@Olaf Soyk
Thank you very much for the link Join-Object
@Max Kozlov
I went ahead and used Notepad++ for the table that already had headers the way you would expect and turned it into a nice csv and it worked great. The others I was able to come up with a solution.
Forgive me for not posting the code yet I will edit this post before the end of the night and show you what I came up with I think you will like it.
One of my idea’s was to read in the text file to a hash table. You can see what I mean assuming you have more than 1 network adapter. Things that would need to be fixed. The keys have extra whitespace that would need to be removed. Also would need to check to see if key is already created if hash table ContainsKey() then could just add or append to its value?
you not use $line in your code but use $test[$i], this way is not ‘foreach’ way
you did not explain, you have one file for one device or may devices in one file.
if many in one than you can ‘append’ data into hash but it is not practically usable.
but better if you start with saving your data to array of hashes/objects/ or hash of hashes/objects/
something like this (if your data guaraneed to not have the same fields for one object)
[not tested]
$data = {}.Invoke() # use collection instead of PS array for fast adding
$current = @{} # init current object hash
foreach ($line in (Get-Content $datafile) { # foreach have current line in $line variable
$name, $value = $line -split ' : ' # use PS split for regex usage instead of char array for string .split()
if ($current.ContainsKey($name)) { # current info already have this key
# object filled, save it to array
$data.Add($current)
$current=@{} # reinit current object hash
}
#fill new field of current object
$current[$name]=$value
}
# save last object
if ($current.Count) {
$data.Add($current)
}
this way you have collection of hashes (or you can have connection of objects if you save it as $data.Add([PSCustomObject]$current) )
you also can have hash of objects if you have unique field (id, for example)
