Creating SCSM tickets using SMLets and ASP.NET

English: This is a photo of a room full of computers all showing the same windows error message at the Nanyang Technological University in Singapore (Photo credit: Wikipedia)

I was trying to figure out how we could leverage ASP.Net to create more functional forms for end user ticket creation in SCSM.

Many would agree that the self service portal is a bit restricted and we needed a method for our Apps group to submit Change Requests that would comply with SOX.

I found a few examples around the net regarding the power of SMLets and PowerShell and eventually found that there are classes available in .Net for leveraging this.

My first major hurdle was determining why I kept getting a strange error message "Unable to access Windows PowerShell PowerShellEngine registry information." when trying to replicate these efforts.

My errors seemed to stem from trying to use assemblies built with prior versions of the .Net library, and instead of writing a wrapper of some sort to deal with this, I installed PowerShell 3 and all my problems went away. (Small clue here)

The caveat to this is that this new flavor is known to cause issues with Sharepoint, Exchange, Configuration Manager and SBS to name a few so read up on the known issues before you create an unresolvable issue.

I chose to deploy my solution into our DEV SCSM environment in same IIS Portal Site area, using our service account application pool created for this purpose. Windows Authentication is also Enabled for this site so we can pick up the users name in the application and use it in the Created By field in the ticket.

You can grab a copy of this project here.

Crystal Reports, Advanced SQL Commands and Parameters

Crystal Reports Pic 4 - Select Data Tables (Photo credit: wmharshana)

I've not written a Crystal Report that required a more advanced query in quite a while, and had a need to union two selects and pass in a parameter to both queries.

I initially just wrote the Union query and attempted to place my {?CustomerName} in both places using the "=" comparison operator, which worked.

When I wanted to add the ability for the user to only add a portion of the customer name in the query and use the LIKE operator, Crystal had some issues and thought I was trying to pass a column name or some such trash.

Eventually I found an article that suggested the use of a DECLARE and SET to hold the parameter that Crystal passes, then hand it off to the query. This also failed until I wrapped the entire UNION in a SELECT and passed in the parameter once.

Yay!

DECLARE @Cust VarChar(200)
SET @Cust = '{?CustomerName}'

SELECT * FROM (
SELECT Name, Address, City, State, Zip From CustArch
UNION
SELECT Name, Address, City, State, Zip From Customers
) a
WHERE Name LIKE '%' + @Cust + '%'