DX Operational Intelligence

Expand all | Collapse all

Alert enrichment based on a query output

Jump to Best Answer
  • 1.  Alert enrichment based on a query output

    Posted 12-17-2019 10:21 AM
    Hi,

    Could someone help..

    I am trying to use the JDBC enrichment option to query a database and based on the output of the query map it to a values that I want to parse into the alert user attribute7...
    The query is simple is to validate if a date exist in the column "sched" if exist the value should be 1 else should be 0.
    And based on that output I map to 1=Holiday and 0=NotHoliday
    Finally the user attribute7 should contain Holiday or NotHoliday.

    For some reason I am always getting Holiday in the attribute7.

    Please let me know your thoughts.

    This is the portion of the XML
    <!-- ======Event Class====== -->
    <EventClass name='Alert'>
     <Enrich>
           <Field input="time_arrival" inputtype="string" type="jdbc"
           outputtype="std" column="id"
      connectionstring="jdbc:sqlserver://******\s2k141;databaseName=mdb;user=***;password=.***;"
           jdbcdriver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           query="select count(*) as line from bpwshft where sched like '%12/25/2019%'"
           returntype="string" output="line" />
     </Enrich>
    
     <Normalize>
      ...
       <Field output='temp_holiday' outputtype='ref' type='map' input='line'>
         <mapentry mapin='0' mapout='NotHoliday' />
         <mapentry mapin='.*' mapout='Holiday' />
       </Field>
      ...
     <\Normalize>
    
     <Format>
      ...
        <Field output='userAttribute7' format='{0}' input='temp_holiday' />
      ...
     <\Format>


    Best Regards,
    Rui Dinis



    ------------------------------
    HCL
    ------------------------------


  • 2.  RE: Alert enrichment based on a query output

    Posted 12-17-2019 12:46 PM
    Hi Rui,
    For testing:
    Did you try to write the output of "line" directly into a userAttribute, to make sure it contains the output you expect (0 or any other number)?

    And once you are sure you only have "0" in the field, you can make the regex more specific:
    mapin='^0$'​

    Michael

    ------------------------------
    Customer Success Architect
    CA Deutschland GmbH (a Broadcom company)
    ------------------------------



  • 3.  RE: Alert enrichment based on a query output

    Posted 12-17-2019 03:16 PM
    Hi Michael,

    Thanks for the reply,

    No I didn't test that, is that the o my way to see it is not possible to capture that in the logs?

    Regards,
    Rui






  • 4.  RE: Alert enrichment based on a query output

    Posted 12-18-2019 03:29 AM
    Hi Rui,

    you can see the information in the logs, but for that you have to set the connector into debug and then analyze the logs.
    The easiest way is to write the "line" value into a userAttribute to see the result of the query.

    Michael

    ------------------------------
    Customer Success Architect
    CA Deutschland GmbH (a Broadcom company)
    ------------------------------



  • 5.  RE: Alert enrichment based on a query output

    Posted 12-18-2019 04:40 AM
    Hi Michael,

    The value of userAttribute is "line", it looks is not parsing the result of the query.

    Rui


    ------------------------------
    HCL
    ------------------------------



  • 6.  RE: Alert enrichment based on a query output

    Posted 12-18-2019 05:08 AM
    Hi Rui,

    can you try using pairedlist as outputtype, and then use the following syntax to get the returned value.  I remember there were some issues that the result of the jdbc query always returns a pairedlist - just try.

    <Enrich>
    <Field input="internal_resourceaddr" inputtype="string" type="jdbc" outputtype="pairedlist" column="name,desc,org" connectionstring="jdbc:sqlserver://server01;databaseName=mdb; user=nsmadmin;password=admin;" jdbcdriver="com.microsoft.sqlserver.jdbc.SQLServerDriver" query="select name,description,organization_uuid from ca_resource_department where id=?" returntype="string,string,string" output="department" />
    </Enrich>


    The <Enrich> property uses the internal_resourceaddr value. The query returns the corresponding name, description, and organization_uuid. The query also assigns this information to new properties using the defined column attributes as follows:
    ■ Returned name is assigned department_name_0
    ■ Returned description is assigned department_desc_0
    ■ Returned organization is assigned department_org_0

    Michael

    ------------------------------
    Customer Success Architect
    CA Deutschland GmbH (a Broadcom company)
    ------------------------------



  • 7.  RE: Alert enrichment based on a query output

    Posted 12-18-2019 11:00 AM
    Edited by Rui Miguel Goncalves Dinis 12-18-2019 11:19 AM
    Michael,

    I have change the Enrich section with the pairedlist outputtype but it still returning "line"(string) as the result.

    <Enrich>
    		
    		<Field input="time_origin" inputtype="string" type="jdbc" 
    			outputtype="pairedlist" column="sched"
    			connectionstring="jdbc:sqlserver://**\s2k141;databaseName=mdb;user=**;password=.**;" 
    			jdbcdriver="com.microsoft.sqlserver.jdbc.SQLServerDriver" 
    			query="	SELECT COUNT(*) FROM bpwshft WHERE sched like '%'+convert(varchar, CONVERT(DATETIME, '?'),101)+'%' and sym like 'Regular'" 
    			returntype="string" output="line" />
    		</Enrich>

    I am not seeing any request to the SQL server starting from the connector...
    I am wondering if the sql driver is in the right place?
    In my case i have it here:
    Catalyst\CatalystConnector\container\lib\sqljdbc4.jar

    I am also seeing the following in the logs where the outputval-->null

    2019-12-18 16:14:35 DEBUG [com.ca.eventplus.catalog.plugin.Enricher:Enrich] AbstractPlugin - fldobj in Enricher ==>com.ca.eventplus.catalog.util.CatalogXMLHandler$FieldObject@6491cb90--field-->line--ntype-->jdbc--inputval-->[time_origin]--outputtype-->pairedlist
    2019-12-18 16:14:35 DEBUG [com.ca.eventplus.catalog.plugin.Enricher:Enrich] AbstractPlugin - inside JDBC block ==>SELECT COUNT(*) sched FROM bpwshft WHERE sched like '%'+convert(varchar, CONVERT(DATETIME, '?'),101)+'%' and sym like 'Regular'--intypestring-->string--column-->[Ljava.lang.String;@16a845ee--paramstr-->time_origin,--Key-->time_origin,com.microsoft.sqlserver.jdbc.SQLServerDriverjdbc:sqlserver://****\s2k141;databaseName=mdb;user=**;password=.***;SELECT COUNT(*) sched FROM bpwshft WHERE sched like '%'+convert(varchar, CONVERT(DATETIME, '?'),101)+'%' and sym like 'Regular'stringstringsched--outputval-->null:jdbc
    2019-12-18 16:14:35 DEBUG [com.ca.eventplus.catalog.plugin.Enricher:Enrich] AbstractPlugin - com.ca.eventplus.catalog.plugin.Enricher:Enrich***** TestInject-3128779.in*************



    Regards,
    Rui Dinis



    ------------------------------
    Rui Dinis
    HCL-Portugal
    ------------------------------



  • 8.  RE: Alert enrichment based on a query output
    Best Answer

    Posted 01-02-2020 09:41 AM
    Ola Rui, Feliz Ano Novo!

    I've seen the same behaviour with other types of enrichments. This is actually where the DEBUG logging is failing short and trial-and-error must take over, unfortunately. I think we can clearly state that the output of "line" does not seem to contain the value that you expect, namely "0", for reasons of it always shows the "anything else" value (.*) This can mean two things: 1. The query returns a value that you do not expect (which is unlikely, because I'm sure you tested the query), and 2. The output of the query is in an unexpected format which will "corrupt" the "Map" section.
    What you could try to do is to make the query part a simple as possible, like a select something very specific, or a count(*) of something.
    This might get you a bit further. Besides this test do enable ALL possible debugging.

    Vincent