SMS Reporting

Goto the SMS Home Page
This page details problems and issues relating to Reporting.

Altiris (Computing Edge) Reports for SMS Hardware

We've recently bought into this product and are now going into the process of building our own reports. To do so requires creation of native SQL queries (vs. WQL that's used in the SMS MMC Query tool!) that run against the SMS database to extract the required report results.

I'm trying to sort out the necessary SQL syntax to extract summary hardware information for either a given system (Prompt for system name at report time), or all systems at  a given Site (again, prompt for Site code at report time).

Don't be deceived in thinking that once you've created a query you can just click 'Show Query Language' at the bottom of the Query properties tab and simply cut and paste that to a ISQL window. The query language contained in that dialog is WQL not SQL and only works against the WBEM/WMI representation of your SMS data that the MMC uses. This is not the 'real' raw SMS data that you get if you access your database with standard SQL tools like iSQL.

What I ended up doing was this:

  1. Get a working query in SMS MMC that returns the data desired
  2. Fire up SQL Profiler to monitor SMS Provider actions against the SMS Database
  3. Create a new 'Trace' in Profiler
  4. Start trace
  5. Execute MMC query
  6. Stop trace & copy resulting T-SQL's SELECT statement; and
  7. paste into the Selection Criteria section of the CE tool.

The only cleanup I needed to do was take any T-SQL double-quoted values AND change them to single-quotes for the query to function correctly. There's a little added residue in the Profiler's query text but it's readily obvious what's junk.

Another option is to look in the SMS Provider log after executing a query in the MMC which will show the actual syntax used to query the SMS database ('SMSProv.log' in your site server's 'SMS\Logs' directory.)
 

Can't re-install Crystal Reports as it says it's already installed

I uninstalled Crystal Reports from the Add/Remove Programs GUI. I am now attempting to re-install Crystal Reports from the SMS 2.0 SP2 CD. When I run Setup, SMS thinks Crystal Reports is still on the system. What do I have to delete from the system/registry to get Crystal Reports installed again?

In the event of a failed setup or corruption of an existing installation it may be desirable to reinstall Crystal Reports for SMS without reinstalling the SMS site. This can be done by carefully following the steps described below. These procedures contain inherent risks that may require the site to be reinstalled anyway. Proceed with caution.

  1. Stop 'Crystal Info Agent', 'Crystal Info Aps', and 'Crystal Info Sentinal' Services from 'Control Panel | Services'
     
  2. Delete the 'CInfo' directory and all it's subdirectories and files contained therein. The 'CInfo' directory is located under the '\SMS\' directory.
     
  3. Remove the following registry keys:
    HKLM\System\CurrentControlSet\Services\Crystal Info Agent
    HKLM\System\CurrentControlSet\Services\Crystal Info Aps
    HKLM\System\CurrentControlSet\Services\Crystal Info Sentinel
    HKLM\Software\Seagate\Crystal Info
    HKLM\Software\Seagate\Crystal MMC SnapIn
    HKLM\Software\Seagate\Seagate Crystal Info for SMS
     
  4. Make a backup copy of 'SMS\Install.map'

    WARNING: Editing the '    Install.map' incorrectly can cause serious problems that may require you to reinstall your SMS site. Make sure you have a good backup before proceeding.
     
  5. Edit the original 'Install.map', deleting the following line:
    INSTALLEDUNIT <CRYSTAL INFO> <17>
     
  6. Close and save 'Install.map'
     
  7. Reboot server.
     
  8. Run SMS 2.0 Setup
     
  9. Select to Modify existing installation
     
  10. Select to install Crystal Reports

'Cannot open SQL server' message when running a Crystal Reports report

I am trying to run a report using Crystal Reports. Every time I try to run a report it fails with a 'Cannot open SQL server' message. I am using Integrated security. I have connected to SQL with the SQL Query Analyzer. I can connect and all rights seems to be as they should be. Info Agent, Info Sentinel and Info APS services are running. What am I missing ?

Some possible ways to work around this.

  1. Stop/Restart Info Aps.
     
  2. Try adding the 'Act as part of the Operating Systems' advanced user right to your SMS Admin account. This MUST be set - not an option, and pertains to the ID/ password used in the report's 'Logon Using' value that's pulling the data for the report.
     
  3. Try adding Domainname\SMSadmin in the logon using path. Be sure this ID (same as used in Step 2 above) ALSO has NTFS [FULL] permissions to the '..\SMS\CINFO' tree (and subordinates) And also note that the references here to the 'SMS Admins' group is the ACTUAL name of the group on the reporting server (a resource server in my case, therefore a local group), so the ID/ password from Step 2 needs to be in here.

Currently I've gotten my system to run SOME of the reports properly, and BLOW UP on others ('Abnormal Program Termination in ..\Cinfo\crw32.exe') - typically the bigger ones where there's more work being done. I'm now beginning to think my 512 MB RAM on the dedicated reporting (SMS Primary) server is not enough.

If all else fails, you can attempt to Modify an existing report (<right click report | modify>). Once you do this the template will come up. Choose to Log On to your SQL Server <wbem obdc> and point Crystal to your SQL box. Once you get logged on you can simply refresh the report data and Crystal will do its thing - annoying, but it works. Or you could use Queryextract.xls from the BORK 4.5 or Support Tools from the SMS CD and import your existing queries into Excel.
 

Crystal Info Error 'Unable to connect to APS'

I've been unable to run Crystal Reports since upgrading to Windows 2000 even after installing SMS SP2.  I have removed SMS and re-installed it on Windows 2000 and now I'm getting the following errors:

Primary Server:

Crystal Info Error
Unable to connect to APS.

Remote Admin Workstations:

Snap-in failed to initialize.
Name: Crystal Info Snapin
CLSID: {CC5D3540-3A97-11D1-864C-00A0C932F8E8}

To resolve the 'Unable to connect to APS' error make sure you're using the correct ODBC System DSN path.

To resolve the 'Snap-in failed to initialise' error copy 'cisnapin.dll' to the right directory.
 

Crystal Reports Hangs with a Grey Screen

Contributed By: Cliff Hobbs [MVP SMS]
 If you are experiencing a grey screen after running reports successfully, this SMS FAQ information will get you back on track...  [Go to article]
 

Crystal Reports says it shouldn't be installed on Sites with more than 500 clients

I was asked to install the Crystal Reports feature on the Site Server.  When I go to install this, it says that this is a sample snap-in and should not be installed if your Site is larger than 500 clients.  Does anybody know what the issues with this are?  Is there a problem?

This isn't a problem as such, merely a disclaimer stating that Sites with over 500 clients may experience issues with running Crystal Info. You can run it on a Site with any number of clients however the performance of the snap-in decreases substantially as the number of clients increase. Microsoft recommends installing Crystal Reports on it's own server, on a Site level above your Primary site.

It seems as though the version of Crystal Reports that ships with SMS isn't the most usable tool and you'd be better off either using the 'QueryExtract.xls' (or it's Access equivalent) when you install the Support Tools ('SUPPORT.EXE') from the SMS CD; or use the full-blown version of Crystal Reports.
 

How do I query the SMS database via WBEM in VBScript?

Contributed By: Cliff Hobbs [MVP SMS]
There isn't a great deal information currently available on this. The following is an extract from an upcoming Rod Trent book. It lists the machine name, user name, and software product from the SMS database.  You'll have to mess with this a bit by entering your SMS server name and Site code, and inserting your own WQL query.  But, this will get you started.  The main thing is connecting to the Site server and getting the WMI object.

 You'll also want to use the cscript compiler when running the VBS file.  Otherwise, you'll get a WSH dialog window for each output.  The information can also be piped to a text file.

'The following line connects to the SMS Server through the WMI layer.

'For SERVER put in your SMS Server name.

'For XXX put in the site code for that server

winmgmt1 = “winmgmts:{impersonationLevel=impersonate}!//SERVER\root\sms\site_XXX"

'The following section echoes the connection then gets the object.

WScript.ECho winmgmt1

Set SystemSet = GetObject( winmgmt1 )

'Here is the query to list Machine Name, User Name, and Product Name.

strQuery = "select distinct SMS_G_System_COMPUTER_SYSTEM.Name, SMS_G_System_COMPUTER_SYSTEM.UserName, SMS_G_System_SoftwareProduct.ProductName from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SoftwareProduct on SMS_G_System_SoftwareProduct.ResourceID = SMS_R_System.ResourceId order by SMS_G_System_COMPUTER_SYSTEM.Name, SMS_G_System_SoftwareProduct.ProductName"

'The following section echoes all the instances found by the query.

Set objEnumerator = SystemSet.ExecQuery(strQuery)

for each instance in objEnumerator

WScript.Echo "-> " & instance.SMS_G_SYSTEM_COMPUTER_SYSTEM.Name & " " & instance.SMS_G_System_COMPUTER_SYSTEM.UserName & " " & instance.SMS_G_System_SoftwareProduct.ProductName

Next
 

Linking to SMS Tables through Access results in only one row of data being returned

We have encountered a replicatable error in that  when we query the SMS database through the 'SMSExtract.xls' utility, all is fine. But if we try to link to the tables through Access, we are only returned one row of data for any table we try to look at. Has anyone seen this before? The Excel access method uses WMI and Access uses WBEM (correct me if I'm wrong). We thought it might have been a problem with our SOE build, but it exists on a vanilla build of a workstation as well.

There is a hotfix available to from PSS to resolve this problem as detailed in 267598 'WMI ODBC Adapter Not Able to Process DATETIME Queries'.

The hotfix takes seconds to run and needs to be run on both the workstation and the server. It also requires re-linking the external tables into the Access database.
 

Problem Communicating with APS When Compiling a Report

Contributed By: Cliff Hobbs [MVP SMS]
 If you're having problems communicating with APS or you get an error saying the report file can't be opened here's a tip to help you out ...  [Go to article]
 

Tips for Using Crystal Reports

Contributed By: Cliff Hobbs [MVP SMS]
 Here are some tips for using Crystal Reports ...  [Go to article]
 

© FAQShop.com 2003 - 2008

Goto the SMS Home Page

 Email the Author