Custom REST API - Add/Remove SQL instance

Hi,

I have created a custom rest api to add a SQL instance to Foglight. I am doing a post call and within the api code are the internal function function calls that work on the installer. I am hitting a few hurdles on the way and seek some help if possible

1) The API code is able to register and create the agents both the DB_SQL_SERVER and the associated spi but then errors out with message - "VERBOSE [http-exec-51] script.system:dbwc_mssql_installer.monitorInstances - com.quest.qsi.fason.framework.installer.exceptions.InstallerServiceException: Could not create agent DB_SQL_Server on FglAM xxx: Not authorized to manage agents". Soon after it unregisters those agents.

2) If I try to enable OS monitoring, my API code fails with error - no credentials found for host xxxx. I think I am unable to populate credentialIDs for DBSS_Installer_OS_Credential topology object correctly in the code.

<type name='DBSS_Installer_OS_Credential' extends='DataObject'>
<annotation name='Keywords' value='DB_SQL_Server'/>
<property name='credentialIDs' type='String' is-many='true' is-containment='false'/>
<property name='lockboxPassword' type='String' is-many='false' is-containment='false'/>
<property name='osUserName' type='String' is-many='false' is-containment='false'/>
<property name='osPassword' type='String' is-many='false' is-containment='false'/>
<property name='authenticationMode' type='DBSS_Installer_DB_Authentication_Mode' is-many='false' is-containment='false'/>
</type>

Thanks

  • Hi,

    On further investigation the error seems to emanate from: AsyncOperationStatusSL<Integer> operationStatus = this.ramService.initializeAgentAsync(remoteClient.getId(), agentType, agentName) - in com.quest.qsi.fason.framework.installer.impl.InstallerAgentServiceImpl class

    Does the RemoteAgentManagementService i.e. " private RemoteAgentManagementService ramService = ServiceLocatorFactory.getLocator().getRemoteAgentManagementService();" defined in InstallerAgentServiceImpl has some restrictions in place e.g. it can't be called via api and so on?

    2021-02-22 14:12:28.343 VERBOSE [http-exec-6] STDOUT - INFO - Creating xxx agent of type DB_SQL_Server on FglAM twi201023124519.estestau.wbctestau.westpac.com.au.
    2021-02-22 14:12:28.343 INFO [http-exec-6] com.quest.qsi.fason.framework.installer.impl.InstallerAgentServiceImpl - Creating xxx agent of type DB_SQL_Server on FglAM yyy.
    2021-02-22 14:12:28.343 VERBOSE [http-exec-6] script.system:dbwc_mssql_installer.monitorInstances - com.quest.qsi.fason.framework.installer.exceptions.InstallerServiceException: Could not create agent DB_SQL_Server on FglAM yyy: Not authorized to manage agents

  • Not sure how you called the related API. The message "Not authorized to manage agents" shows that the current user didn't have sufficient privileges to perform related action while foglight Administrator is required when managing agents through the API.

    Regards

    Jemy

  • Hi Jemy,

    I am using my credentials to execute the API which has both Administrator and API roles. Any other ideas?
    The installer works fine when a SQL instance is added via GUI or fglcmd.

    Thanks

  • Per the given message, I would suggest to confirm again whether you generated the token from a user which has administrator and api roles. If yes, then try  use Core API to create agent first, here InstallerAgentServiceImpl is not from Core but DB cartridge. Or you can consider using Core API to create an IC cartridge. If it's OK to do so, then it may be something to do with the API from DB cartridge. Or you can share your script if you confirm that the privileges are OK.

    Regards

    Jemy

  • Hi Jemy,

    The rights in the token seem to be okay

    "roles": [
                    "Operator",
                    "Dashboard User",
                    "General Access",
                    "Advanced Operator",
                    "Console User",
                    "Dashboard Designer",
                    "Cartridge Developer",
                    "Command Line Access",
                    "Security",
                    "Anybody",
                    "Foglight Database Alarm Manager",
                    "Administrator",
                    "API Access"
                ]
    Do we have any samples on creating an agent using the Core API?
    The script I am using is as follows -

    import javax.ws.rs.*;
    import javax.ws.rs.core.*;
    import javax.ws.rs.container.*;
    import javax.servlet.http.*;
    import com.quest.forge.rest.annotation.Secured;
    import com.quest.forge.rest.service.*;
    import com.quest.forge.rest.api.*;
    import com.quest.wcf.servicelayer.*;
    //import com.quest.qsi.fason.framework.utils.QSIAES;



    @Path("/instance")
    @Secured
    @Produces("application/json")
    class SQLHelper{

    @Context
    private ContainerRequestContext requestContext;

    @Context
    protected HttpServletRequest request;

    @POST
    @Path("add")
    public Response AddSQLInstance(@FormParam("hostName") String hostName, @FormParam("instanceName") String instanceName, @FormParam("port") String port, @FormParam("osUserName") String osUserName, @FormParam("dbUserName") String dbUserName, @FormParam("enableOS") String enableOS, @FormParam("enableSPI") String enableSPI, @FormParam("useSSL") String useSSL){
    def foglightServiceLocator = ServiceFactory.getInstance().getFoglightServiceLocator();
    def ts = foglightServiceLocator.getTopologyService();
    def helper = foglightServiceLocator.getExtensionContext().get(ForgeRestApiHelper.class);
    def host = null;
    //def host = ts.queryTopologyObjects(metric_query)
    def entity = null;
    //def decoder = new QSIAES();
    try
    {
    // 1) Create installer session object
    def session = ServiceRegistry.getEvaluationService().invokeFunction("system:dbwc_mssql_installer.createSessionDataObject",null,null,null);
    //2) List all AGMs
    def fglamContainer = ServiceRegistry.getEvaluationService().invokeFunction("system:dbwc_mssql_installer.listFglAMs",null,null,null);
    //3) Get optimal AGM
    def fglamName = fglamContainer.get("optimal_fglam").fglam_name;

    //4) Prepare initial discovery of FGLAMs
    //function call may take a long time to execute
    session = ServiceRegistry.getEvaluationService().invokeFunction("system:dbwc_mssql_installer.prepareInitialDiscovery", [fglamName , session], null, null );
    //disabled the option to used the default cred for DB and OS from the seesion
    session.set("osUseDefault",false);
    session.set("dbUseDefault",false);


    // set installer instance object
    def type = ts.getType("DBSS_Installer_Instance");
    def instance = ts.createAnonymousDataObject(type);
    session.get("instances").add(instance);
    instance.set("session", session);
    instance.set("isSelected", true); // marks the instance as selected
    instance.set("isDbMonitored", false);
    instance.set("osType", "WINDOWS");
    //instance.set("dbAgentName", fglamName);

    //set osCred
    type = ts.getType("DBSS_Installer_OS_Credential");
    def osCred = ts.createAnonymousDataObject(type);

    //set dbCred
    type = ts.getType("DBSS_Installer_DB_Credential");
    def dbCred = ts.createAnonymousDataObject(type);
    instance.set("dbCred", dbCred);

    // set os credentials
    if(osUserName?.trim())
    {
    osCred.set("osUserName", osUserName);
    def pwd = "xxx"; //encrypted pwd from QSIAES
    osCred.set("osPassword", pwd); //"OS Password" column of "silent_installer_input_template.csv"
    osCred.set("osCredType", "Windows_Custom_Account"); //"OS Credentials Type" column of "silent_installer_input_template.csv"
    instance.set("osCred", osCred);
    instance.set("osUseDefault", false); // for the silent installer there's an input for each instance

    List<String> credIDs = new ArrayList<String>();
    def credService = foglightServiceLocator.getCredentialManagementService();
    def lockboxList = credService.listLockboxes();
    def lockbox = lockboxList.find{it.getName().equals("DB-Agent Lockbox")}
    def credQuery = credService.createProtectedCredentialQuery();
    credQuery.setLockbox( lockbox );
    def credentialList = credService.listCredentials( credQuery );
    def cred = credentialList.find{it.getName().equalsIgnoreCase(osUserName)}
    credentialList.each{
    credIDs.add(it.getCredentialId() );
    }
    //credIDs.add(cred.getCredentialId() );
    osCred.set("credentialIDs", credIDs);
    osCred.set("lockboxPassword", null);
    }
    else
    {
    throw new Exception("osUserName parameter is mandatory");
    }


    if(dbUserName?.trim())
    {
    type = ts.getType("DBSS_Installer_DB_Authentication_Mode");
    def DbAuthenticationMode = ts.createAnonymousDataObject(type);
    DbAuthenticationMode.set("value","Windows_Custom_Account");

    dbCred.set("DbAuthenticationMode", DbAuthenticationMode);
    dbCred.set("user",dbUserName);
    def pwd = "xxx";
    dbCred.set("password",pwd);
    }
    else
    {
    throw new Exception("dbUserName parameter is mandatory");
    }



    if (hostName?.trim())
    {
    //osCred.set("credentialIDs",[""]);
    instance.set("hostName", hostName);
    dbCred.set("hostName", hostName);
    }
    else
    {
    throw new Exception("hostName parameter is mandatory");
    }

    if(instanceName?.trim())
    {
    instance.set("instanceName", instanceName);
    dbCred.set("instanceName", instanceName);
    }
    else
    {
    throw new Exception("instanceName parameter is mandatory");
    }


    def srvName = hostName + "\\" + instanceName;

    instance.set("serverName" , srvName);

    if(port?.trim())
    {
    dbCred.set("port", Integer.valueOf(port) );
    }
    else
    {
    throw new Exception("port parameter is mandatory");
    }

    def validValues = ["true","false"];

    if(!enableOS?.trim() || !validValues.contains(enableOS.toLowerCase()))
    {
    throw new Exception("enableOS parameter is mandatory and should be either true or false");
    }
    else
    {
    instance.set("skipOSMonitoring", true);
    //functionHelper.invokeFunction("system:dbwc_mssql_installer.validateDBAndOSCred", session , instance, osCredType, osUserName , osPassword ,lockboxName , lockboxPassword );
    def osCredType = osCred.get("osCredType");
    def osPassword = osCred.get("osPassword");
    ServiceRegistry.getEvaluationService().invokeFunction("system:dbwc_mssql_installer.validateDBAndOSCred", [session , instance, osCredType, osUserName , osPassword ,null , null], null, null );

    }


    if(!enableSPI?.trim() || !validValues.contains(enableSPI.toLowerCase()))
    {
    throw new Exception("enableSPI parameter is mandatory and should be either true or false");
    }
    else
    {

    if(Boolean.valueOf(enableSPI.toLowerCase()) == true)
    {
    // make sure that a PI repository was already installed and configured for this monitoring FglAM
    def spiFglAMName = session.get("spiRepositoryProfile")?.get("fglamName");
    if ((spiFglAMName != null) && (!spiFglAMName.isEmpty())) {
    // set spi
    type = ts.getType("DBSS_Installer_Instance_PI_Configuration");
    def spi = ts.createAnonymousDataObject(type);
    instance.set("spi", spi);
    spi.set("enableSPI", true);

    //entity = helper.toSerializableEntity(session, request, requestContext);
    //return Response.ok(entity).build();
    }
    else
    {
    def errMessage = String.format("Failed to mark instance %s as PI enabled since PI repository was not configured correctly", instance.get("serverName"));
    throw new Exception(errMessage);
    }
    }
    }
    //host = fglamContainer.get("optimal_fglam").fglam_name;

    def validuseSSLAttrs = ["mandatory", "off"];
    if(!useSSL?.trim() || !validuseSSLAttrs.contains(useSSL.toLowerCase()))
    {
    throw new Exception("useSSL parameter is mandatory and should be either Mandatory or Off");
    }
    else
    {
    dbCred.set("vcSSLConnection", useSSL);
    }

    //entity = helper.toSerializableEntity(instance, request, requestContext);
    def newInstance = session.instances.findAll{it.isSelected}
    if (newInstance != null && newInstance.size() > 0)
    {

    if (!ServiceRegistry.getEvaluationService().invokeFunction("system:dbwc_mssql_agent20installer.canAddNewAgents", [session, 1], null, null).equals("SUCCESS")) {
    //handleError("The FglAM reached its Agents limit");
    throw new Exception("The FglAM reached its Agents limit");
    }
    // 5) monitoring process
    ServiceRegistry.getEvaluationService().invokeFunction("system:dbwc_mssql_installer.activateMonitoring", [session], null, null);
    }
    entity = helper.toSerializableEntity(session, request, requestContext);
    }


    catch(Exception e)
    {
    //entity = "Error adding SQL instance to Foglight. Please investigate the logs";
    return Response.status(Response.Status.FORBIDDEN).entity(e.getMessage()).build();
    }
    return Response.ok(entity).build();
    }

    }

    class SQLAPIExtension implements ForgeRestApiExtension{
    @Override
    public Class getRestResourceClz(){
    return SQLHelper.class;
    }

    @Override
    public String getCartridgeName(){
    return "SQLHelperCartridge";
    }
    }

    def rrm = server.context('extensionContext').get(RestResourceManager.class);
    rrm.unregisterRestAPIExtensions("SQLHelperCartridge");
    rrm.registerRestAPIExtension(new SQLAPIExtension());
  • I still have no idea whether you generated the token on an account which has admin and api roles.With sufficient privileges, using the run script rest api should be able to create agent, please refer to the help doc of rest api about how to generate token to execute the run script api.

    I had a quick look at your code, it relies on DB cartridge too much. You may need to consult corresponding team(or support team) for further assistance. Here's an example about using Core API to create some IC agents. The code is to create some agents for the given fglam, just for your reference.

    rms=server.RemoteAgentManagementService;
    agentService=server.AgentService;
    configService=server.ConfigService;
    prefix="ICAgent_test_move"; //Agent name pattern
    agentType="WindowsAgent"
    eachHostAgentCount = 20;
    monitoringHosts=["localhost","localhost"]
    fglamName = 'myFglAM-1'
    def count = 1;
    def index = 0;
    (1..10).each {
        try {
            

            log.info("counr $count, index $index");
            agentName = prefix + it;
            while(!createAgent(agentName, index)){
                Thread.sleep(6000);
            }
            count++;
            if (count >= eachHostAgentCount) {
                if(index < monitoringHosts.size()){
                    index++;
                }
                count = 0;
            }        
        }catch (Exception e){
            log.info(e.message)
        }
    }

    def createAgent(agentName, index) {
        def fglam1 = findFglam(fglamName)
        try {
            if (fglam1 && !agentService.findByName(agentName)) {
                log.info('Create agent!')
                rms.initializeAgent(fglam1.id, agentType, agentName);
                log.info('Agent instances!')
                def agent = agentService.findByName(agentName)[0];
                log.info("Agent $agent!")
                def config = configService.getAgentInstancePrimaryAsp(agent.getAgentNamespace(), agent.getTypeId(), agent.getId());
                config.setValueByString('host', monitoringHosts[index]);
                config.setValueByString('hostNameOverride', agentName);
                config.setValueByString('collectSystemId', "false");
                configService.saveConfig(config);
            }
        }catch (Exception e) {
            log.info(e.message)
            return false;
        }
        if(fglam1){
            return true;
        }else{
            return false;
        }
    }

    return 'Done!'

    def findFglam(name){
        try{
        return rms.findAllRemoteClients().find {
            it.id.startsWith(name)
        }
        }catch(Exception e){
                return null;
        }
    }

    Regards

    Jemy

  • Thanks Jemy

    The generated token has both API and Admin access as can be identified by the role tag I posted above when I executed /security/login call (as below)

    "roles": [
                    "Operator",
                    "Dashboard User",
                    "General Access",
                    "Advanced Operator",
                    "Console User",
                    "Dashboard Designer",
                    "Cartridge Developer",
                    "Command Line Access",
                    "Security",
                    "Anybody",
                    "Foglight Database Alarm Manager",
                    "Administrator",
                    "API Access"
                ]
    I'll try using the CORE API.
    I can create both Windows and DB_SQL_SERVER agents isn't it using the CORE API?
    Using the DB cartridge code would have been easier for me as much of it is already written. I have already raised a SR with Quest support team seeking help on this issue, this was done a few day ago.
    Thanks
  • Thanks for the confirmation on the token. The API below are from Core while InstallerAgentServiceImpl is not from Core.

    rms=server.RemoteAgentManagementService;
    agentService=server.AgentService;
    configService=server.ConfigService;

  • Hi Jemy,

    I modified the code in the installer script to create the agent via CORE API. Still geeting the security exception. I have also updated my comments on the associated SR case.

    srvRAM = server["RemoteAgentManagementService"];

    srvRAM.initializeAgent(fglam1.id, DBSS_AGENT_TYPE, dbAgentName);

     

     

    instead of “InstallerServiceFactory.getInstance().getAdAgentService().createAgent”, I still get the security exception “Not authorized to manage agents”

     

    [http-exec-1] script.system:dbwc_mssql_installer.16

    Starting to monitor instance dwd210122163030.ESDEVAU.WBCDEVAU.WESTPAC.COM.AU\A005E02.

    Wed, 2021-02-24 16:43:47.761

    [http-exec-1] script.system:dbwc_mssql_installer.16

    Creating DB agent DWD210122163030.ESDEVAU.WBCDEVAU.WESTPAC.COM.AU-A005E02

    Wed, 2021-02-24 16:43:47.761

    [http-exec-1] script.system:dbwc_mssql_installer.16

    java.lang.SecurityException: Not authorized to manage agents

    Wed, 2021-02-24 16:43:47.761

    [http-exec-1] script.system:dbwc_mssql_installer.16

    Agent DWD210122163030.ESDEVAU.WBCDEVAU.WESTPAC.COM.AU-A005E02 already exists, check for a duplicate data.

     

    Thanks

  • Here's a simple test about creating agent, including WindowsAgent and DB_SQL_Server Agent with related Core API. Other features like releasing lockbox, activate agent are not included and only some ASP configured in the test.

    1. The code set to the Body of the rest api request in Postman

    	<ScriptBean>
    		<script>
            <![CDATA[
    			rms=server.RemoteAgentManagementService;
    agentService=server.AgentService;
    configService=server.ConfigService;
    
    //set agent type and fglam ...
    agentType="DB_SQL_Server"  //WindowsAgent if creating WindowsAgent
    monitoringHost="localhost"
    fglamName = 'qg95f7z2.prod.quest.corp'
    
    createAgent("testAgent3")//set the agent name you want to create
    
    def createAgent(agentName) {
        def fglam1 = findFglam(fglamName)
        try {
            if (fglam1 && !agentService.findByName(agentName)) {
                
                rms.initializeAgent(fglam1.id, agentType, agentName);
    
                def agent = agentService.findByName(agentName)[0];
    
                def config = configService.getAgentInstancePrimaryAsp(agent.getAgentNamespace(), agent.getTypeId(), agent.getId());
                /*test for DB_SQL_Server*/
                config.setValueByString('monHost', monitoringHost);
                config.setValueByString('monICHostName', monitoringHost);
                config.setValueByString('monInstance', "test-instance");
               
                /*test for IC*/
                /*
                config.setValueByString('host', monitoringHost);
                config.setValueByString('hostNameOverride', agentName);
                config.setValueByString('collectSystemId', "false");
                */
                configService.saveConfig(config);
            }
        }catch (Exception e) {
            return false;
        }
        if(fglam1){
            return true;
        }else{
            return false;
        }
    }
    
    return 'Done!'
    
    def findFglam(name){
        try{
        return rms.findAllRemoteClients().find {
            it.id.startsWith(name)
        }
        }catch(Exception e){
                return null;
        }
    }
    ]]>
    		</script>
    	</ScriptBean>

    2. A screenshot of the test. I tried WindowsAgent and DB_SQL_Server and both were successfully.

    3. I tried with foglight account, and sufficient roles were already granted to the foglight account.

    4. The DB_SQL_Server agent I created.

    5. I didn't face the exception "Not authorized to manage agents"  from "script.system:dbwc_mssql_installer.16" as I only invoked Core API.

    Through this way should be able to create the agent, and you can customize the code to include more features like setting more ASP, activating the agents, creating more agents at a time, etc...

    Regards

    Jemy