Techie Pie

There are days when I miss Unicenter Service Desk. I feel a bit nostalgic for the days when I was the “It” girl where that application was concerned; I knew every last nuance of the program from the smallest code file to the user interface. Plus it was just downright fun to work with. I spent countless hours making calls to the domsrvr process trying to figure out what syntax in my custom file would work, and what wouldn’t. The day I finally cracked that nut was one of my proudest, and thereafter there wasn’t anything I couldn’t make the application do.

One of the neat features in USD 5.0/5.5 was the ability to attach files to helpdesk requests. Customers had asked for it for years, and when USD 5.0 hit it was a welcome addition to the product.

The drawback, however, was that development had not seen fit to include a utility that would clean the system directory when a file got unassociated from a ticket. In small environments, this wasn’t much of an issue; small shops probably didn’t use file attachments much anyway. However in large corporate environments, where hundreds of files were attached or removed every hour, it created a lot of junk eating up disk space for no reason.

To address this, I wrote a utility in Perl that would delete files from the disk drive when they had been marked for deletion in USD. The only schema mod required to make the code work was the addition of a binary field to the ATTMNT table that Perl could write to. Once I had that in place, as an administrator I could manually run the cleanup program against the system on an as-needed basis.

Header comments:

30 Nov 2001

This script intended for use with CA Advanced Help Desk 5.0 and Unicenter Service Desk 5.5. CA does not include a cleanup utility for the attachment repository. When users to the system remove file attachments from the issue, the physical file remains on the drive share. This causes the drive to grow and grow and grow. I am using the Attachment table in AHD to pull the names of files that the system has indicated as removed (del=1). Based on this, I’m telling Perl to take the filename, connect to the share, and delete the file. When finished, Perl can update the DB with it’s own flag that the file has been removed (zperl_del=1). The field zperl_del was created at the AHD/USD level using a SCH file to modify the database.

Setup:

1. Apply the schema change in zClean_Attachments.sch to your system. Make sure that you back up the Attachment table first!

2. You will need a System DSN for perl to get access to the database. Win32::ODBC is MSSQL-specific. I rewrote this using DBI to make it more portable.

3. The field added to the Attachment table is not intended to be accessed by USD. That’s why we can get away with a direct SQL update (which the domsrvr won’t see) and not have to build objects. If you want the ability to interact with this little field through the USD client, then you need to build it.

#use Win32::ODBC;
use DBI ;

use Time::localtime;
$now = ctime();

chdir (”f:/”) || die “I’ve lost the F: drive! ($!)”;
open (PERLLOG, “>>e:/service_desk/log/perl_log.txt”) || die “Can’t open Perl Log File:$!”;

#—————————————————————————–#
# Connect to AHD database using DSN System connection. Return error if fails.#
#—————————————————————————–#

#my($db) = new Win32::ODBC(’PerlCGI’);
# if (!($db = new Win32::ODBC(’PerlCGI’))) {
# print PERLLOG “$now : Error connecting to DSN PerlCGI:$!\n”;
# }

#———————————————————-
# SQL Logon credentials
# Set server name and IP for easy changing later
#———————————————————-

my $db_username = “username” ;
my $db_pwd = “password” ;
my $dsn = “dsn_name” ;

#——————————————-
# ODBC connection to AHD database
#——————————————-

my $dbh = DBI->connect(”DBI:ODBC:$dsn”,$db_username,$db_pwd)
or die “Unable to connect: ” . DBI->errstr ;

#————————————————————-#
# Need to change all zperl_del IS NULL values to be zperl_del=0.#
#————————————————————-#

#$sqlnonulls = “SELECT zperl_del FROM ahd.attmnt ” .
# “UPDATE ahd.attmnt SET zperl_del = 0 ” .
# “WHERE zperl_del IS NULL “;

#print PERLLOG “$sqlnonulls\n”;

#$db->Sql($sqlnonulls);
# if ($db->Sql($sqlnonulls)) {
# print PERLLOG “$now: SQL no nulls failed.\n”;
# print PERLLOG “$now: Error: ” . $db->Error() . “\n”;
# }

my $sth = $dbh->do( ”
SELECT zperl_del FROM ahd.attmnt
UPDATE ahd.attmnt SET zperl_del = 0
WHERE zperl_del IS NULL
” ) ;

#————————————————#
# Now we need to pull all rows where perl_del=0. #
#————————————————#

# To make debugging & updating easier, I put the SELECT statement into a variable.

#$sqlgetdata = “SELECT del,zperl_del,file_name FROM ahd.attmnt ” .
# “WHERE del = 1 AND zperl_del = 0 “;

#print PERLLOG “$sqlgetdata\n”;

#$db->Sql($sqlgetdata);
# if ($db->Sql($sqlgetdata)) {
# print PERLLOG “$now: SQL get data failed.\n”;
# print PERLLOG “$now: Error: ” . $db->Error() . “\n”;
# }

$sth = $dbh->prepare( ”
SELECT file_name FROM ahd.attmnt
WHERE del = 1 AND zperl_del = 0
” ) ;
$sth->execute() ;

$sth->bind_col( 1, \$thisfile) ;

my @stash;
my $array_ref;
while ( $array_ref = $sth->fetchrow_arrayref ) {
push @stash, [ @$array_ref ];
}

#—————————————————————————–#
# Go find and delete the offending file. Pipe status to $NX_ROOT\log\perl_log #
#—————————————————————————–#

#while ($db->FetchRow()) {
# my(%data) = $db->DataHash();

# print PERLLOG “$now: Query Returns: $data{’del’}, $data{’zperl_del’}, $data{’file_name’}\n”;
# $thisfile = $data{’file_name’};
print PERLLOG “$now: Looking for $thisfile, please wait…\n”;
if (-e “f:/attachment/default/$thisfile”) {
print PERLLOG “$now: File found in DEFAULT share. Time to delete!\n”;
unlink (”f:/attachment/default/$thisfile”) || warn “$now: Unable to delete $_\: $!”;
print PERLLOG “$now: File deleted. =$thisfile\n”;
}
else {
if (-e “f:/attachment/gui/$thisfile”) {
print PERLLOG “$now: File found in GUI share. Time to delete!\n”;
unlink (”f:/attachment/gui/$thisfile”) || warn “$now: Unable to delete $_\: $!”;
print PERLLOG “$now: File deleted. =$thisfile\n”;
}
else {
if (-e “f:/attachment/web/$thisfile”) {
print PERLLOG “$now: File found in WEB share. Time to delete!\n”;
unlink (”f:/attachment/web/$thisfile”) || warn “$now: Unable to delete $_\: $!”;
print PERLLOG “$now: File deleted. =$thisfile\n”;
}
else {
print PERLLOG “$now: File not found in attachment repository!\n”;
}
}
}

#}

#——————————————————————————–#
# Now go and update perl_del to be equal to 1, meaning Perl has deleted the file.#
#——————————————————————————–#

#$sqlupdateperl = “SELECT zperl_del FROM ahd.attmnt ” .
# “UPDATE ahd.attmnt SET zperl_del = 1 ” .
# “WHERE del = 1 AND zperl_del = 0 “;

#$db->Sql($sqlupdateperl);
# if ($db->Sql($sqlupdateperl)) {
# print PERLLOG “$now: Update to zperl_del failed.\n”;
# print PERLLOG “$now: Error: ” . $db->Error() . “\n”;
# }

my $sth = $dbh->do( ”
SELECT zperl_del FROM ahd.attmnt
UPDATE ahd.attmnt SET zperl_del = 1
WHERE del = 1 AND zperl_del = 0
” ) ;

#———————————————–#
# Always close your DB connection when finished!#
#———————————————–#

close(PERLLOG);
$dbh->disconnect;
exit;

One of the projects I’m working on is a rewrite of web portal for users to access some data metrics. A requirement for this web portal is that users must authenticate against a database running on SQL 2005 that contains usernames and passwords fed to it from a 3rd party, non-MS email system. This means, simply, that I cannot use SQL logins or Windows authentication as a means to get users into the web portal.

Now this project is the sort of thing that doesn’t have deadlines attached to it, and is not a high priority. Because of this I’m using it as my learning tool for VB.net and ASP.net. So the first thing I had to figure out was how to transform the original ASP login page into a secure, VB.net login page that would query the database for user authentication.

It took a while. Searches through Google yielded a variety of “how-to” articles on designing VB.net login pages, but they all assumed that I was going to use an ASP.net security setup, or some other Windows-based authentication. I could not, for the life of me, find an example of VB.net code to do what I wanted.

Lucky for you, my pain is your gain. Here is the code behind for a login page in VB.net.

———————————————————————–

Imports System.Data.SqlClient

———————————————————————–
Partial Class _Default
Inherits System.Web.UI.Page

———————————————————————–
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub
———————————————————————–
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
‘ variables for username and password
Dim strUser, strPassword, strPWResult
Dim intBldg, blnAllSchools, intEmployeeNo
‘ set form values into variables
strUser = txtUser.Text
strPassword = txtPassword.Text

‘ echo input for testing
‘Response.Write(strUser & “</br>”)
‘Response.Write(strPassword & “</br>”)

‘ the connection string is defined in the web.config file

Dim MySqlConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(”cnMyConn”))
Dim dr As SqlDataReader

Try
MySqlConn.Open()
Dim SQL As String
SQL = “SELECT * FROM [table] WHERE userid = ‘” & strUser & “‘”

Dim mySQLCmd As New SqlCommand(SQL, MySqlConn)

dr = mySQLCmd.ExecuteReader
While dr.Read
‘Response.Write(dr(0))
strPWResult = dr(0).ToString()
intBldg = dr(1).ToString()
blnAllSchools = dr(2).ToString()
intEmployeeNo = dr(3).ToString()

‘Response.Write(strPWResult)
If LCase(strPWResult) = LCase(strPassword) Then
‘Response.Write(”User valid!”)
Session(”UserID”) = strUser
Session(”Bldg”) = intBldg
Session(”AllSchools”) = blnAllSchools
Response.Redirect(”my_webpage.aspx”)

‘ for debugging

‘Response.Write(intBldg & “</br>”)
‘Response.Write(blnAllSchools & “</br>”)
‘Response.Write(intEmployeeNo & “</br>”)

Else
Response.Write(”Wrong username or password!”)
Session.Abandon()

End If

End While
dr.Close()

Finally
MySqlConn.Close()
End Try

End Sub
End Class

For all its improvements, SQL 2005 still doesn’t provide native support for epoch date/time. So I had to write some functions to do the conversion for me, then configure computed columns to use the functions. Here’s how I did it:

1. Create a function to convert a date to epoch:

USE [tablename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[date2timestamp](
@dateStr datetime
)
returns bigint
as begin

/** Now, find out how many seconds have existed between now and the epoch **/

return convert(bigint,
datediff(ss, ‘01-01-1970 00:00:00′, @dateStr))
end

2. Create a function that will convert epoch back to date:

USE [tablename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[timestamp2date](
@numSeconds bigint
)
returns varchar(20)
as begin
/** Now, find out how many seconds have existed between @numSeconds and the epoch. **/

/** If you need to modify according to your TZ **/

/** set @numSeconds = @numSeconds + (60 * 60 * -6) — subtract CST **/

return dateadd(ss, @numSeconds, ‘01-01-1970 00:00:00′)
end

3. Since my task needed to calculate the difference between the to values, I had to create a third function:

USE [tablename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[epoch_diff] ( @epoch_start bigint, @epoch_end bigint )

RETURNS bigint
AS

BEGIN
RETURN (@epoch_end - @epoch_start)
END

4. To configure the computed column in SQL 2005, you need to right-click on the table and choose Design from the pop-up menu. Add your new column (if not already created), and under Column Properties locate the property Computed Column Specification (Formula).

For this example, it assumes that there is another column in the table with the actual date value that you want to convert to its epoch equivalent. So in the (Formula) field insert the function:

([dbo].[date2timestamp]([fieldname]))

I left the Is Persisted value set to no.

SQL 2005 will go through the existing table data and add the new field value when you save the changes, and new row inserts will have the field value automatically inserted.

In your query code, you can access the difference between the epoch dates like this:

USE tablename

/** gets the number of seconds and divides by 60 for minutes. rounds to nearest whole number. Values less than 1 minute show up as zero. **/

SELECT (dbo.epoch_diff(epoch_start, epoch_end)/60) AS diff
FROM tablename