10 Replies Latest reply: Nov 4, 2008 11:01 AM by 666373 RSS

    Use of SQL fetchlet to create a custom management plug-in

    666373
      Hi,

      I would appreciate if you anyone could help me out on this:

      I need to fetch some data from EMF tables and create a custom Management plug-in in OEM so that it can be monitored from OEM itself. For getting the data from database table i am using SQL fetchlet coz it provides the facility to use SQL statement in it. Following is the code i have developed in target-type.xml file.

      <TargetMetadata META_VER="1.0" TYPE="emf_filer">
      <Display>
      <Label NLSID="emf_filer">Error Management Filer</Label>
      </Display>
      <Metric NAME="emf_filer" TYPE="TABLE">
      <Display>
      <Label NLSID="emf_filer_resp">Response</Label>
      </Display>
      <TableDescriptor>
      <!-- <ColumnDescriptor NAME="tcpPing" TYPE="NUMBER" >
      <Display>
      <Label NLSID="netapp_filer_resp_tcpPing">TCP Ping, Milliseconds</Label>
      </Display>
      </ColumnDescriptor> -->
      <ColumnDescriptor NAME="Status" TYPE="NUMBER" >
      <Display>
      <Label NLSID="emf_filer_resp_status">Status</Label>
      </Display>
      </ColumnDescriptor>
      <ColumnDescriptor NAME="PROGRAM_NAME" TYPE="STRING" >
      <Display>
      <Label NLSID="PROGRAM_NAME">Program Name</Label>
      </Display>
      </ColumnDescriptor>
      <ColumnDescriptor NAME="PROGRAM_TYPE" TYPE="STRING" >
      <Display>
      <Label NLSID="PROGRAM_TYPE">Program Type</Label>
      </Display>
      </ColumnDescriptor>
      <ColumnDescriptor NAME="RICEW_ID" TYPE="STRING" >
      <Display>
      <Label NLSID="RICEW_ID">Rice Id</Label>
      </Display>
      </ColumnDescriptor>
      <ColumnDescriptor NAME="Request_id" TYPE="NUMBER" IS_KEY="TRUE" >
      <Display>
      <Label NLSID="Request_id">Request Id</Label>
      </Display>
      </ColumnDescriptor>
      <ColumnDescriptor NAME="EBIZ_INSTANCE_NAME " TYPE="STRING" >
      <Display>
      <Label NLSID="EBIZ_INSTANCE_NAME">Instance Name</Label>
      </Display>
      </ColumnDescriptor>
      </TableDescriptor>
      <QueryDescriptor FETCHLET_ID="SQL">
      <Property NAME="MachineName" SCOPE="INSTANCE">MachineName</Property>
      <Property NAME="Port" SCOPE="INSTANCE">Port</Property>
      <Property NAME="SID" SCOPE="INSTANCE">SID</Property>
      <Property NAME="UserName" SCOPE="UserName">apps</Property>
      <Property NAME="password" SCOPE="password">password</Property>
      <Property NAME="ServiceName" SCOPE="INSTANCE">ServiceName</Property>
      <Property NAME="STATEMENT" SCOPE="INSTANCE">
      BEGIN
      SELECT PROGRAM_NAME, PROGRAM_TYPE,
      RICEW_ID,REQUEST_ID, EBIZ_INSTANCE_NAME FROM XX_EMF_MESSAGE_HEADER WHERE STATUS = 'ERROR' AND RICEW_ID = 'INT-DL019';
      END;
      </Property>
      <!-- <Property NAME="SQLINPARAM1" SCOPE="ENV">HOSTNAME</Property>
      <Property NAME="SQLOUTPARAMPOS" SCOPE="GLOBAL">2</Property>
      <Property NAME="SQLOUTPARAMTYPE" SCOPE="GLOBAL">SQL_CURSOR</Property>-->
      <Property NAME="NUMROWS" SCOPE="GLOBAL">40
      </Property>
      </QueryDescriptor>

      <ExecutionDescriptor>
      <GetTable NAME="emf_filer"/>
      <GetView NAME="emf_View" FROM_TABLE="emf_filer">
      <Column NAME="PROGRAM_NAME"/>
      <Column NAME="PROGRAM_TYPE"/>
      <Column NAME="RICEW_ID"/>
      <Column NAME="REQUEST_ID"/>
      <Column NAME="EBIZ_INSTANCE_NAME"/>
      <Filter COLUMN_NAME="REQUEST_ID" OPERATOR="GT">0</Filter>
      </GetView>
      </ExecutionDescriptor>
      <InstanceProperties>
      <InstanceProperty NAME="UserName" CREDENTIAL="TRUE" OPTIONAL="FALSE" NEED_REENTER="FALSE" HIDE_ENTRY="FALSE">
      <Display>
      <Label NLSID="UserName_emf">Username</Label>
      </Display>
      </InstanceProperty>
      <InstanceProperty NAME="password" CREDENTIAL="TRUE" OPTIONAL="FALSE" NEED_REENTER="TRUE">
      <Display>
      <Label NLSID="password_emf">Password</Label>
      </Display>
      </InstanceProperty>
      <InstanceProperty NAME="MachineName" CREDENTIAL="FALSE" OPTIONAL="FALSE">
      <Display>
      <Label NLSID="MachineName_emf">Listener Machine Name</Label>
      </Display>
      </InstanceProperty>
      <InstanceProperty NAME="Port" CREDENTIAL="FAL
      :
      :
      :
      :
      :
      My doubt here is:
      As per Oracle it is importent to use response matrics with status column. How can i accomodate Response matrics with this code. what will be the columns except Status that i need to mention? if i use just one matrics i.e response then what will i use in in FROM clause in ExecutionDescriptor [<GetView NAME="emf_View" FROM_TABLE="emf_filer">]? i wuld appreciate if anyone could clarify my issues.

      Thanks for your help.

      Regards,
      AS
        • 1. Re: Use of SQL fetchlet to create a custom management plug-in
          caroy
          First of all, you need to break the Response metric out into its own metric. So you'd have just a Response metric with a Status column (and you can have the ping column or leave it out) and you'd have a separate metric for the query you are doing.

          For that second metric, you'll want to change these lines:

          <Property NAME="UserName" SCOPE="UserName">apps</Property>
          <Property NAME="password" SCOPE="password">password</Property>

          to:

          <Property NAME="UserName" SCOPE="INSTANCE">UserName</Property>
          <Property NAME="password" SCOPE="INSTANCE">password</Property>

          where the bold values match the name of your InstanceProperty.

          You'll want to change the SCOPE of your STATEMENT Property to be GLOBAL, and you can remove the last three properties (SQLOUTPARAMPOS, SQLOUTPARAMTYPE, NUMROWS). And remove the BEGIN and END; from your STATEMENT Property value, just leaving the select statement.

          Also, you shouldn't need the ExecutionDescriptor. Remove that part.

          Once that is done, make sure the columns in your metric match up with the return values in the SQL select clause because that's how the agent will read them in when doing the data collection.
          • 2. Re: Use of SQL fetchlet to create a custom management plug-in
            666373
            Hi, Thanks for your response..

            I have a doubt here. if i use Response matrics as a saperate matric then don't i need to use queryDescriptor for Response matric? and if i have to use Querydescriptor then as i am using SQL Fetchlet, what will i mention in STATEMENT property for Response matrics?

            Thanks for your help.

            Regards,
            AS
            • 3. Re: Use of SQL fetchlet to create a custom management plug-in
              caroy
              Each metric gets its own QueryDescriptor. So you will have the Response metric with a QueryDescriptor, and then you will have your other metric with a different QueryDescriptor. That way you could use different fetchlets to get different data.
              • 4. Re: Use of SQL fetchlet to create a custom management plug-in
                666373
                That is what i am trying to ask..as i will b using 2 dfifferent matrics so i will have to use 2 diffrent QueryDescriptors one for Response matric and one for other one. Now following QueryDescriptor will be added in other(Not Response) matric then what should i add in QueryDescriptor in Response matric?

                Query Descriptor for matric other than "Reposnse":

                QueryDescriptor FETCHLET_ID="SQL">
                     <Property NAME="MachineName" SCOPE="INSTANCE">MachineName</Property>
                     <Property NAME="Port" SCOPE="INSTANCE">Port</Property>
                     <Property NAME="SID" SCOPE="INSTANCE">SID</Property>
                     <Property NAME="UserName" SCOPE="UserName">UserName</Property>
                     <Property NAME="password" SCOPE="password">password</Property>
                     <Property NAME="ServiceName" SCOPE="INSTANCE">ServiceName</Property>
                     <Property NAME="STATEMENT" SCOPE="GLOBAL">
                          SELECT PROGRAM_NAME,
                                         PROGRAM_TYPE,
                                         RICEW_ID,REQUEST_ID,
                                         EBIZ_INSTANCE_NAME
                          FROM XX_EMF_MESSAGE_HEADER WHERE STATUS = 'ERROR' AND RICEW_ID = 'INT-DL019';
                          </Property>
                </QueryDescriptor>

                what should be the QueryDescriptor for Response matric?? Will it be same or different?

                Thanks for your help.

                Regards,
                AS

                Edited by: user9959418 on Oct 31, 2008 1:10 PM
                • 5. Re: Use of SQL fetchlet to create a custom management plug-in
                  caroy
                  First of all, you still need to change the SCOPE for your credentials... INSTANCE if you're going to get them from your InstanceProperties or GLOBAL if you plan on hardcoding them for now (but you'll want to change them to INSTANCE eventually).

                  The SQL statement should return data in the order and number of columns you have for your metric, so if you have only a single column for your Response metric (Status), you would return only a single thing from your select clause. Probably something like:

                  select 1 from dual

                  The problem with using the SQLFetchlet to determine your Response metric's Status column is that if the database is down, there's no way to return 0 for the Status column. If the DB is down, the SQLFetchlet won't be able to connect and the collection will return nothing at all (because of the error). The Status column is used to determine up/down status in the repository but it's also used to determine Availability percentage. If you want that number to be accurate, you'll probably want to create a script which can ping the database to see if it is up or not. I imagine the Oracle database has a script to do this for its Response metric, so you should consider copying it to use with your own target type and using the OSLineToken Fetchlet instead of the SQLFetchlet for the Response metric.
                  • 6. Re: Use of SQL fetchlet to create a custom management plug-in
                    666373
                    I am trying to develop it as per your guidence. I really appreciate your help.

                    Thanks,

                    Regards,
                    AS
                    • 7. Re: Use of SQL fetchlet to create a custom management plug-in
                      666373
                      I was looking at the oacle_database.xml target file in order to find the script to test the database connectivity, in this they are calling &instance_metrics, &database_metrics, &credentials.xmlp files. I am not sure what these 3 files are doing?? I think the script you mentioned to check whther the database is running or not, will be tested by one of these files. if you have any idea of this then please guide me on this.

                      <TargetMetadata META_VER="4.2" TYPE="oracle_database" CATEGORY_PROPERTIES="VersionCategory;MCPkg;MetricScope;DataGuardStatus" RESOURCE_BUNDLE_PACKAGE="oracle.sysman.db.rsc">
                           <Display>
                                <Label NLSID="name">Database Instance</Label>
                           </Display>
                           <!-- Skip all metrics when State columns triggers a severity (MOUNTED database) -->
                           <AltSkipCondition METRIC="Response" COLUMN="State"/>

                      &instance_metrics;
                      &database_metrics;
                      &credentials;

                      Also if there any doc on oracle_database plug-in then please let me know.

                      Appreciate your help.

                      Thanks,
                      Regards,
                      Anshul singhal
                      • 8. Re: Use of SQL fetchlet to create a custom management plug-in
                        caroy
                        Just look for whichever one contains the Response metric.
                        • 9. Re: Use of SQL fetchlet to create a custom management plug-in
                          caroy
                          You are aware there is an Extensibility Guide you can download which will give you details and examples on the target metadata file. It's on OTN on this page:

                          http://www.oracle.com/technology/products/oem/extensions/index.html
                          • 10. Re: Use of SQL fetchlet to create a custom management plug-in
                            666373
                            Hi,

                            I really appreciate your help and time to solve this problem.

                            Thanks,

                            Regards,
                            AS