CABI/BOXI for ITCM: Error-- "No data to Retrieve in Query 1"

Products

• CA Automation Suite for Data Centers - Configuration Automation
• CA Client Automation - Asset Management
• CA Client Automation - IT Client Manager
• CA Client Automation
• CA Client Automation - Remote Control
• CA Client Automation - Asset Intelligence
• CA Client Automation - Desktop Migration Manager
• CA Client Automation - Patch Manager

Issue/Introduction

Problem:

When a user with permissions to access CABI/BOXI attempts to run a report for Client Automation (ITCM), they receive an error, "No data to retrieve in Query 1":

<Please see attached file for image>

Environment:

CA Business Intelligence (CABI) -- All versions

Cause:

When running reports from CABI/BOXI against an ITCM Domain Manger or Enterprise manager, the user you are running the report as in BOXI must be a defined user in ITCM Security Profiles. The user must have logged into the DSM Explorer at least once before being able to run a report against an ITCM MDB.

The reports will query ITCM's security related tables to verify if the user should have access to the information requested in the Report.

If the user does not have appropriate access in ITCM or is not defined in ITCM the user will get an error while running the report that says something along the lines of "No data to Retrieve in Query 1"

Resolution:

Setting up the user in ITCM Security Profiles

To set the appropriate permissions for a user in ITCM Security Profiles follow the steps below:

1. Log into the DSM Explorer with a user that has Full Control over Security and on the top tool bar select "Security->Security Profiles"
   
   

   <Please see attached file for image>

   
   
   
2. Select the user from the list and select "Class Permissions" or Select "Add" to add a new user and this will bring you to the "Class Permissions" after adding the user
   
   

   <Please see attached file for image>

   
   
   
3. Select at least the minimum permissions needed in the user's Security Profile as listed below:
   
   
   • On the "Asset Group" object "Read (VR)" Class Permissions
     
     
   • On the "Computer" object "Read (VR)" Class Permissions
     
     
   • On the "Domain" object "Read (VR)" Class Permissions
     
     

     <Please see attached file for image>

     
     
     
4. Have the user login to the DSM Explorer with the account you just added.
   
   
5. In BOXI's Central Management Console, under "Users and Groups" you will also need to add the user to one of the following groups:
   
   
   • ITCM Administrators
     
     
   • ITCM End Users
     
     
   • ITCM Publishers
     
     

If the user does not have access to one of these groups, they will not even be able to see the Report Folders in BOXI

<Please see attached file for image>

Verifying the user you are logging into BOXI with will be able to run Reports

1. When installing the ITCM Universe for BOXI you are prompted for the Domain/Active Directory Name and Type(Either WINNT or LDAP).
   
   The LDAP/WINNT domain you choose here must match exactly what is in the ITCM MDB in the 'URI' column of the 'ca_discovered_user' table.
   
   If you are unsure of whether to choose WINNT or LDAP and what Domain/Active directory name to use during the install of the ITCM Univere, run the query below and see what Domain/Active directory is being used in your ITCM MDB.
   
   select uri from ca_discovered_user
   
   

   <Please see attached file for image>

   
   
   
2. If you chose LDAP as the security type, then you will not be able to run reports as the BOXI "Administrator" account, as this account would not exist in LDAP and would not match up with an account in ITCM.
   
   
3. If you chose WINNT as the domain type and you used the hostname of the ITCM Domain Manager as the WINNT "Domain" then you will be able to use the BOXI Enterprise "Administrator" account to run reports. This is because it will pass the Domain name as the local machine name, and pass the user "Administrator, and by default ITCM puts the local Administrator group into the Security Profiles.
   
   
4. You can only use one Domain/Active Directory per instance of BOXI. Meaning you cannot use two different LDAP's, or LDAP and Local WINNT security to run reports. It is only one or the other.
   
   
5. Below are two queries, one for WINNT and one for LDAP, you can use to verify that your user account will be able to run reports if they have the permissions needed in ITCM.

These queries will only return results once the User logs into the DSM Explorer at least once:

Example A

For WINNT use the following query:

SELECT uri FROM CA_DISCOVERED_USER
WHERE uri like ('winnt://'+'DOMAIN_NAME'+'/'+ 'USERNAME')

If you are using WINNT, then you can Substitute 'DOMAIN_NAME' with the WINNT Domain name you selected during the install of the ITCM Reports, and where it states 'USERNAME' substitute the username you will login with in BOXI.

For example if your Domain/Active Directory name is "DOMAIN1" and your user account is "USER1" then you can run the following query to see if your account matches up in ITCM to allow you to run reports from BOXI.

SELECT uri FROM CA_DISCOVERED_USER
WHERE uri like ('winnt://'+'DOMAIN1'+'/'+ 'USER1')

This query should yield a result that looks like the example below if the user is found:

winnt://domain1/user1

Example B

For LDAP users use the following query:

You can check your Domain and user account to see if it will match what is in your ITCM mdb by specifying your username where it states 'USERNAME' and your Domain where it says 'DOMAIN.COM'

(SELECT uri FROM CA_DISCOVERED_USER WHERE ((case when substring (uri, 1,4) ='ldap' then substring(uri , ((CHARINDEX('/cn=',uri))+4), ((CHARINDEX(',',uri)) - ((CHARINDEX('/cn=',uri))+4) ) ) else '' end )= 'USERNAME'and (case when substring (uri, 1,4) ='ldap' then (substring(uri ,
((CHARINDEX('://',uri))+3), ((CHARINDEX('.com',uri))-4) )) end in ('DOMAIN.COM') ) )and domain_uuid = (select set_val_uuid from ca_settings where set_id = 1) )

For example if your username is "USER1" and your LDAP Domain is "DOMAIN1.com" then they SQL you can test with would be

(SELECT uri FROM CA_DISCOVERED_USER WHERE ((case when substring (uri, 1,4) ='ldap' then substring(uri , ((CHARINDEX('/cn=',uri))+4), ((CHARINDEX(',',uri)) - ((CHARINDEX('/cn=',uri))+4) ) ) else '' end )= 'USER1'and (case when substring (uri, 1,4) ='ldap' then (substring(uri ,
((CHARINDEX('://',uri))+3), ((CHARINDEX('.com',uri))-4) )) end in ('DOMAIN1.COM') ) )and domain_uuid = (select set_val_uuid from ca_settings where set_id = 1) )

This query should return a result that looks like the output below if the user is found:

ldap://domain1.com/cn=user1,ou=users,ou=north america,dc=domain1,dc=com

Additional Information:

In the CABI/BOXI universe released with Client Automation (ITCM) r12.9 and r14.0, there's an option during the CABI Universe deployment, to not integrate with ITCM security profiles.  On the screen with "Security Provider" selection, there is now a third option, which will allow you to rely only on CABI authentication-- i.e. if the user has proper permissions to access CABI and run the report, the ITCM universe will not check if that very user also has security permissions in ITCM to view the data relevant to the report results.  This also alleviates the requirement for users running reports to access and login to DSM Explorer at least once.

Environment

Release: UASIT.99000-12.9-Asset Intelligence
Component: