Category Archives: PowerShell

SharePoint List – Delete Columns Through PowerShell

Today my colleague ran into an issue where she added columns to a SharePoint 2013 list, later realised they were not what she wanted and was unable to delete them.

I ended up doing up the following PowerShell script to delete the columns based on some examples I found online in both C# and PowerShell.  The key one I found was on SharePoint Diary; however, this did not work for me but was close to what I had already started.

The full script that worked for me is as follows:

Continue reading

Rename Files with PowerShell

While on holidays I started scanning in a bunch of pictures for a family project and since this was being done in batches each batch of scanned images would be named childhoodXXXXX.jpg where XXXXX is the number of the current image in sequence.  This would be the same for each batch.  Since wanted a means of knowing which images were part of which batch (and thus the corresponding package they came from) I wrote a little script to rename files in a folder with the following requirements:

  • Prepend batch number to beginning of file name
  • Prepend to files that did not currently start with a digit (since we know all names start with childhood)
  • Script is being run from the directory containing the files
  • I didn’t include a recursive folder structure so all files are in the same directory

The script is as follows:

Continue reading

Ping Results to Database

I updated my script to write the results to a database to allow for easier manipulation of the data through queries, instead of having to traverse a file, linearly.  The database design is very simple, consisting of only two tables, and can be easily expanded to contain more information about the device.   The bold fields in the tables are required fields.  There is a 1-to-many relationship between the device and the ping results, since each result can only be for one device, but each device can have many results.

Ping Results Database
Ping Results Database

The full script can be seen at the bottom of the post.  The script starts a job running the ping script block 4 times, sleeping for 14 seconds in between.  It is not an exact science here, but the idea is that the script can be scheduled using Windows Task Scheduler to run every minute and will ping all the machine on first starting and about every 15 seconds to make 4 times in a minute.

The script block creates a new SQL Client Connection object and opens a connection to the database, using the following code:

Continue reading

Ping Results with CSV

This is a project that came out of a wish list from a coworker to monitor,  at a glance, if something is up or down as well as have a bit of a history to analyze for any slow or dropped pings, hence the graph.  The first phase (script) ping.ps1 for performing the ping tests is from an external source that my coworker found and then modified by the two of us to get what we want, the second phase of the process is GeneratePingCharts.ps1 written by me to generate the graphs from the output of the first script using RGraph which is an HTML 5 charts library.

GeneratePingCharts.ps1 output sampleping.ps1 output sample
Ping Results GraphsPing Results HTM

GeneratePingCharts.ps1

I am writing about this one first since I wrote it from scratch. The script calculates the ping response time averages for month, week, & day utilizing the data collected and saved in a CSV file and outputs the graphs and averages to an HTML file. For flexibility I allow the folder containing the CSV files, where the HTML should be outputted, and the location of the RGraph library files to be passed as parameters to the script.

The script starts by outputting the CSS utilized and then gathers the list of files containing the CSV extension from the folder path passed in.  For each file found a new job is started, with a maximum of 10 jobs running at a time, to process the CSV file.  The script waits for all 10 jobs to finish  and then removes the jobs prior to starting 10 more jobs.  This process continues until all files have been handled.

The line which starts the job by running the script block and passing the parameters is:

Start-Job $ProcessCSVScriptBlock -ArgumentList $file.FullName,$outputPath,$rGraphJS

The script block then initiates the processing of the CSV file by calling the Process-CSV function.  The CSV file is imported into a variable using the Import-CSV PowerShell cmdlet followed by each row being processed.  The sum of all the response times and number of response times collected is tracked to calculate the averages.  The response times are stored in a string with the format [a,b,c,….,z] where a-z is the response time value and will contain as many as there are stored in the CSV for the given time frame.  The only exception to this is month, which stores the average response time over $mInc points; otherwise, the graph would not draw do to the large quantity of points.  All this data is then stored into a custom object which is passed to the Generate-Chart function.

The Generate-Chart function stores the HTML for the graph page in a variable inserting the appropriate information stored in the $chartOut parameter in with the HTML string so that the:

  • Machine name and run time are displayed at the top
  • Averages are displayed in a table
  • Day, week, and month graphs contain the appropriate data points.

Worth mentioning is that the Get-My-Date function is used to convert the date stored in an unsupported format (MM_DD_YYYY) to a supported format (YYYY-MM-DD) so that within the Process-CSV function the date can be compared against today’s date allowing the script to know which graphs the ping result belong too.

Continue reading