Community

Subscribe
Highlighted
Accepted Solution

How to create an own data source

I want to create an own data source in wfa. It contains IP addresses, interfaces, ipspaces and netmasks for new vfilers. I already created a dictionary object and the data source object. Now I'm searching for a code example in powershell to import an excel file into the cache database. Any hints or code examples are highly appreciated.

Thanks in advance

Re: How to create an own data source

I am going to try to post a new how-to video on this.  It is a pretty easy process.

  1. Create a new dictionary object in a new schema.  The object should contain all of the column names and types from the file you plan to use
  2. Create a new Data source Type of Script and use the following example (below).  Here I have the file copied over to the temp location for the data sources
  3. Create a new Data source using the previously created data source type.  Set the hostname to localhost and the port to 8080 (both of these are required but these values can be substituted) 

A couple of important things to remember

  • Natural keys cannot be duplicated.  So if a column will contain duplicate do not set it as a Natural Key or it will be dropped to only one line item
  • Dictionary Objects must be named identical to the files which feed them and vice versa.  If your Diction Object is called 'tenants_addresses' then the file needs to be called 'tenants_addresses'

Script

--------------------------------------------

$tenants_addresses = "C:\wfa\tenants_addresses.csv"

Get-WFALogger -message ("Grabbing the file - " + $tenants_addresses)

Copy-Item $tenants_addresses .\

--------------------------------------------

Re: How to create an own data source

Unfortunately it doesn't work. Maybe I'm missing something. First I tried it with the script. Then I copied the file manually to ...wfa\jboss\server\default\tmp\wfa and it did not work too. The file gets deleted from this folder when I aquire the data source. Error message stays the same. File is not found.

How to create an own data source

I gave it a new try with a new scheme. Now wfa tries to import the data and I get the error data trucated for column id at row 1. However, I have no column id. Do I have to put one in my input file? Or in more general how does my input file should look like when I have the columns ip,interface,ipspace,netmask,hostname?

Re: How to create an own data source

I just published the new How-To video for this.  I ran out of time to get it posted this past Friday.  Please take a look and let me know if you have any other questions.  This should get you off the ground.

https://communities.netapp.com/videos/3351

Jeremy Goodrum, NetApp

The Pirate

Twitter: @virtpirate

Blog: www.virtpirate.com

Re: How to create an own data source

Hi Jeremy,

great video, thanks. I exported the data from an excel spread sheet as tab separated but it seems not to work. I get the error "data truncated at row 1". Do I have to add the \N at the beginning of each line in the file or is there any possibility to get the input file directly from the excel spread sheet? And can you post the ppt file from the video as well?

My file looks like this:

1    5.242.234.8    vif3-231    its    255.255.255.128    v998spnvv1013e

2    5.242.234.9    vif3-231    its    255.255.255.128    v998spnvv1020e

3    5.242.234.10    vif3-231    its    255.255.255.128    v998spnvv1029e

4    5.242.234.11    vif3-231    its    255.255.255.128    v998spnvv1032e

5    5.242.234.12    vif3-231    its    255.255.255.128    v998spnvv1049e

6    5.242.234.13    vif3-231    its    255.255.255.128    v998spnvv1056e

7    5.242.234.14    vif3-231    its    255.255.255.128    v998spnvv1058e

Best regards

Stefan Kellner

Re: How to create an own data source

Got it. With \N it works.

Re: How to create an own data source

Stefan,

Thanks for checking out the video, I am happy to hear that it helped.  Yeah, the \N seems to be very important.  Also, you do not need to add an ID field in your file as it is automatically created with the import.  Please don't forget to mark this question as answered.

Jeremy Goodrum, NetApp

The Pirate

Twitter: @virtpirate

Blog: www.virtpirate.com

Re: How to create an own data source

The first column seems to be a hidden column called 'id' for the primary key in the WFA database.  The \N tells mysql it's a null field, so mysql creates its own value.  You could put your own key in there instead of \N if you wanted to. 

View the Data Source script for the vc Scheme under "Data Source Types" --> "VMware vCenter" as an example.  This one script updates several dictionary objects at one time.  Note some of the tab delimited CSV files it creates have \N as the first column, while others use a unique identifier like the Host object ($hostFile) starts out with a $hostId that it gets from the (object returned from Get-VMHost)'s Id.GetHashCode() call.  If you look at the Host dictionary object, there is no HostID listed.  The dictionary object starts with the 2nd column in the CSV, the Name parameter.

Add-Content $hostFile ([byte[]][char[]] "$hostId`t$name`t$ip`t$os_version`t$virtualCenterIp`t$cluster`t$datacenterName`n") -Encoding Byte

I looked at the table structure in mysql, and confirmed the first column is the primary key for the table, and it doesn't map back to anything listed in the dictionary object definition.

mysql> use vc;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_vc    |
+-----------------+
| data_store      |
| host            |
| lun             |
| nas_share       |
| virtual_disk    |
| virtual_machine |
+-----------------+
6 rows in set (0.00 sec)

mysql> show columns from host;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | PRI | NULL    | auto_increment |
| name              | varchar(255) | NO   | MUL | NULL    |                |
| ip                | varchar(255) | YES  |     | NULL    |                |
| os_version        | varchar(255) | YES  |     | NULL    |                |
| virtual_center_ip | varchar(255) | NO   |     | NULL    |                |
| cluster_name      | varchar(255) | YES  |     | NULL    |                |
| data_center_name  | varchar(255) | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
7 rows in set (0.03 sec)

mysql>

Hope this helps,

Dave

Re: How to create an own data source

Hi Dave,

          I'm getting a 'Error running data acquisition script: prefix string too short' error when i added the csv file as a datasource. i followed all the steps provided in the Video, but at last it failed. Can you please help?

Thanks

Mahesh.P