Category Archives: Software

Programs that help me that I think others could benefit from. These are all generally open source.

Make Excel automatically divide and conquer your csv files

I have been working with power shell and creating .csv files for import with another script.

I need to work with my .csv in Excel to massage the data and was finding it annoying that Excel wouldn’t convert my comma separated data for me into columns. CSV stands for Comma Seperated Values doesn’t it??

I did some digging and found a line I now add to the top of my .csv that tell excel what my separation character is.

Add the line below to top of your output to get the same result

sep=,

You can interchange the comma with whatever character you prefer.

Note: depending where you are and the character you like, you may want to review your regional settings as some countries use comma to separate sections when writing currency values.

How To Excel At AD Group Management

One trick I like that I think is worth passing on is to use Excel to simply your life when it comes to populating user groups. I do my best to have all user groups essentially the same in all my sites with the prefix being the only difference.

I make a master group that will hold all my sub (site) groups. My quickest way to populate that is to use excel to generate my list and paste it into the Select box.

All my sites have a 4 character code at the start. In Excel, I put my first site code in A1. I move to B1 with the next and so on.

In the 2nd column (B1), I put =A1 & “_site_group_name;”

Note: the ; at the end in crucial as it is a delimiter between values. Then select the cell and bring your mouse cursor to the lower right hand corner until it becomes a Plus.

If you don’t have any empty rows, you should be able to double-click at this point and have it auto fill in all your other group names.

Select all the entries in your B column and paste into your Select Users, Contacts.. box to add to your group. Hit OK and you should be fine.

Sure beats typing or searching for them all.

Spiceworks, a free solution for tickets and inventory

I’ve started becoming more familiar with Spiceworks. For anyone who doesn’t know, Spiceworks is a (web-based) ticketing system that also has an inventory component. The host OS has to be a Windows machine but it can handle Apple and Linux products when it comes to inventory.

Spiceworks is also a tech community based site that has forums where I have found a few solutions. You can also find additional plugins there to add to the apps functionality. You can create individual users and control what each has access to. The favorite feature for me is that tickets are pulled via email and you can manage tickets via hash tags in emails. Spiceworks is free to use as it is Ad-Supported. You can pay to not have Ads which would mean your company would be one of the company’s whose ads are shown. I think most of us find the ads actually interesting as they are from other techs providing services we would find useful. Either way they aren’t obtrusive.

I have used spiceworks to collect inventory. You have a few options here. With a standalone server, you can either have the server run scheduled scans. You may need a balance here as you don’t want to run them too often and bog down the network and also don’t want to miss machines that aren’t always on.

I have tried pushing a remote agent via GPO which works as well. Spiceworks has recently added options to help with this option as you can limit how often the agents phone home and reduce server load. We had issues with this as spiceworks has limited connections and we found ourselves locked out as the agents were using all the connections. Due to this we ended up stopping its use. It was unfortunate as the inventory reports had a wealth of data.

The other alternative I’m trying out is using remote sites. This means you install an instance of Spiceworks at every site and configure the local range to scan information. That server then reports back to the master server with it’s site name as part of the data. You can then run reports per site in case you have assigned techs. I’m only testing with it now and will post back how it goes.