The double-edged sword of open-source

Standard

I got involved into a discussion about open-source software. Apparently a maintainer of an open-source project handed over the rains to another person and the other person changed the software to include a coin/mining exploit.  This really got me thinking about the double-edged sword of open-source.

Where did open-source originate from?

A little history lesson about open-source projects. Open-source came to be in 1998 where it was developed after Netscape’s announcement that the software for the Navigator software was going to publicly released.

The term got more momentum during the Freeware Summit organized by Tim O’Reilly in April 1998.

The highlight was when the Open Source Summit which is known as the birth of open-source.

So what’s the problem with open-source?

At the same time there is one and there isn’t one.

Open-source is a blessing in my opinion. It enables developers to help out with projects and collaborate. It gives companies the choice to either go buy proprietary software or to choose the option to go with open-source which is mostly free.

The problem with open-source, and which most people have forgotten, is that as soon as the software is released the maintainer has no responsibilities towards anyone. The software is provided “AS IS” which is mentioned very clearly in the license.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
OTHER DEALINGS IN THE SOFTWARE.

This also is both a good and a bad thing.

It’s a good thing because it releases the maintainer of any duties that have to do with the software product. Other people can contribute and create their own features which the maintainer is not reliable for. I certainly would not want to be responsible for a bug in one of my open-source projects and having to deal with large claims from companies because they lost revenue.

On the other hand, because the maintainer has no responsibilities, you are the one responsible from the moment you download the software.

The maintainer of the open-source project doesn’t even to reply to any issues, give you support or any other interaction with the software user. Everything that a maintainer does outside of releasing the software is big bonus and shows the character and passion of the maintainer.

What happened in the discussion?

Apparently the new maintainer changed the code of the project and included a bitcoin miner into it. So everyone who actually used that software and updated it, would get a bitcoin miner installed which would cause a lot of problems.

Immediately you would think that the original maintainer would be the person responsible because he didn’t check the person that wanted to take over the rains.

It is very scary that open-source projects are vulnerable to these kind of practices because at some point we trust the software and put it into our environments. That causes a threat to our production environments.

In reality, maintainers of an open-source project, are in no way responsible for these kind of issues. As soon as a piece of open-source software is released, the maintainer has done it’s work.

How can we avoid this?

I don’t think we can actually avoid these kind of problems. If for some reason such a thing happens mostly of the time people will find out quickly.

News like this will go quick especially with social media platforms like Facebook and Twitter, projects that are compromised will become flagged very soon.

I think we need to be diligent when we start using open-source software. Make sure you use software that is used a lot or is developed by someone with a good reputation.

It’s pretty easy to look into a Github repository and get basic information like who the maintainer is and do some research on the person. If you don’t trust it, don’t install it. Get more information, go ask on forums and ask questions before you start to rely on these projects.

My two cents on the responsibilities

I’m a contributor to a couple of open-source projects and I rely heavily on them in my day-to-day work.

I’ve contributed a bunch of commands to the dbatools projects. I feel responsible for these commands but I’m completely fine if someone else finds a bug and fixes it. When someone finds a bug in a command and notifies me, I will try to fix it. I feel responsible because I created them but it’s in no way only my responsibility.

Maintainers and contributors have no obligation to help after a piece of software is released. Everything that’s done after the release is a big bonus.

I do think that the way the projects are run reflects the types of people who are involved. Regularly I get questions from people all over the world about a command I created.

I will always reply to these questions because I think that it’s important to give support in these projects and it helps the projects being.

Conclusion

Open-source software is great and most of the time you get a reliable version for free or a small amount of money. You, as the user, have to make sure that the project can be trusted.

Closed software is great too but it can be costly. The cost for the software gives a sense a security and a lot of companies prefer to pay for software instead of using open-source.

Both have their pros and cons and you must decide what’s best for your use-case. I use both open-source and closed source and use them for my use-cases.

 

Troubleshooting Dynamic SSRS Queries

Standard

The Problem

In my daily work I have to work with a lot of SSRS (SQL Server Reporting Services) reports that have either stored procedures, queries or dynamic queries to get all the results.
Troubleshooting dynamic SSRS queries can sometimes be difficult. That’s is especially the case when you’re dealing with multiple hundreds of lines of code all generated dynamically.

An example of such an error is:

Error Dynamic Query

Because the query is generated during execution using all kinds of parameters, you don’t know how the query looks like exactly. Debugging this query would be pretty difficult and tedious, but there is an easy way to get the compiled query.

How does a dynamic query look like

If you never used dynamic queries in SSRS you might not know how that looks like and when it’s used.

A dynamic query in SSRS is actually an expression in a data set. The expression is build upon execution and can include TSQL code, SSRS fields etc.
The reason I use this in my reports is because I sometimes need to implement complicated filtering in my reports using the report parameters.

You can filter the data using the “Filters” tab in the data set but that would cause me to generate to extensive amount of data during execution which I’d like to avoid.

When you open a data set with an dynamic query it looks like this

Dataset Window Dynamic Query

You don’t see much of the query because you needto click the “fx” button on the right to get the following screen:

Expression Window Dynamic Query

The “=” sign indicated the start of the expression. For readability reasons I join every new line with the “&” sign. I could have used one big line of code but this makes it easier to read.

During execution I use several parameters to filter out values. That can be seen on the last two lines in the expression the image above. Using this method of filtering cuts down the amount of data I retrieve during execution.

The solution

How can I look at the query after it’s executed? The solution is to create a text box and enter the following expression:

Solution Dynamic Query

This expression will display the query of the data set in the text box during execution.

This doesn’t solve the problem yet because we’re still dealing with the problem that the query fails during execution.

How to display the query without executing it? The solution is really simple but you it requires some understanding how SSRS compiles the expression during execution.
The fact is that the query expression is compiled as a single line of code.

To display the code we can use the “–” to create a comment.

Commentec Expression Dynamic Query

This will render the query but it will not execute out query:

Compiled Query Dynamic Query

There you have it, your compiled query.

The next step would be to copy this code and paste it to SSMS or SQL OPS to further debug the query.

 

Looking back at my first lightning talk

Standard

I had the opportunity to speak at the PASS Summit 2017 with a lightning talk.

This was the first time I ever did a lightning talk and it was different than a normal session.

It all boils down to the fact that you only have about 10 to 15 minutes for the entire talk.
This brings a couple of complications because suddenly you have to make sure your content fits within that time frame. You also have to make sure with the amount of content that you don’t go too fast. Going to too fast in a lightning talk is disastrous because attendees will not be able to follow you.

I normally do 60 minute sessions where I have the time to send to dig a little deeper than originally planned.

So how can I do such a short session and still make sure that the audience gets a bang for their buck?

After thinking about it I made a couple of steps:

  1. Write down the subjects I wanted to talk about
  2. Make the content for the subject and made sure it was short and to the point
  3. Present the content out loud and record it

During the recording I would watch the timer in PowerPoint to see when I would hit the 10 minute mark.

If I had gone over I would go into the content again and try to adjust it.
If I made it within those 10 minutes I would watch the recording and pay attention to how fast I was talking. I had to adjust multiple times to make sure I wasn’t going too fast.

After a couple iterations I was satisfied with the preparation and I could go into the presentation with confidence.

How did the session go?

This was actually really funny. I noticed on my itinerary for my flight that I would have to leave to the airport before 10:00 AM. The lightning talk sessions were from 9:30 AM to 10:30 AM so I had to make sure I was the first one to present because otherwise I wouldn’t be able to make it to my flight.

Fortunately the other presenters were so kind to let me go first and I thank them for it.

Because I prepared this session pretty well everything went smooth. I was able to do my talk and show some a couple of demos and finish within the 10 minute frame.

A couple of weeks later I got the feedback from several people from the audience and I was excited about that. Lots of people liked the content and the overall session so that was a big win for me.

Conclusion

Looking back this was a very good experience for me. I find doing a lightning talk is way more difficult than a normal session. It all comes down to preparation and placing yourself into the audience.

If you attended the lightning talks as PASS Summit 2017, please leave a comment because I’d really like to know your opinion about what went well and what didn’t. There is always an opportunity to learn and I like to get better with every session I do.

 

Pretty up your KeePass

It's So Beautiful
Standard

Having a password manager these days is almost required especially for people working in IT. One password manager that I’ve been using for years is KeePass. You can secure the database with a master password and additionally (and recommended) create a key file.
You can create folders to differentiate the entries and it has a ton of features to make password management easy.

To make things even easier several browsers like Firefox and Chrome support using KeePass to handle passwords for websites.

If you don’t already have some sort of password manager, and I don’t mean the Post-Its you have on your monitor, I would tell you to at least give it a try. There are other solutions out there but this one is free and has made my life a lo easier.

So back to the original subject, prettying up KeePass. You might have noticed when you use KeePass that you can assign different icons to an entry.

Entry Details

And when you click the icon button you can select many standard icons.

Icon selection

KeePass makes it possible to add a custom icon and save it into the database. You don’t have to have the icons available on your computer.

As you may see in the image above I also have a selection of custom icons that I’ve added for my entries. In example I have an entry for LinkedIn for which I have the official icon for.

These days almost every bigger companies website has it’s logo in the top bar of the browser. That’s called a “shortcut icon”. Most websites have them named like “favicon.ico” or something similar.

If you open the code of the website and look for the i.e. shortcut icon you will get an URL that points to the icon. Copy the URL, open the password entry, click the icon button, click the add button for custom icons and paste the URL into the filename textbox.

Add new icon

Click the “Open” button and give it a couple of seconds to get the icon file downloaded and imported into the database. After the import of the icon it’s selected in the icon picker window. Click “OK” and you’ll see that your entry has it’s own icon.

Icon selection result

Now what are you waiting for, go pretty up your KeePass entries.

My First Virtual Presentation

Standard

firstOn March 16 I will be presenting my first virtual session for PASS and I’m really excited!

The session is called “Documenting SQL Server with PowerShell”.

The abstract:

Documentation is mostly overlooked and only comes up when a problem arises. What if you’d have a tool or method to generate documentation for all your database servers? In this session, I will show you show how easy it is to use PowerShell to retrieve information from your servers. I’ll detail what can be used to document your servers, how to retrieve the information and what should be documented. In the end you no longer have an excuse not to document your servers.

If you’re interested you can register with the link below and I’ll see you on March 16th on the PowerShell Virtual Chapter.

Registration URL: Documenting SQL Server with PowerShell
Webinar ID: 153-105-707

 

DBA performance evaluation

Standard

evaluationLike most people I get my yearly performance evaluation but I’ve had the experience that my peers don’t really know how to evaluate me. So how do you effectively evaluate a DBA.

Your manager/team leader/superior has the information from previous performance interviews and based on that sees if you have performed as you should, or under, or above what was expected.

What I found out is that when the IT department doesn’t have a good understanding of the DBA’s responsibilities and possible metric to measure the effectiveness of the DBA (group) the evaluations don’t work.

How are most evalutions done

Your superior should have at least the following information:

  1. Knowledge of the DBA’ s job requirements
  2. Record of classes / training attended throughout the year
  3. Attendance records
  4. Productivity reports
  5. Records of previous performance interviews

You and your superior come together and based on the information above your superior shows how you performed.
In most cases you only have little room for discussion because most things are already discussed in previous interviews.

The problem

The problem is that there are so many factors that make this so difficult. A few questions that come to mind are:

  1. There are many different types of DBA’s, which one is used for evaluation
  2. What are the metrics to measure the performance and effectiveness on
  3. When is a DBA successful and and when is his/her performance good/medium/bad

The problem with this is that most managers I had (don’t get offended if you’re one of them this is just my experience) only look for certain aspects that can be found in other IT related jobs like how many incidents you solved or changes that are made.

You can look at the amount of incidents that were registered and fixed during that year but that’s only the case when you talk about real incidents like a server going down, low disk space or performance related problems.

This can be very offending towards other people who could do a tremendous amount of work in the background but is never really visible.

A DBA is a “jack of all trades” kind of person but to clearly evaluate a DBA you have to have a clear understanding what he/she does.

The solution

To address the first question in Problem-paragraph Craig Mullins wrote a nice article about this which gives a good overview of what kind of DBAs there are. Of course there are different versions and flavors especially with the DevOps who are hot nowadays.

An article that helped me out and for most still applies is an article written also by Craig Mullins called “Measuring DBA Effectiveness“. It helped my manager and me to create an evaluation metric.

The last thing, and I surely hope that’s in place, make reports from your service management application like Topdesk or SysAid. The reports give a clear understanding of the workload that’s been done.

To have another metric for the workload I used Brent Ozar’s “sp_Blitz” to create a health-check with all the items that need to be addressed. When an item of the list is done, remove it or give a status done, to keep track about what’s been done. When the manager asks for the progress of the list you can hand over the list.

I hope this helps you out for your next evaluation.

 

Visio Stencil for SQL Server: Updated

Standard

When I look at the statistics of my blog a lot of people seem to search for the Microsoft Visio stencil I once made somewhere in 2010. Unfortunately I haven’t updated the stencil for the new SQL Server versions.

I updated the stencil and created some new objects, adjusted some coloring.

visiostencil_sqlserver

If you’re interested, you can download it from here

Lorem Ipsum feature in Word 2007/2012

Standard

Every webdeveloper or programmer is familiar with the Lorem Ipsum text used to fill up pages or output text.

When I write documents I start with creating the chapters and fill them up with random text. The use of Lorem Ipsum generators makes this easier, but wouldn’t it be easier if there was a feature that did this for you instead of copying and pasting the text from generators?

It turns out that Word 2007 and Word 2012 have an undocumented feature for creating lorem ipsum text parts.

All you have to do is use the following code:

and hit the enter key

After hitting the enter key you’ll have the lorem ipsum text inserted:

The feature has some extending parameter which makes it easy to generate large amounts of lorem ipsum text.

You could do the following to create 5 paragraphs with 14 sentences:

I really like this feature and maybe you too.