Stuff I Do

Use this single line command to re-index every table in a Microsoft SQL Server database:

EXEC sp_msForEachTable
  @COMMAND1 = 'DBCC DBREINDEX ("?")'

We use an open source software package called GLPI to track our equipment and the trouble tickets associated with them. A while back I had built a ASP.net front end for our end users to enter their own trouble tickets. Since the database is populated with every computer, printer and phone in the company there was a simple little interface that allowed them to select a radio button for say printer and then a drop down box would appear with all the printers in the company with the printer’s location for them to select the printer that is having the issue.

The problem came into the fact that I thought people would be conscientious enough to actually select the device that was actually having the problem. This was not the case. Instead all the trouble tickets were being generated for the first piece of equipment in the corresponding list. A bit frustrating as we would then have to drop into the database and change the association between the ticket to the correct equipment.
This needed to stop so here is how I did it.

First off I changed the SQL query to add one more select option to the top of the list of options they could choose from. For the printers the query now looked like this for the printers:

SELECT * FROM
   (SELECT  id,
       CONCAT(name,'  {  ',comments,' } ') AS name
   FROM glpi_printers
   WHERE deleted = 0
       AND is_template = 0
       AND name <> 'shelf'
   UNION ALL
   SELECT  0,
       '--SELECT A PRINTER--'
   ) as t1
ORDER BY
   name;

This now adds the option –SELECT A PRINTER—at the top of the list with an ID value of 0.
Next I add a CompareValidator control to the drop down list that displays the equipment listing.

<asp:CompareValidator
   ID="cvDevice"
   runat="server"
   ControlToValidate="ddlSelectedDevice"
   ValueToCompare="0" type="Integer"
   operator="GreaterThan"
   ErrorMessage="<br /><strong>You must select a valid device for support!</strong>"
   display="Dynamic"
   SetFocusOnError="True"
/>

Now when they create a ticket if they skip over the dropdown list for the equipment, they will get a message telling them to select a valid device. While this does not guarantee that they will actually choose the equipment that is in need of support, I have a better chance at getting the correct equipment selected.

Working on an intranet app and was trying to get a javascript function to run onchange of a drop down box. We use IE7 as our standard web browser so as I was testing it I kept getting this message stating “Object doesn’t support this property or method”. Kept limiting my javascript function to I finally just had an alert(“TEST”) that wouldn’t even work.

I was calling the function medicalPlan and it related to an form with an id=”medicalPlan”. Apparently there is a problem if you call a javascript function with the same name as the form element’s id. As soon as a renamed the function everything worked as you would expect it to.

I have never really done much with PHP. I do very little coding and when I do it has always been a Microsoft centric language. For whatever reason, it can’t be boredom I have too much to be doing, this weekend I started fiddling around with PHP. I am actually working through a PHP tutorial so I actually learn the correct ways to use the language instead of just mashing things together like I have done in the past.

Not sure if it is the procedure or the documentation that I am using but it seems to be a very easy language with incredible power available to use. At this point I have only worked through operators, arrays and loops but I have been impressed with the little bit that I have done with it. I am curious once I get to the point of pulling data from a MySQL database and that should be fun.

I have no idea what I am going to do with this knowledge, we use ASP and ASP.net at work.

Our maintenance department uses a CMMS package called MP2 for all maintenance work orders. The software is based on a MSSQL back end which has allowed us to create many additional reports and web interfaces to help the maintenance staff handle the work orders. Some of the work orders are scheduled PMs and they have a due date. Part of their review is making sure they have the work orders closed on time so to help them out I created a SQL Server Reporting Services report that will email them a list of work orders that are due the next business day.

First I have to get a listing of all the work orders that are due the next business day and then if it is Friday, get all the work orders that are due up through Monday. Below is the TSQL to get that info:

select
	wo.wonum,
	woeqlist.location,
	equip.description,
	wo.taskdesc,
	wo.wotype,
	wo.atfirstname + ' ' + wo.atlastname as fullname,
	wo.schedfinishdate
from
	wo left join woeqlist on wo.wonum = woeqlist.wonum
		join equip on woeqlist.eqnum = equip.eqnum
where
	schedfinishdate
		between
			cast(convert(char, getdate(), 101) as datetime) + 1 and
			case
				when (select datename(dw, getdate())) = 'Friday' then cast(convert(char, getdate(), 101)as datetime) + 3
				else cast(convert(char, getdate(), 101) as datetime) + 1
			end
	and status != 'C'
order by wonum

This will return the work order number, which plant location the equipment’s name, what the title of the work order is, type of work order, who is assigned to the work order and the date it is due as a extreme reminder which day they need to complete this job.

Throw that in a Reporting Services report and we are almost ready. I don’t want to send an email unless there is actually a work order that is due for the following day. For this I create a Data Driven Subscription in the Reporting Services web page for this report.

To do this go to the report you want to send out in the Reporting Services web page and click on the Subscriptions tab. Select the New Data Driven Subscription. Give this subscription a description and select the Shared Data Sources option and click Next. Pick your shared data source and click Next. Now we will make a slightly modified query to get back an email address if any records are available.

select
	email = 'MaintenanceDept@domain.com'
from
	wo
where
	schedfinishdate
		between
			cast(convert(char, getdate(), 101) as datetime) + 1 and
			case
				when (select datename(dw, getdate())) = 'Friday' then cast(convert(char, getdate(), 101)as datetime) + 3
				else cast(convert(char, getdate(), 101) as datetime) + 1
			end
	and status != 'C'
group by email

This will return us just one record of MaintenanceDept@domain.com if there are any number of work orders due for the next business day.

On the next page in the TO: field select the database field email to include the needed email address. Set any other properties on this page and click Next. Set the days and hours that you want the report to run and you are done.

Now an email will go out only when there are work orders that are due tomorrow and if no work orders are do, no one gets an email.

In a later post, I will show a super cool user defined function that you can use to take into account holidays and other office closed events.

We are in the process of moving from a Windows 2003 server to a Windows 2008 server for our intranet and one column in one page of ASP.net code was returning System.Byte[] instead of the appropriate value. This only happened on the 2008 server and the 2003 rendered the data correctly. Apparently this is an issue when your database is MySQL, as mine is so you have to wrap the offending data in a CAST. Such as this simple example:

SELECT column1, CAST(column2, AS CHAR) FROM table

Once you have CAST the value to a CHAR it will work correctly again.

I am using ODBC connection to the MySQL database and on the 2003 box it is MySQL ODBC 3.51.12 Driver and on the 2008 box it is MySQL ODBC 5.1.4 Driver. Could it be the ODBC driver that causes the issue? Maybe but I am not willing to take the time to try different ODBC drivers for something that a CAST statement fixes.

I am working on a web app to replace a crusty old vendor supplied application for our maintenance work order generation software. The application is primarily a Win32 application and the vendor built this bolt on web app to make it easier to have users generate work requests. The problem I have with it is that it is the most convoluted way to enter a work request with big gaudy buttons and the fact that they charge you on a per logon basis to for the application. It is sold in packs of 25 users and we have used up the initial 25 users and don’t really feel like purchasing more for an application that has not had an update since it’s initial release in 2001!

Anyway I am working on replacing this web app with one of my own creation. One of the good things the original application has is the ability to email the requester any updates that might happen on their request as it makes its way through the maintenance department. The way the vendor app does this is they have another table in the database that you add an email address for each of the 25 users who have access to application. Not a real elegant way to do this, especially when you open up the app for every user in the company as I do not want another database to update if someone comes or goes. The downside of our logon names is that they don’t match up to our email addresses, meaning logon names are a series of characters while email address are first initial plus last name at domain, so I can’t just take the logon name and stick the domain at the end of it. The only way to do this nicely is to talk to AD and do a search on the directory for the mail address using the logon name as the filter.

First thing you need to do is turn off Anonymous Access in IIS to where this app is located. From the IIS manager drill down to the directory where the app is located. Right mouse click on the directory and select PROPERTIES. Click on the Directory Security tab and then in the Authentication and access control, click the Edit button. Remove the tick mark on the Enable anonymous access box and tick Integrated Windows Authentication. Click OK twice to save out the settings. This allows you to capture the intranet user as they access the page.

Then at the top of your page you will need to import the directory services namespace and the assembly and we are also going to import the system.net namespace so we can do a workstation lookup also.

<%@ Import Namespace="System.Net" %>
<%@ Import Namespace="System.DirectoryServices" %>
<%@ Assembly Name="System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"%>

Then in your code you will need to declare your items. You will have to setup the LDAP string to match your own domain.

Dim strUserName, strFullName, strCompName, strEmail, strPhoneExt As String
Dim entry As New DirectoryServices.DirectoryEntry("LDAP://OU=Users,DC=domainname,DC=com")
Dim mySearcher As New System.DirectoryServices.DirectorySearcher(entry)

Then personally I like to plug these items into the page_load but you can do it where ever you want.

Sub page_load(ByVal src As Object, ByVal e As EventArgs)
  If Not Page.IsPostBack Then
   getUser()
   getUserContactInfo()
   getWorkstationInfo()
  End If
End Sub

Now for each function in the page_load starting first with the getUser. What is happening here is that the logged on username is captured when they access this page. In my domain, usernames are a string of characters that is not the user’s name, so to get the easy to read full name I need to query AD. First I set the strUserName and strip off the domain name so it is just the pre Windows 2000 logon name. In the second portion of this script do the actual query on AD using the stripped down username as a filter to get to the displayName value contained in AD. At the end strFullName will contain a value such as “John Doe”.

     Sub getUser()
        ' set username and trim off the domain name
        strUserName = System.Web.HttpContext.Current.User.Identity.Name
        strUserName = UCase(strUserName.Replace("DOMAINNAME\", ""))

        ' search AD for full name
        Dim resultFullName As System.DirectoryServices.SearchResult
        mySearcher.Filter = ("(sAMAccountName= " & strUserName & ")")
           For Each resultFullName In mySearcher.FindAll()
               strFullName = (resultFullName.GetDirectoryEntry.Properties("displayName").Value)
            Next
    End Sub

With the getUserContactInfo I am gathering the user’s email and phone extension to be saved into the database along with the user’s request for quick easy return contact with the requester. This gets a little different as we use the AD fields a little different when it comes to phone numbers. The Phone Number field in AD we use for DID number and is not real useful for internal work order. Our phone extensions are entered into the Office field and so that is what we are going to query to return the extension of the user in this section.

   Sub getUserContactInfo()
        ' search AD for email address and phone ext for the current user
               Dim resultEmail As System.DirectoryServices.SearchResult
               mySearcher.Filter = ("(sAMAccountName= " & strUserName & ")")
               For Each resultEmail In mySearcher.FindAll()
                   strEmail = (resultEmail.GetDirectoryEntry.Properties("mail").Value)
               Next
               Dim resultPhoneExt As System.DirectoryServices.SearchResult
               mySearcher.Filter = ("(sAMAccountName= " & strUserName & ")")
               For Each resultPhoneExt In mySearcher.FindAll()
                   strPhoneExt = (resultPhoneExt.GetDirectoryEntry.Properties("physicalDeliveryOfficeName").Value)
               Next
    End Sub

In the last section, the getWorkstationInfo, I want to know what workstation they were on when they issued the request. This is pretty simple and straight forward on how you gather the workstation name. It comes as a FQDN which I don’t really care for. Since we have only a single namespace, the domain name is worthless to me, so to save space and clean up the look, I strip it off.

   Sub getWorkstationInfo()
        ' set workstation name and trim off domain name
        Dim host As System.Net.IPHostEntry
        host = System.Net.Dns.GetHostEntry(Request.ServerVariables.Item("REMOTE_HOST"))
        strCompName = host.HostName
        strCompName = strCompName.Replace(".domainname.com", "")
    End Sub

There are many ways to do this and I am not a programmer by any stretch of the imagination, but this works for me. Your mileage may vary on this, but I thought I would throw it out there and maybe it will benefit someone.

I am a noob when it comes to any kind of full web programming, so I am learning by using ASP.net to create pages. Everything is going well and I am getting data returned from the database and have it bound to a GridView. The problem I was running into was that I wanted to print an error message to the user if the search string they entered returned zero rows. I needed a way to read the DataSet and goto the error if it was empty. Took a little bit to figure out but here is what I came up with, real programmers feel to show a more effective way if you have one.

If dbDataSet.Tables.Count > 0 And dbDataSet.Tables(0).Rows.Count > 0 Then
   ' your data here
Else
   ' error message here
End If

Most of the documentation and samples of using parameterized queries assumes that you are using MS SQL server as your backend. In that setup you can use this sort of SQL code in the asp:SqlDataSource tag:

SelectCommand="SELECT tracking, date, author, contents, private FROM glpi_followups WHERE tracking = @tracking"

Trying to use this same code for MySQL will return nothing. To get it to work with MySQL you need to use a ? instead of the @parameter.

SelectCommand="SELECT tracking, date, author, contents, private FROM glpi_followups WHERE tracking = ?"

What I have not figured out is if you have more than one parameter how do you use make one ? seem different than the next ?. If anyone has the solution to that one, please let me know.