Techie Pie

Came across an interesting problem in Altiris while working with Patch Management, and seems it is one of those undocumented tidbits.

I basically found myself in the situation where, post-install/repair, Patch Management wasn’t detecting that Patch Management for Windows Report Pack was installed when the application indicated that it was. This is a fairly common error, and anybody who has had to uninstall/reinstall PM via the Solution Center has probably had to deal with it at some point.

Patch Management Core has detected that the following component is not installed: Patch Management for Windows Report Pack This component is required by the page you tried to view and must be installed before you can continue.

But what made this (un)fun was that the standard solution to this problem - repair the NS install and then the SP - didn’t work. In fact, none of the solutions in the KB worked.

Now my stupid moment was in not checking the log file sooner; I assumed it was the standard problem that I had worked with before. However, I was wrong. There were two key items in the log file:

[CDATA[Transaction being rolled back automatically during dispose. To disable this warning rollback your transaction explicitly! Dispose location:

and

[CDATA[Failed to install product. [This index operation requires 2048 KB of memory per DOP. The total requirement of 8192 KB for DOP of 4 is greater than the sp_configure value of 1024 KB set for the advanced server configuration option "index create memory (KB)". Increase this setting or reduce DOP and rerun the query.
The statement has been terminated.] ( Unhandled exception. Type=System.Data.SqlClient.SqlException Msg=This index operation requires 2048 KB of memory per DOP. The total requirement of 8192 KB for DOP of 4 is greater than the sp_configure value of 1024 KB set for the advanced server configuration option “index create memory (KB)”. Increase this setting or reduce DOP and rerun the query. The statement has been terminated.

The net effect of this is that the option is not actually installed by the Solution Center, even though the application indicates that it is installed.

Fortunately the solution was easy: In SQL 2005, modify the server properties so that Index Memory Size is configured to use dynamic values. I rebooted the Altiris server as a precaution, then tried things again. And it worked!

It’s been a very long time since I had anything to contribute to the site.

This is partly due to laziness, a common trait among coders of all colors.

Second, I started a new job over the summer and I have been busier than a one-legged man in an ass-kicking contest.

Third, my boyfriend of two years moved halfway across the country so we could stay together.

All told, lots of changes.  But a new year is here, and new goals too.  So I’ll get back to documenting all the little nuggets I encounter!

On several occasions I have been asked to move SQL 2005 system databases at some point in the server’s lifecycle. Moving master, msdb, model and tempdb isn’t difficult, but it does require some attention to detail (and server downtime).

Fortunately, the SQL 2005 Books Online include instructions on how to move system databases in SQL 2005. What you might want to know, however, is that when following these instructions there is an error under the section titled Moving the Master and Resource Databases. Specifically, some details are missing at lines 14-16. If you follow these instructions to the letter (like I do), you need make the following changes (the bolded lines are my additions):

14. MODIFY FILE (NAME=log, FILENAME-’new_path_of_master\mssqlsystemresource.ldf’) ;

GO

14a. Stop SQL

15. Move the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the new location.

15a. Restart SQL in single user mode

16. Set the Resource database to read-only by running the following statement:

ALTER DATABASE mssqlsystemresource SET READ_ONLY ;

The change is necessary because in order to move mssqlsystemresource.mdf and mssqlsystemresource.ldf the SQL services need be stopped, but in order to set Resource to read only, the service needs to be in single user mode.

It’s a simple modification that keeps following the Microsoft instructions clean and error-free.

Today I found myself in the situation where I wanted to add some maintenance plans under SQL 2005, but got an error that looked like “Cannot find folder \Maintenance.”

The short version is that, somehow, the entry for the Maintenance folder in MSBD got nuked.  After some digging I found a forum thread with instructions on how to fix the problem.

All you need to do is run two SQL queries against MSDB.

1. Verify that the entry is lost:

exec sp_executesql N’exec [msdb].[dbo].[sp_dts_getfolder] @P1, @P2′,N’@P1 nvarchar(17),@P2 uniqueidentifier’,N’Maintenance Plans’,'00000000-0000-0000-0000-000000000000′

2. Recreate the entry:

exec sp_dts_addfolder ‘00000000-0000-0000-0000-000000000000′,’Maintenance Plans’,'08AA12D5-8F98-4DAB-A4FC-980B150A5DC8′

Many thanks to the original poster at the link above for providing the solution.

Here’s an easy way to force computers to report inventory to Notification Server via a job on Deployment Server:

1. Copy the file AeXWebInvPkg.exe to the client;

2. Copy a batch file to the client to run the executible:

@echo off
echo Altiris is collecting inventory on this computer.
echo Please do not close this window. It will close on it’s own in a moment.
c:\aexwebinvpkg.exe

3. Run a vb script to launch the batch file:

Set sh = CreateObject(”WScript.Shell”)
Set fso = CreateObject(”Scripting.FileSystemObject”)

sh.run “c:\inventory_ns.bat”

Set sh = Nothing
Set fso = Nothing

Since we have a fairly aggressive purge policy in place on NS, I use this script to get computers back quickly after a campus has been offline for the summer.

Just a minor note regarding my experience in setting up SQL 2005 Reporting Services for the first time…

Overall, a very smooth process. I specifically followed the instructions in Chapter 2 of Microsoft SQL Server 2005 Reporting Services (Step by Step), ISBN 978-0-7356-2250-0. There were only a few minor caveats:

1. I installed Reporting Services on an existing SQL 2005 SP2 installation, which meant that I had to go back and rerun SP2 for just that portion of the install before I could configure Database Setup under the Configure Report Server interface.

2. Even after doing this, I got a failure when I chose the option to upgrade the ReportServer DB. The simple fix was to click Apply yet again, and then everything finished like it was supposed to. Guess the DB upgrade had to be in place before the rest of the configuration scripts would run.

And that’s about it. I’ll blog more details about actually using Reporting Services as I get into it in more detail over the coming weeks.

If you have Altiris, at some point you have no doubt wanted to create a collection based on a program that may be installed on the desktop.  For me, this program was the administrative nightmare known as Deep Freeze, however you can modify the query to reflect whatever program you want to hunt for.

It is important to note that the SQL to build the collection for this is NOT the same as the SQL used to build a report for this, and if you start with a report to find the software and then try to create the collection, you won’t get all the data.  To create the collection statement, I poked around at a SQL level first until I got the desired result set.  At that point I was able to use the query to create the computer collection.

The bold items represent what you would swap out for your own query.

select Guid from vResource where Guid in (select ResourceGuid as Guid from (SELECT DISTINCT
i.[Name],
csw.KnownAs [Product Name],
csw.ProductVersion [Version],
csw.Manufacturer,
i.[Guid] [ResourceGuid]
FROM dbo.vComputer i
JOIN dbo.Inv_AeX_SW_Audit_Software_spt t1
ON t1.[_ResourceGuid] = i.Guid
JOIN dbo.Cmn_SW_Common csw
ON csw.[_KeyHash] = t1.[_KeyHash]
JOIN dbo.Inv_AeX_AC_Identification d
ON d.[_ResourceGuid] = i.Guid
JOIN dbo.CollectionMembership cm
ON cm.ResourceGuid = d.[_ResourceGuid]
JOIN dbo.vCollection it
ON it.Guid = cm.CollectionGuid
/* WHERE csw.KnownAs = ‘Deep Freeze‘ OR csw.KnownAs = ‘Deep Freeze 5‘ OR csw.KnownAs = ‘Deep Freeze 6‘ */
WHERE csw.KnownAs LIKE ‘Deep Freeze%‘
AND d.[System Type] LIKE ‘Win%’
AND i.[Name] LIKE ‘%’ ) xxx)

I can’t take credit for the original write up of this snippet, but sadly it has also been so long that I can’t recall exactly where I got it (I know it was on the web).  Basically what this code will do, assuming you accepted some type of input earlier in your script, is rename an XP computer (whether on the domain or not) and force a reboot.

I’ve only worked with it on XP Pro platforms, so YMMV.

# Connect to Computer
$objWMILocator = Win32::OLE->new(’WbemScripting.SWbemLocator’);
$objWMILocator->Security_->{AuthenticationLevel} = 6;
$objWMIComp = $objWMILocator->ConnectServer($strComputer, ‘root\\cimv2′);
my $objWMICompSys = $objWMIComp->Get(’Win32_ComputerSystem.Name=\” . $strComputer . ‘\”);

# Rename Computer
$intRC = $objWMICompSys->Rename($strNewComputer, $strLocalPasswd, $strLocalUser);
if ($intRC != 0) {
print ‘Rename failed with error: ‘ . $intRC, “\n”;
}
else {
print “Successfully renamed $strComputer to $strNewComputer\n”;
}

print “Rebooting system…\n”;
$colOS = $objWMIComp->InstancesOf(’Win32_OperatingSystem’);
foreach my $objOS (in $colOS) {
Win32::InitiateSystemShutdown( ”, “\nAction Complete.\n\nSystem will now Reboot\!”, 10, 0, 1 );
}

Here’s a super simple way to generate a random number in your perl scripts:

srand (time ^ ($$ + ($$ << 15)));
my $random_number = int(rand(30000)) + 55000 ;
print “random = $random_number\n” ;

You can play with the numeric values in line 2 to get the range of numbers you want.

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;