How do I troubleshoot the error message 'No Data For Report' when running AppManager Reports? (NETIQKB37572)

How do I troubleshoot the error message 'No Data For Report' when running AppManager Report Knowledge Scripts?
Running any ReportAM_* Knowledge Script returns event error message: 'No Data For Report'.
Running any Report_* Knowledge Script returns event error message: 'No Data For Report'.

Please use the instructions below to troubleshoot this error message.

Warning: This SQL Query directly accesses your database and updates/deletes data. If the query is performed incorrectly, it can cause irreparable harm to the database and may result in loss of data. If you are unfamiliar with SQL or how to run a SQL Query, please consult with your SQL Database Administrator.

To verify whether or not there is data available in the ArchiveData table:

1. To get the Data Source ID, you need to look in the Operator Console under the 'Graph Data' pane.  Find the data stream you are trying to report against and note its Data Source ID number.
2. To ensure there is data in the ArchiveData table, run the query in Step 3 using the Microsoft SQL Query Analyzer for the selected Data Source ID.
3. Select the 'QDB' database from the drop-down menu at the top.  In the query window, type and execute the following query, making sure to replace the ??? with the Data Source ID found in Step 1:

SELECT * FROM ArchiveData WHERE DataID=???

1. After executing the query, there should be rows of data returned.  You have just verified data exists in the ArchiveData table for reporting.  However, the results from the above query do not return the information in an easy-to-understand format.
2. The following query will give more detailed information to help verify that the data exists by showing the date and time each point was collected.  Please specify the Data Source ID value where the ??? are indicated:

declare @t int
exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'ActiveTimeBias',
@param = @t OUT
select @t = @t * 60 /* convert to seconds */
select object.name as MachineName, archivedata.dataid as ArchiveDataID, archivedataheader.jobid as JobNumber, dateadd(second, archivedata.time-(@t), '1/1/70 0:0:0') as Time
from archivedata, archivedataheader, object (nolock)
where archivedataheader.dataid = archivedata.dataid
and archivedataheader.mcmachineobjid = object.objid
and archivedata.dataid = ???
order by time

After verifying that data exists in the ArchiveData table for the specified date-time range selected in the report, if the 'No Data For Report' error persists:

Warning:  The following is for more advanced SQL Administrators.  Please consult with your SQL Database Administrator before performing this task. 

To continue troubleshooting, the report's stored procedure must be 'captured' and then executed in the SQL Query Analyzer.  The Microsoft SQL Server Profiler program must be utilized to capture the report's stored procedure query. 

There are two sets of steps to follow to use SQL Profiler:  (A)  Using a predefined trace Template or (B) Manually creating the Trace

(A)  Using a predefined trace Template

1. The following Microsoft SQL Profiler Template will be able to capture the exact stored procedure used by a report:  ftp://tsfix:snapshot@ftp.netiq.com/tsfix/patches/NETIQKB37572/AMReport_Profiler_Trace.tdf
2. To use the AMReport Profiler Trace template, first launch Microsoft SQL Profiler from Start | Programs | Microsoft SQL Server | Profiler.  
3. Next, start a new trace [File | New | Trace] from the menu.  You will be prompted to connect to the database. 
4. Connect (login) to the AppManager QDB (Repository) as 'sa' or Administrator. 
5. Next, click on the 'General' tab.
6. Select the AMReport Profile Trace.tdf template by clicking the folder Browse button for the 'Template File Name'. 
7. Finally, click the 'Run' button to start the Profiler Trace, and then start the Report Job in the AppManager Operator Console.
8. After the AppManager Report job has run and completed, the exact stored procedure that the report used should show in the trace (starts with exec NetIQrp..).  Copy the report's entire stored procedure, paste it into SQL Query Analyzer, and run it against the AppManager Repository (see steps 9 and 10 below to continue).

(B) Manually creating the Trace

1. Launch the SQL Profiler on the SQL Server where the AppManager QDB database resides.
2. Click the 'Run' button to start the capture. 
3. Go back to the AppManager Operator Console and run the Report knowledge script that is generating the error message.  After the error message is produced, go back to the SQL Profiler and stop the capture. 
4. To locate the correct report stored procedure, in the SQL Profiler trace:
   
   
   • Select Edit/Find or Press <CTRL+F>
   • In the Find dialog type in the ?Search value? (Important! This is case sensitive):

   exec NetIQrp

   • Select TextData from the ?Data column? pull down menu
   • Select TextData from the ?Data column? pull down menu
   • Hit the Find Next button?
     
     
5. You may notice the following query appears with the same beginning as ?exec NetIQrp?, but it is not the actual report's stored procedure:
   
   declare @P1 varchar(128)
   set @P1='STIDES'
   declare @P2 varchar(128)
   set @P2='QDB'
   exec NetIQrpBrowserQDBSelect '4699', @P1 output, @P2 output
   select @P1, @P2
   

6. Keep hitting the 'Find Next' button? or press the F3 key to continue searching. 
   
   The following are actual valid sample report stored procedure queries that can be found in the SQL Profiler:
   
   exec NetIQrpPullData 'NT_CpuLoaded', '', '0', '', '237', '2004-1-1 00:00:00', '2004-2-6 13:44:21', 3
   exec NetIQrpPullData 'NT_CpuLoaded', '%LIKEPROCESSOR Utilization%', '0', '', '237', '2004-1-1 00:0.
   0:00', '2004-2-6 14:20:43', 2
   exec NetIQrpDataSummary 'NT_LogicalDiskSpace', '%LIKE%USED^^%', '0', '', '', '2004-1-1 00:00:00', '2004-2-6 14:23:05', 2, '1234567', 0, 1440
   exec NetIQrpPullData 'NT_MemUtil', '%', '0', '', '', '2004-1-1 00:00:00', '2004-2-6 14:31:26', 1

7. Once the stored procedure for the specific report is found, copy the entire line from the TextData column and paste it into the SQL Query Analyzer.
8. Execute this query against the QDB database (see steps 9 and 10 below to continue).
9. If the query results return an empty Grid tab with only the column headers listed, this proves that there is no data being returned, based on the parameters specified in the report knowledge script. 
10. If an error is returned, please contact NetIQ Technical Support with the error message.

All Report Knowledge Scripts pull data from the AppManager QDB table called 'ArchiveData'.  When a report is run on the Report Agent, a query is built which is then executes as a Stored Procedure in the QDB.  This Stored Procedure is executed against the QDB via NetIQOLE.  The error message "No Data For Report" indicates that there is no data in the ArchiveData table for which the parameter settings in the Report Knowledge Script were set.  Most commonly, it is the Date-Time range settings that are the root cause of this error message.  Occasionally, there might be missing data.  Therefore, the Report's Stored Procedure is returning a correct error message.

Formerly known as NETIQKB37572