potatoqualitee / modules

Modules like Invoke-CsvSqlcmd

CsvSqlcmd

Invoke-CsvSqlcmd will enable you to natively query a CSV file using SQL syntax using Microsoft's Text Driver. The syntax is as simple as:

Invoke-CsvSqlcmd -csv file.csv -sql "select * from table"

To make command line queries easier, this script will convert the word "table" within the -sql parameter to the actual CSV formatted table name. If the FirstRowColumnNames switch is not used, the query engine automatically names the columns or "fields", F1, F2, F3, etc.

If you are running Invoke-CsvSqlcmd.ps1 on a 64-bit system, and the 64-bit Text Driver is not installed, the script will automatically switch to a 32-bit shell and execute the query. It will then communicate the data results to the 64-bit shell using Export-Clixml/Import-Clixml.

While the shell switch process is rather quick, you can avoid this step by running the script within a 32-bit PowerShell shell ("$env:windir\syswow64\windowspowershell\v1.0\powershell.exe")

Other examples

Invoke-CsvSqlcmd -csv C:\temp\housingmarket.csv -sql "select address from table where price < 250000" -FirstRowColumnNames

This example return all rows with a price less than 250000 to the screen. The first row of the CSV file, C:\temp\housingmarket.csv, contains column names.

Invoke-CsvSqlcmd -csv C:\temp\unstructured.csv -sql "select F1, F2, F3 from table where F3 > 7" 

This example will return the first three columns of all rows within the CSV file C:\temp\unstructured.csv to the screen. Since the -FirstRowColumnNames switch was not used, the query engine automatically names the columns or "fields", F1, F2, F3 and so on.

$datatable = Invoke-CsvSqlcmd -csv C:\temp\unstructured.csv -sql "select F1, F2, F3 from table"  
$datatable.rows.count

Invoke-CsvSqlcmd.ps1 returns rows of a datatable, and in this case, we create a datatable by assigning the output of the script to a variable, instead of to the screen.

SqlMaxMemory

Inspired by Jonathan Kehayias's post about SQL Server Max memory (http://bit.ly/sqlmemcalc), this function displays SQL Server's: total memory, currently configured SQL max memory, and the calculated recommendation.

Jonathan notes that the forumla used provides a general recommendation that doesn't account for everything that may be going on in your specific enviornment.

Get-SqlMaxMemory displays information relating to SQL Server Max Memory configuration settings. Works on SQL Server 2000-2014. Set-SqlMaxMemory sets SQL Server max memory then displays information relating to SQL Server Max Memory configuration settings. Works on SQL Server 2000-2014.

Examples

Get-SqlMaxMemory -SqlCms sqlcluster
Get-SqlMaxMemory -SqlCms sqlcluster | Where-Object { $_.SqlMaxMB -gt $_.TotalMB } | Set-SqlMaxMemory -UseRecommended
Set-SqlMaxMemory -SqlCms sqlcluster -SqlCmsGroups Express -MaxMB 512 -Verbose
Get-SqlMaxMemory sqlcluster
Set-SqlMaxMemory sqlcluster 25600

CloudFlareDynDns

Updates specified CloudFlare DNS hostname to the current connection's external IP address using CloudFlare API v4 https://api.cloudflare.com/

This module is useful for homelabs. Remember how DynDns used to dynamically update your IP address for free? The functionality provided by this module is similar but updates CloudFlare hosted domains. CloudFlare is free and awesome, and I recommend it, if even for its simplified DNS management and free HTTPS.

This should be setup as a scheduled task. I set mine for 5 minutes.

Examples

Update-CloudFlareDynamicDns -Token 1234567893feefc5f0q5000bfo0c38d90bbeb -Email [email protected] -Zone example.com
Update-CloudFlareDynamicDns -Token 1234567893feefc5f0q5000bfo0c38d90bbeb -Email [email protected] -Zone example.com -Record homelab
Update-CloudFlareDynamicDns -Token 1234567893feefc5f0q5000bfo0c38d90bbeb -Email [email protected] -Zone example.com -Record homelab -UseDns

modules's Issues

Bad Request 400

I have a CNAMES and MX in my DNS zone.
I had to modify this line to include the type
$recordurl = "$baseurl/$zoneid/dns_records/?name=$hostname"
to
$recordurl = "$baseurl/$zoneid/dns_records/?name=$hostname&type=A"

Support for updating multiple records

Hi - Many thanks for the excellent script.

I'd like to be able to update multiple records at once. The reason for this is that I'm calling the script using Windows Task Scheduler and it would be much easier to process all of my subdomains with one task, instead of multiple tasks.

I realise I could go down the CNAME route - but I'd prefer not to do this.

Kind Regards
Chris

TLS needs to be set to 1.2 for CloudFlareDynDns

I was getting https 426 errors while running this module. This is due to invoke-restmethod defaulting to TLS 1.0.

You can set this by adding this line of code into your module:

#Set TLS Verson
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

Possible bugfix / speed boost

Hi @ctrlbold :) I was just looking over the latest version of Test-SqlImportSpeed, and line 515 ($datatable = $datatable.Clone()) jumped out at me as being a potential bug. You're calling Clone() on the same DataTable object that you've just populated and passed to the background thread, and I'm not certain what state it will be in when that happens. (Will it still contain any rows? If so, does the script waste time importing the same rows twice? etc.)

The idea was to maintain a separate, empty DataTable object with the columns initialized, and clone that after passing the populated one off to a background thread. It's a small change that might have a pretty decent impact on speed and reliability:

# Around line 453, just after setting up the columns on the $dataTable object:

$emptyDataTable = $dataTable.Clone()

# Line 515:

$dataTable = $emptyDataTable.Clone()

Updating root record fails when a CAA record type exists for the domain

If i have example.com, i have an A record for that in cloudflares DNS which is called example.com
I then set up a CAA record type which has the same name, example.com, with a value i.e. 0 issue "letsencrypt.org"

When the script runs, using the below invocation, it tries to update the CAA record.
Update-CloudFlareDynamicDns -token TOKEN -Email [email protected] -zone example.com

As a workaround, i appended "-record example.com" to the above command, which initially created a duplicate DNS record for the root domain, but once i had deleted both and reran the amended invocation, below, it seems to function correctly.
Update-CloudFlareDynamicDns -token TOKEN -Email [email protected] -zone example.com -record example.com
While this does run, given -record is specified, the console output will say updating example.com.example.com.

I'd call it an edge case, given the number of sites currently using CAA, however it may be worth fixing correctly. Maybe ignoring CAA record types unless -type CAA is called? I wouldn't hold your breath on a PR from me, though i will give it a go.

Input array is longer than the number of columns in this table error

Hi,

Thank you so much for the ppt explanation and also the super fast code.
But I get this input array error I created the table in sql and I tried to load very small set of data (6 rows) to test it and all the rows are properly formatted ( tab delimited) . Can you please let me know why I am facing this issue.

Regards

Support for "Authorization: Bearer <token>"

Hi guys I will work for Authorization: Bearer support only access DNS of a specific zone using this code as support... just with a little change in getting the specific zoneid and hostid as arguments

Add IPV6 support

Hi,

Would it be possible to add support for IPV6 to the module?

Thank you.