Script to send a lease in a database

This topic contains 10 replies, has 2 voices, and was last updated by Profile photo of XONE3D . XONE3D . 2 years, 4 months ago.

  • Author
    Posts
  • #17498
    Profile photo of XONE3D .
    XONE3D .
    Participant

    Hello,

    i ask you for helping to make a script. I would like to send in a mysql database all adresse ip et adresse MAC from a lease

    For moment i have make this for script

     
    [[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
    
    $connectionString = "server=localhost;uid=admin;pwd=test;database=zabbix;port=3306;"
    $connection = New-Object MySql.Data.MySqlClient.MySqlConnection
    $connexion.ConnectionString = $connectionString
    Export-DhcpServer -Computername winserver -File C:\Users\Administrateur\dhcpexport.xml -ScopeId 192.168.0.0 -Leases
    
    $sql = "LOAD DATA LOCAL INFILE "C:\Users\Administrateurs\mysql.csv"
                     INTO TABLE zabbix
                     FIELDS ENCLOSED BY ','
                     LINES TERMINATED BY '\r\n'"
    
    $command = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $connection)
    
    $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
    
    $dataSet = New-Object System.Data.DataSet
    
    $connection.Close()
    

    can someone say me how to convert the xml file in a file text and help me for the query ?

    Thanks you

  • #17503
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Please attach a sample XML file that you'd like to convert to CSV. You'll probably need to rename it as .txt for the forums to allow the attachment. Also, we'd need to know which fields you want to be in the CSV file and database. (You mentioned IP address and MAC address; is that everything you need?)

  • #17505
    Profile photo of XONE3D .
    XONE3D .
    Participant

    I have attached the file in .txt.
    From the Lease from this file, i would just retrive the fadresse ip and the mac adress fileds. that's all
    For the example :

      192.168.0.0
      41-7a-65-72-74-79-75-69-6f-70

    can you say me if is possible to convert the xml outpout from the cmd Export-DHCP server in a .cvs file in the script automatically ?

    the goal is to have a script that does an export of the converted file and just send the relevant fields in a database.

    Thank you for the help

  • #17507
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    OK, while you can go this route and export to an XML file first, then extract some of its data as CSV, you don't actually have to do that. The general approach for converting something to a CSV file in PowerShell is to build some objects in memory which contain the properties you need (and where the property names become the header row of the CSV file), then pipe those objects to Export-Csv.

    It so happens that there's another set of commands in the DhcpServer module which give you the lease information as objects already, eliminating the need for the extra step of saving an XML file, then importing it and manipulating data. Try this:

    Get-DhcpServerv4Lease -ScopeId 192.168.0.0 -ComputerName winserver |
    Select-Object -Property ClientId, IPAddress |
    Export-Csv -NoTypeInformation -Path C:\Users\Administrateurs\mysql.csv
    

    The Get-DhcpServerv4Lease command gives you all of the same information about the IPv4 leases that you got from the XML file (and none of the extra stuff that you don't need for this particular operation). The Select-Object command gets rid of all properties on the objects except for ClientId (MAC address) and IPAddress, and the resulting objects are passed on to Export-Csv to produce the file you need. If you want, you can even rename the ClientId property to MacAddress by changing the arguments to Select-Object slightly. This is what's generally referred to as a "constructed property" in the PowerShell community:

    Get-DhcpServerv4Lease -ScopeId 192.168.0.0 -ComputerName winserver |
    Select-Object -Property @{ Name = 'MacAddress'; Expression = { $_.ClientId } }, IPAddress |
    Export-Csv -NoTypeInformation -Path C:\Users\Administrateurs\mysql.csv
    
  • #17508
    Profile photo of XONE3D .
    XONE3D .
    Participant

    Thank you for the explain !

    It's really nice frome you, i begin the powershell that two day and ther is not easy ti understand.

    Juste a last question i have impleted your code

    {[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
    
    $connectionString = "server=localhost;uid=admin;pwd=test;database=zabbix;port=3306;"
    $connection = New-Object MySql.Data.MySqlClient.MySqlConnection
    $connexion.ConnectionString = $connectionString
    
    Get-DhcpServerv4Lease -ScopeId 192.168.0.0 -ComputerName winserver |
    Select-Object -Property ClientId, IPAddress |
    Export-Csv -NoTypeInformation -Path C:\Users\Administrateur\mysql.csv
    
    $sql = "LOAD DATA LOCAL INFILE "C:\Users\Administrateur\mysql.csv"
                     INTO TABLE dhcp
                     FIELDS ENCLOSED BY ""
                     LINES TERMINATED BY '\n'"
    
    $command = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $connection)
    
    $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
    
    $dataSet = New-Object System.Data.DataSet
    
    $connection.Close()}

    I have a error when i launch my script, i mind that comming from the sql query.

    I have the same query for a linux server and it work goods but i supect that windows make the carachter encaplulation not frome the same way.

    It say there is a inantendu token with a parse error

    Thank you !

  • #17510
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    There are a couple of quoting problems with your string. In this case, it's probably easiest to use a PowerShell here-string (see the [url="http://technet.microsoft.com/en-us/library/hh847740.aspx"]about_Quoting_Rules[/url] help file for details on these, as well as every other type of string quoting issue you might come across.)

    Also, the contents of your LOAD DATA INFILE command might be a bit off. I don't have a MySQL instance to test this, but according to [url="http://dev.mysql.com/doc/refman/5.1/en/load-data.html"]their documentation[/url], this should be what works for loading a CSV file from Windows:

    $sql = @'
        LOAD DATA LOCAL INFILE 'C:\Users\Administrateur\mysql.csv'
        INTO TABLE dhcp
        FIELDS OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\r\n'
        IGNORE 1 LINES;
    '@
    

    It may be difficult to tell by looking at it, but that [b]ENCLOSED BY '"'[/b] bit is a lone double quotation mark surrounded by a pair of single quotation marks.

    It looks like something is off about the rest of the script as well. I'm not sure what you're doing with the DataAdapter or DataSet objects. I would expect to see you calling the [url="http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqlcommand.html#connector-net-examples-mysqlcommand-executenonquery"]ExecuteNonQuery()[/url] method on the $command object.

  • #17529
    Profile photo of XONE3D .
    XONE3D .
    Participant

    Thank you for the reply ,

    can you say me how to show the log in windows server please ? while the script working but ther is no record in database and i have any error message in the console ...

    I gonna be cray with this !!

    {[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
    $connectionString = "server=localhost;uid=admin;pwd=test;database=zabbix;port=3306;"
    $connection = New-Object MySql.Data.MySqlClient.MySqlConnection
    $connection.ConnectionString = $connectionString
    
    Get-DhcpServerv4Lease -ScopeId 192.168.0.0 -ComputerName winserver |
    Select-Object -Property ClientId, IPAddress |
    Export-Csv -NoTypeInformation -Path C:\Users\Administrateur\mysql.csv
    
    $sql = @'
        LOAD DATA LOCAL INFILE 'C:\Users\Administrateur\mysql.csv'
        INTO TABLE dhcp
        FIELDS OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\r\n'
        IGNORE 1 LINES;
    '@
    
    $command = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $connection)
    
    $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
    
    $dataSet = New-Object System.Data.DataSet
    
    $recordCount = $dataAdapter.Fill($dataSet, "sample_data")
    
    $dataSet.Tables["sample_data"] | Format-Table > C:\inetpub\wwwroot\samples.html
    
    $connection.Close()}

    for this line

    {$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
    
    $dataSet = New-Object System.Data.DataSet
    
    $recordCount = $dataAdapter.Fill($dataSet, "sample_data")
    
    $dataSet.Tables["sample_data"] | Format-Table > C:\inetpub\wwwroot\samples.html}

    i have be inspired from this site [url]https://suite.io/mark-alexander-bain/1rza2aa[/url]

  • #17531
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    I see that you've copied and pasted a lot of code from that blog post, but the problem is that the blog was demonstrating a SELECT command, which retrieves data from the DB. You've changed the SQL query to be a LOAD DATA INFILE command instead, which is something completely different. The rest of the code is no longer appropriate to that type of query.

    This can be a pretty complicated topic. I'd start by learning about the SQL language itself (and you may as well do this from MySQL's documentation, since that's the engine you're using.) From there, start to learn about the .NET API that they've provided (which looks very similar to Microsoft SQL Server classes, by the way, so you could potentially read up on those as well for more information.)

    I can't test this code, since I don't have an instance of MySQL (or a DHCP server, for that matter), but I think this is what you're trying to do:

    [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
    $connectionString = "server=localhost;uid=admin;pwd=test;database=zabbix;port=3306;"
    $connection = New-Object MySql.Data.MySqlClient.MySqlConnection
    $connection.ConnectionString = $connectionString
     
    Get-DhcpServerv4Lease -ScopeId 192.168.0.0 -ComputerName winserver |
    Select-Object -Property ClientId, IPAddress |
    Export-Csv -NoTypeInformation -Path C:\Users\Administrateur\mysql.csv
     
    $sql = @'
        LOAD DATA LOCAL INFILE 'C:\Users\Administrateur\mysql.csv'
        INTO TABLE dhcp
        FIELDS OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\r\n'
        IGNORE 1 LINES;
    '@
     
    $command = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $connection)
    
    # If we've done anything wrong up to this point, ExecuteNonQuery() will produce an error.
    
    $rowsAffected = $command.ExecuteNonQuery()
    
    # Now that we're done sending data to the DB, it's time to pull it back out again using the
    # sample code you found which started with a SELECT statement.  In this case, the table name
    # is dhcp (taken from your "LOAD DATA INFILE 'filename' INTO TABLE dhcp" query.)
    
    $sql = 'SELECT * FROM dhcp'
    
    $command = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $connection)
    $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
    $dataSet = New-Object System.Data.DataSet
    
    # Here, "sample_data" is whatever you want to call it, so long as it's the same value in the
    # call to Fill(), and in the next line which access that name in $dataSet.Tables.
    
    $recordCount = $dataAdapter.Fill($dataSet, "sample_data")
    $dataSet.Tables["sample_data"] | Format-Table > C:\inetpub\wwwroot\samples.html
     
    $connection.Close()
    
  • #17590
    Profile photo of XONE3D .
    XONE3D .
    Participant

    The script work , there two probleme, the first was $connection.Open() was not whrite et the second was the caractere for the sql request, in linux the path is with one / but with windows is \\.

    Can you juste say me how make a space betwen the field ? i explain me, i have remove the double quotes and now i would remove the[b],[/b] and make a space for the delimieur to MySQL . It is possible ?

    Thank you for helping et for all your explain !!

    {Foreach {$_ -replace '"',''}}
    {[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
    $connectionString = "server=localhost;uid=admin;pwd=test;database=zabbix;port=3306;"
    $connection = New-Object MySql.Data.MySqlClient.MySqlConnection
    $connection.ConnectionString = $connectionString
    $connection.Open()
     
    Get-DhcpServerv4Lease -ScopeId 192.168.0.0 -ComputerName winserver |
    Select-Object -Property ClientId, IPAddress, ClientType, HostName |
    Export-Csv -NoTypeInformation -Path C:\Users\Administrateur\mysql.csv
    $extractfile= "C:\Users\Administrateur\mysql.csv"
    $delguill = Get-Content C:\Users\Administrateur\mysql.csv | Foreach {$_ -replace '"',''}
    $delguill > C:\Users\Administrateur\mysql.csv
    
    $sql = @'
        LOAD DATA LOCAL INFILE 'C:\\Users\\Administrateur\\mysql.csv'
        INTO TABLE dhcp
        FIELDS ENCLOSED BY ','
        LINES TERMINATED BY '\n'
        IGNORE 1 LINES;
    '@
      
    $command = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $connection)
     
    # If we've done anything wrong up to this point, ExecuteNonQuery() will produce an error.
     
    $rowsAffected = $command.ExecuteNonQuery();
     
    # Now that we're done sending data to the DB, it's time to pull it back out again using the
    # sample code you found which started with a SELECT statement.  In this case, the table name
    # is dhcp (taken from your "LOAD DATA INFILE 'filename' INTO TABLE dhcp" query.)
     
    $sql = 'SELECT * FROM dhcp'
     
    $command = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $connection)
    $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
    $dataSet = New-Object System.Data.DataSet
     
    # Here, "sample_data" is whatever you want to call it, so long as it's the same value in the
    # call to Fill(), and in the next line which access that name in $dataSet.Tables.
     
    $recordCount = $dataAdapter.Fill($dataSet, "sample_data")
    $dataSet.Tables["sample_data"] | Format-Table > C:\inetpub\wwwroot\samples.html
     
    $connection.Close()}
  • #17624
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    I don't think you want to remove the quotes and change commas to spaces like that. However, looking over my earlier post and comparing it to the MySQL documentation, I did just notice that part of the SQL query to read a CSV-style file was missing. Try this:

    $sql = @'
        LOAD DATA LOCAL INFILE 'C:\Users\Administrateur\mysql.csv'
        INTO TABLE dhcp
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\r\n'
        IGNORE 1 LINES;
    '@
    

    According to the documentation, the default field terminator is a tab, not a comma. Adding that [b]FIELDS TERMINATED BY ','[/b] bit should address this.

  • #17666
    Profile photo of XONE3D .
    XONE3D .
    Participant

    the script work !!

    I have juste a trouble with the mySQL connection , i have this message :

    {Exception lors de l'appel de «Open» avec «0» argument(s): «Host 'winserver' is not allowed to connect to this MySQL
    server»}

    . I have change the bind-address in mysql et change the host from my server file, i ask a MySQL forum for that.

    I thank you for your helping !!

You must be logged in to reply to this topic.