Jdbc_static local_lookup not working with Azure data

[spate (Scott)]

Hi, I'm working with Azure data being piped into ELK, and I want to add the subscription name to the event. Currently it only has the subscription ID. We have an external postgreql DB with subscription name and ID (as shown below). I have jdbc_static configured in my logstash pipeline which I have had to wrestle with but am at the point now where I'm not sure what to do. The pipeline runs and I don't get any errors, but the subscription field is empty in the event. I think what is happening is that the local_lookup query is running and returning zero results due to the way the subscription ID is formatted.

==Postgresql data==

-[ RECORD 1 ]-----+------------------------------------------
subscription_name | Subscription 1 - POC
subscriptionguid | 4ce9b4be-ecba-42c0-7879-3ca316ed20bb

===Relevant portion of logstash pipeline .conf===

jdbc_static {
    local_db_objects => [
      {
        name => "subs"
        index_columns => ["subscription_name"]
        columns => [
          ["subscription_name", "varchar(60)"],
          ["subscriptionguid", "varchar(60)"]
        ]
      }
    ]
    loaders => [
      {
        id => "subscription_info"
        query => "select subscription_name, subscriptionguid from clouddata order by subscription_name"
        local_table => "subs"
      }
    ]
    local_lookups => [
      {
        query => "SELECT subscription_name FROM subs WHERE subscriptionguid = :p1"
        parameters => {p1 => "[azure][subscription]"}
        target => "subscription"
      }
    ]

I had trouble figuring out how to populate p1 but from the logs I can see that it is getting the data correclty. If I add single quotes around p1 (':p1'), I get an error like:
:exception=>"Java::JavaSql::SQLSyntaxErrorException: Syntax error: Encountered "4ce9b4b" at line 1, column 62."==

This tells me that the query is running correctly and p1 is being populated with the subscription ID, but again I think it is not formatted correctly.

To further support this idea, the following command fails

root:> echo '\x \ select subscription_name from clouddata where subscriptionguid = 4ce9b4be-ecba-42c0-7879-3ca316ed20bb;' | psql -h -U -d

ERROR: syntax error at or near "ce9b4be"
LINE 1: ...ion_name from clouddata where subscriptionguid = 4ce9b4be-ec... (there is an arrow pointing at the "c" after the "4").

Again, I have tried single quotes, double quotes, parenthesis, angle/square/curly brackets. I haven't been able to find the magic combination of characters to get this to work but I suspect if I do, this might fix the problem with local_lookups.

Any suggestions you can provide are appreciated.

[spate (Scott)]

The problem was the subscriptionid received from Azure had uppercase characters and the database I was querying had lowercase characters. Matching the case solved the problem.