# ES Lookups

**URL:** <https://discuss.elastic.co/t/es-lookups/220276>\
**Category:** Logstash\
**Created:** [February 20, 2020, 11:44pm UTC](https://discuss.elastic.co/t/es-lookups/220276 "2020-02-20T23:44:23Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![joaociocca](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joaociocca/32/15827_2.png) [@joaociocca](https://discuss.elastic.co/u/joaociocca)\
**Post date:** [February 20, 2020, 11:44pm UTC](https://discuss.elastic.co/t/es-lookups/220276/1 "2020-02-20T23:44:23Z")

</div>

So, I'm back dealing with RRAS logs on Logstash/Elasticsearch, and seems I'm in a pickle.

I'm using `filter { elasticsearch`, and debug shows it's working fine:

```auto
[2020-02-20T20:36:56,495][DEBUG][logstash.pipeline] filter received {"event"=>{"ConnectInfo"=>nil, "AcctDelayTime"=>nil, "AcctSessionID"=>"<number>", "AcctTunnelConn"=>nil, "AcctOutputOctets"=>nil, "UserName"=>nil, "CallingStationID"=>nil, "NASIPAddress"=>nil, "AcctLinkCount"=>nil, "TunnelPreference"=>nil, "ServiceType"=>nil, "FQUser"=>"<user>", "@version"=>"<number>", "PolicyName"=>nil, "ReasonCode"=>"<number>", "IdleTimeout"=>nil, "AcctMultiSsnID"=>nil, "MSCHAPDomain"=>nil, "AcctOutputPackets"=>nil, "AcctAuthentic"=>nil, "TunnelMediumType"=>nil, "PacketType"=>"<number>", "PortLimit"=>nil, "AcctInputPackets"=>nil, "RemoteServerAddress"=>nil, "MSRASVendor"=>nil, "TunnelPvtGroupID"=>nil, "AcctInputOctets"=>nil, "TunnelServerEndpt"=>nil, "ProviderType"=>"<number>", "Class"=>"<number> <number> <number>.<number>.<number>.<number> <number>/<number>/<number> <number>:<number>:<number> <number>", "path"=>"/var/path/file", "EventTimestamp"=>nil, "CallbackNumber"=>nil, "AcctStatusType"=>nil, "TunnelAssignmentID"=>nil, "MSMPPEEncryptionTypes"=>nil, "AuthenticationType"=>"<number>", "MSAcctEAPType"=>nil, "TunnelType"=>nil, "TunnelClientEndpt"=>nil, "message"=>"\"<hostname>\",\"RAS\",<number>/<number>/<number>,<number>:<number>:<number>,<number>,,\"<user>\",,,,,,,,,,,,,,,,,<number>,,<number>,\"<number> <number> <number>.<number>.<number>.<number> <number>/<number>/<number> <number>:<number>:<number> <number>\",,,,,,,,,\"<number>\",,,,,,,,,,,,,,,,,,,,,,,,,\"Microsoft Routing and Remote Access Service Policy\",<number>,,,,\r", "AcctInterimInterval"=>nil, "host"=>"<anotherhost>", "AcctTerminateCause"=>nil, "@timestamp"=><number>-<number>-<number>T<number>:<number>:<number>.<number>Z, "FramedProtocol"=>nil, "ClientIPAddress"=>nil, "FramedIPAddress"=>nil, "EAPFriendlyName"=>nil, "ProxyPolicyName"=>"Microsoft Routing and Remote Access Service Policy", "MSAcctAuthType"=>nil, "ClientFriendlyName"=>nil, "CalledStationID"=>nil, "NASIdentifier"=>nil, "ServiceName"=>"RAS", "MSRASClientVersion"=>nil, "type"=>"rras_log", "MSCHAPError"=>nil, "Date"=>"<number>/<number>/<number>", "ComputerName"=>"<hostname>", "ClientVendor"=>nil, "TerminationAction"=>nil, "AcctSessionTime"=>nil, "MSRASVersion"=>nil, "NASPort"=>nil, "NASPortType"=>nil, "MSMPPEEncryptionPolicy"=>nil, "Time"=>"<number>:<number>:<number>", "MSRASClientName"=>nil, "ProviderName"=>nil, "SessionTimeout"=>nil}}
[2020-02-20T20:36:56,526][DEBUG][logstash.filters.elasticsearch] Querying elasticsearch for lookup {:params=>{:index=>"rras_vpn-2018.11.24", :q=>"PacketType:1 AND AcctSessionID:100365 AND FQUser:<user>", :size=>1, :sort=>"@timestamp:desc"}}

```

So it get's the correct query. But... nothing happens? I have configured the filter plugin like this:

```auto
    elasticsearch {
      hosts => ["http://10.122.151.127:9200"]
      index => "%{[@metadata][_index]}"
      query => "PacketType:1 AND AcctSessionID:%{[AcctSessionID]} AND FQUser:%{[FQUser]}"
      fields => { 
        "MSRASClientName" => "Source_MSRASClientName"
        "loginCredential" => "Source_LoginCredential"
        "FQUser" => "Source_FQUser"
        "UserName" => "Source_UserName"
        "TunnelServerEndpt" => "Source_TunnelServerEndpt"
        "CalledStationID" => "Source_CalledStationID"
        "TunnelClientEndpt" => "Source_TunnelClientEndpt"
        "CallingStationID" => "Source_CallingStationID"
        "PolicyName" => "Source_PolicyName"
      }
    }

```

But I'm getting none of those fields. No matter that, when I repeat the exact same query, I get a hit. Any ideas on what I'm doing wrong? Be it on debug or the final output, these fields NEVER show up.

---

<div class="post-metadata">

**Author:** ![joaociocca](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joaociocca/32/15827_2.png) [@joaociocca](https://discuss.elastic.co/u/joaociocca)\
**Post date:** [February 21, 2020, 3:36am UTC](https://discuss.elastic.co/t/es-lookups/220276/2 "2020-02-21T03:36:26Z")

</div>

ok, now this seems to be happening because older data doesn't actually have anything in those fields... and I suck at making ES queries. On to figure out how to make a query that retrieves everything from 2018-01-01 up until now...

```json
    query => '{ "query": { "query_string": { "query": "\"range\" : { \"@timestamp\" : { \"gte\" : 2018-01-01, \"lte\" : now }}" } }, "sort": ["@timestamp"] }'

```

this doesn't seem to work...

---

<div class="post-metadata">

**Author:** ![joaociocca](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joaociocca/32/15827_2.png) [@joaociocca](https://discuss.elastic.co/u/joaociocca)\
**Post date:** [March 10, 2020, 12:37am UTC](https://discuss.elastic.co/t/es-lookups/220276/3 "2020-03-10T00:37:10Z")

</div>

I'm back from vacation and still at a loss with this issue. I managed to correct the query:

```auto
      "query": { 
            "range" : { 
              "@timestamp" : { 
                "gte" : "2018-11-01", "lte" : "now"
              }
            }
      }, "sort": ["@timestamp"]

```

(because I needed from Nov 1st instead of Jan 1st, so everything is nice now)

But I'm still not getting the filter result:

```auto
[2020-03-09T21:21:09,553][DEBUG][logstash.pipeline] filter received {"event"=>{"EventTimestamp"=>nil, "FQUser"=>"<user>", "AcctStatusType"=>nil, "MSAcctEAPType"=>nil, "PacketType"=>"3", "TunnelPreference"=>nil, "AcctMultiSsnID"=>nil, "ClientVendor"=>nil, "TunnelClientEndpt"=>nil, "@version"=>"1", "AcctAuthentic"=>nil, "Date"=>"11/24/2018", "EAPFriendlyName"=>nil, "MSRASVendor"=>nil, "@timestamp"=>2018-11-24T05:14:17.000Z, "MSMPPEEncryptionPolicy"=>nil, "AcctSessionTime"=>nil, "ClientFriendlyName"=>nil, "CallbackNumber"=>nil, "MSRASClientVersion"=>nil, "FramedIPAddress"=>nil, "host"=>"<host>", "NASIdentifier"=>nil, "SessionTimeout"=>nil, "MSAcctAuthType"=>nil, "ProviderName"=>nil, "AcctInterimInterval"=>nil, "type"=>"rras_log", "PolicyName"=>nil, "ServiceType"=>nil, "CalledStationID"=>nil, "NASPortType"=>nil, "ClientIPAddress"=>nil, "ProxyPolicyName"=>"Microsoft Routing and Remote Access Service Policy", "MSMPPEEncryptionTypes"=>nil, "MSRASClientName"=>nil, "RemoteServerAddress"=>nil, "TunnelServerEndpt"=>nil, "TunnelPvtGroupID"=>nil, "ServiceName"=>"RAS", "MSCHAPError"=>nil, "ProviderType"=>"1", "ConnectInfo"=>nil, "AcctTunnelConn"=>nil, "UserName"=>nil, "NASPort"=>nil, "AuthenticationType"=>"4", "MSCHAPDomain"=>nil, "AcctOutputPackets"=>nil, "Time"=>"03:14:17", "AcctInputPackets"=>nil, "PortLimit"=>nil, "CallingStationID"=>nil, "ComputerName"=>"<server>", "AcctTerminateCause"=>nil, "TunnelType"=>nil, "AcctInputOctets"=>nil, "TerminationAction"=>nil, "IdleTimeout"=>nil, "TunnelMediumType"=>nil, "message"=>"\"<server>\",\"RAS\",11/24/2018,03:14:17,3,,\"<user>\",,,,,,,,,,,,,,,,,4,,48,\"311 1 <ip> 05/26/2018 16:09:57 100364\",,,,,,,,,\"100365\",,,,,,,,,,,,,,,,,,,,,,,,,\"Microsoft Routing and Remote Access Service Policy\",1,,,,\r", "NASIPAddress"=>nil, "AcctSessionID"=>"100365", "path"=>"/var/leitura/entrada_vpn_full.csv", "FramedProtocol"=>nil, "TunnelAssignmentID"=>nil, "AcctOutputOctets"=>nil, "MSRASVersion"=>nil, "AcctLinkCount"=>nil, "ReasonCode"=>"48", "AcctDelayTime"=>nil, "Class"=>"311 1 <ip> 05/26/2018 16:09:57 100364"}}
[2020-03-09T21:21:09,588][DEBUG][logstash.filters.elasticsearch] Querying elasticsearch for lookup {:params=>{:index=>"rras_vpn-2018.11.24", :q=>"PacketType:1 AND AcctSessionID:100365 AND FQUser:<user>", :size=>1, :sort=>"@timestamp:desc"}}
[2020-03-09T21:21:09,588][INFO][logstash.filters.elasticsearch] New ElasticSearch filter client {:hosts=>["http://10.122.151.127:9200"]}
C:/Users/c070054/Downloads/logstash-6.8.6/vendor/bundle/jruby/2.5.0/gems/logstash-filter-fingerprint-3.2.1/lib/logstash/filters/fingerprint.rb:181: warning: constant ::Fixnum is deprecated
[2020-03-09T21:21:09,819][DEBUG][logstash.pipeline] output received {"event"=>{"EventTimestamp"=>nil, "FQUser"=>"<user>", "AcctStatusType"=>nil, "MSAcctEAPType"=>nil, "PacketType"=>"3", "TunnelPreference"=>nil, "AcctMultiSsnID"=>nil, "ClientVendor"=>nil, "TunnelClientEndpt"=>nil, "@version"=>"1", "AcctAuthentic"=>nil, "Date"=>"11/24/2018", "EAPFriendlyName"=>nil, "MSRASVendor"=>nil, "@timestamp"=>2018-11-24T05:14:17.000Z, "MSMPPEEncryptionPolicy"=>nil, "AcctSessionTime"=>nil, "ClientFriendlyName"=>nil, "CallbackNumber"=>nil, "MSRASClientVersion"=>nil, "FramedIPAddress"=>nil, "host"=>"<host>", "NASIdentifier"=>nil, "SessionTimeout"=>nil, "MSAcctAuthType"=>nil, "ProviderName"=>nil, "AcctInterimInterval"=>nil, "type"=>"rras_log", "PolicyName"=>nil, "ServiceType"=>nil, "CalledStationID"=>nil, "NASPortType"=>nil, "ClientIPAddress"=>nil, "ProxyPolicyName"=>"Microsoft Routing and Remote Access Service Policy", "MSMPPEEncryptionTypes"=>nil, "MSRASClientName"=>nil, "RemoteServerAddress"=>nil, "TunnelServerEndpt"=>nil, "TunnelPvtGroupID"=>nil, "ServiceName"=>"RAS", "MSCHAPError"=>nil, "ProviderType"=>"1", "ConnectInfo"=>nil, "AcctTunnelConn"=>nil, "UserName"=>nil, "NASPort"=>nil, "AuthenticationType"=>"4", "MSCHAPDomain"=>nil, "AcctOutputPackets"=>nil, "Time"=>"03:14:17", "AcctInputPackets"=>nil, "PortLimit"=>nil, "CallingStationID"=>nil, "ComputerName"=>"<server>", "AcctTerminateCause"=>nil, "TunnelType"=>nil, "AcctInputOctets"=>nil, "TerminationAction"=>nil, "IdleTimeout"=>nil, "TunnelMediumType"=>nil, "message"=>"\"<server>\",\"RAS\",11/24/2018,03:14:17,3,,\"<user>\",,,,,,,,,,,,,,,,,4,,48,\"311 1 <ip> 05/26/2018 16:09:57 100364\",,,,,,,,,\"100365\",,,,,,,,,,,,,,,,,,,,,,,,,\"Microsoft Routing and Remote Access Service Policy\",1,,,,\r", "NASIPAddress"=>nil, "AcctSessionID"=>"100365", "path"=>"/var/leitura/entrada_vpn_full.csv", "FramedProtocol"=>nil, "TunnelAssignmentID"=>nil, "AcctOutputOctets"=>nil, "MSRASVersion"=>nil, "AcctLinkCount"=>nil, "ReasonCode"=>"48", "AcctDelayTime"=>nil, "Class"=>"311 1 <ip> 05/26/2018 16:09:57 100364"}}
C:/Users/c070054/Downloads/logstash-6.8.6/vendor/bundle/jruby/2.5.0/gems/awesome_print-1.7.0/lib/awesome_print/formatters/base_formatter.rb:31: warning: constant ::Fixnum is deprecated

```

I can manually put the query " `PacketType:1 AND AcctSessionID:100365 AND FQUser:<user>"` on Kibana and I receive the desired result, but I get absolutely NONE of the "Source\_" fields on ruby\_debug output:

```auto
{
         "AcctOutputPackets" => nil,
           "ProxyPolicyName" => "Microsoft Routing and Remote Access Service Policy",
             "AcctLinkCount" => nil,
       "AcctInterimInterval" => nil,
                      "type" => "rras_log",
            "AcctTunnelConn" => nil,
           "MSRASClientName" => nil,
       "RemoteServerAddress" => nil,
                      "path" => "/var/leitura/entrada_vpn_full.csv",
           "EAPFriendlyName" => nil,
            "SessionTimeout" => nil,
             "MSAcctEAPType" => nil,
         "TunnelServerEndpt" => nil,
                "PacketType" => "3",
               "ServiceType" => nil,
          "AcctInputPackets" => nil,
                      "host" => "<host>",
          "TunnelPreference" => nil,
        "AuthenticationType" => "4",
         "TerminationAction" => nil,
    "MSMPPEEncryptionPolicy" => nil,
              "ClientVendor" => nil,
            "MSAcctAuthType" => nil,
                 "@metadata" => {
              "_type" => "doc",
         "total_hits" => 0,
             "_index" => "rras_vpn-2018.11.24",
                "_id" => "jbDwEGkBOCcFUcpMwgAf",
        "fingerprint" => 1014899617
    },
                    "FQUser" => "<user>",
            "FramedProtocol" => nil,
           "AcctInputOctets" => nil,
             "AcctAuthentic" => nil,
                "ReasonCode" => "48",
               "NASPortType" => nil,
          "CallingStationID" => nil,
            "EventTimestamp" => nil,
            "AcctStatusType" => nil,
               "ServiceName" => "RAS",
                "PolicyName" => nil,
            "CallbackNumber" => nil,
              "ComputerName" => "<server>",
                     "Class" => "311 1 <ip> 05/26/2018 16:09:57 100364",
              "ProviderType" => "1",
        "TunnelAssignmentID" => nil,
     "MSMPPEEncryptionTypes" => nil,
               "ConnectInfo" => nil,
                 "PortLimit" => nil,
               "IdleTimeout" => nil,
                      "Time" => "03:14:17",
           "ClientIPAddress" => nil,
             "NASIdentifier" => nil,
           "CalledStationID" => nil,
         "TunnelClientEndpt" => nil,
             "AcctDelayTime" => nil,
                  "@version" => "1",
              "MSCHAPDomain" => nil,
               "MSRASVendor" => nil,
        "ClientFriendlyName" => nil,
           "FramedIPAddress" => nil,
             "AcctSessionID" => "100365",
        "MSRASClientVersion" => nil,
                  "UserName" => nil,
                "TunnelType" => nil,
           "AcctSessionTime" => nil,
                   "NASPort" => nil,
                   "message" => "\"<server>\",\"RAS\",11/24/2018,03:14:17,3,,\"<user>\",,,,,,,,,,,,,,,,,4,,48,\"311 1 <ip> 05/26/2018 16:09:57 100364\",,,,,,,,,\"100365\",,,,,,,,,,,,,,,,,,,,,,,,,\"Microsoft Routing and Remote Access Service Policy\",1,,,,\r",
                      "Date" => "11/24/2018",
          "TunnelMediumType" => nil,
            "AcctMultiSsnID" => nil,
              "MSRASVersion" => nil,
              "ProviderName" => nil,
                "@timestamp" => 2018-11-24T05:14:17.000Z,
          "AcctOutputOctets" => nil,
          "TunnelPvtGroupID" => nil,
        "AcctTerminateCause" => nil,
              "NASIPAddress" => nil,
               "MSCHAPError" => nil
}

```

What could I be doing wrong?

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [March 10, 2020, 12:57am UTC](https://discuss.elastic.co/t/es-lookups/220276/4 "2020-03-10T00:57:02Z")

</div>

How come you are basing the query on an index specified through a metadata field and not a static index pattern? How many matches does a query that succeeds return?

---

<div class="post-metadata">

**Author:** ![joaociocca](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joaociocca/32/15827_2.png) [@joaociocca](https://discuss.elastic.co/u/joaociocca)\
**Post date:** [March 10, 2020, 2:32am UTC](https://discuss.elastic.co/t/es-lookups/220276/5 "2020-03-10T02:32:48Z")

</div>

> [@Christian\_Dahlqvist](#):
>
> an index specified through a metadata field and not a static index pattern?

As you can see on the second debug line:

```auto
[2020-03-09T21:21:09,588][DEBUG][logstash.filters.elasticsearch] Querying elasticsearch for lookup {:params=>{:index=>"rras_vpn-2018.11.24", :q=>"PacketType:1 AND AcctSessionID:100365 AND FQUser:<user>", :size=>1, :sort=>"@timestamp:desc"}}

```

Query is going on a single index, in this case `rras_vpn-2018.11.24`. It's specified through the metadata because the input is from rras\_vpn\*, so other records will try other indices.

> [@Christian\_Dahlqvist](#):
>
> How many matches does a query that succeeds return?

that's one of the things, actually... it should be returning only one (as it correctly does when I try the query on Kibana's Discovery) - but considering the logs and ruby\_debug output, nothing is coming out, since filter received and output received show exactly the same fields, and not any of the `Source_*` fields...

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [March 10, 2020, 5:44am UTC](https://discuss.elastic.co/t/es-lookups/220276/6 "2020-03-10T05:44:16Z")

</div>

Can you please show the document that query should have matched?

---

<div class="post-metadata">

**Author:** ![joaociocca](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joaociocca/32/15827_2.png) [@joaociocca](https://discuss.elastic.co/u/joaociocca)\
**Post date:** [March 10, 2020, 6:06pm UTC](https://discuss.elastic.co/t/es-lookups/220276/7 "2020-03-10T18:06:18Z")

</div>

```auto
GET /rras_vpn-2018.11.24/_search
{ "query":
  { "bool" :
    { "must": [
          { "match": { "AcctSessionID" : 100365 }},
          { "match": { "FQUser" : "<user>" }}
        ]
      }
    }
}

```

And the result...

```auto
{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 2,
    "max_score": 8.916708,
    "hits": [
      {
        "_index": "rras_vpn-2018.11.24",
        "_type": "doc",
        "_id": "jbDwEGkBOCcFUcpMwgAf",
        "_score": 8.916708,
        "_source": {
          "ProxyPolicyName": "Microsoft Routing and Remote Access Service Policy",
          "ClientIPAddress": null,
          "AuthenticationType": "4",
          "SessionTimeout": null,
          "MSAcctAuthType": null,
          "EventTimestamp": null,
          "AcctTerminateCause": null,
          "ComputerName": "<server>",
          "CallbackNumber": null,
          "TunnelPvtGroupID": null,
          "MSMPPEEncryptionTypes": null,
          "AcctInputPackets": null,
          "AcctAuthentic": null,
          "MSRASVersion": null,
          "ProviderName": null,
          "ConnectInfo": null,
          "AcctLinkCount": null,
          "PortLimit": null,
          "AcctTunnelConn": null,
          "MSRASClientVersion": null,
          "message": """"<server>","RAS",11/24/2018,03:14:17,3,,"<user>",,,,,,,,,,,,,,,,,4,,48,"311 1 <ip> 05/26/2018 16:09:57 100364",,,,,,,,,"100365",,,,,,,,,,,,,,,,,,,,,,,,,"Microsoft Routing and Remote Access Service Policy",1,,,,
""",
          "FQUser": "<user>",
          "FramedIPAddress": null,
          "AcctInterimInterval": null,
          "AcctOutputPackets": null,
          "AcctStatusType": null,
          "NASIPAddress": null,
          "ReasonCode": "48",
          "host": "<host>",
          "UserName": null,
          "TunnelMediumType": null,
          "NASIdentifier": null,
          "@timestamp": "2018-11-24T05:14:17.000Z",
          "AcctDelayTime": null,
          "MSMPPEEncryptionPolicy": null,
          "PacketType": "3",
          "RemoteServerAddress": null,
          "ClientFriendlyName": null,
          "CallingStationID": null,
          "Time": "03:14:17",
          "MSRASVendor": null,
          "MSRASClientName": null,
          "ProviderType": "1",
          "ClientVendor": null,
          "@version": "1",
          "MSAcctEAPType": null,
          "CalledStationID": null,
          "AcctInputOctets": null,
          "AcctSessionTime": null,
          "ServiceName": "RAS",
          "type": "rras_log",
          "EAPFriendlyName": null,
          "TunnelAssignmentID": null,
          "TunnelServerEndpt": null,
          "TunnelPreference": null,
          "MSCHAPError": null,
          "MSCHAPDomain": null,
          "Class": "311 1 <ip> 05/26/2018 16:09:57 100364",
          "PolicyName": null,
          "ServiceType": null,
          "TunnelType": null,
          "path": "/var/leitura/entrada_vpn_full.csv",
          "TerminationAction": null,
          "AcctOutputOctets": null,
          "NASPort": null,
          "IdleTimeout": null,
          "AcctSessionID": "100365",
          "AcctMultiSsnID": null,
          "TunnelClientEndpt": null,
          "NASPortType": null,
          "Date": "11/24/2018",
          "FramedProtocol": null
        }
      },
      {
        "_index": "rras_vpn-2018.11.24",
        "_type": "doc",
        "_id": "jLDwEGkBOCcFUcpMwgAf",
        "_score": 8.1542425,
        "_source": {
          "ProxyPolicyName": "Microsoft Routing and Remote Access Service Policy",
          "ClientIPAddress": "<ip>",
          "AuthenticationType": "4",
          "SessionTimeout": null,
          "MSAcctAuthType": null,
          "EventTimestamp": null,
          "AcctTerminateCause": null,
          "ComputerName": "<server>",
          "CallbackNumber": null,
          "TunnelPvtGroupID": null,
          "MSMPPEEncryptionTypes": null,
          "AcctInputPackets": null,
          "AcctAuthentic": null,
          "MSRASVersion": "MSRASV5.20",
          "ProviderName": null,
          "ConnectInfo": null,
          "AcctLinkCount": null,
          "PortLimit": null,
          "AcctTunnelConn": null,
          "MSRASClientVersion": null,
          "message": """"<server>","RAS",11/24/2018,03:14:17,1,"<user>","<user>","<ip>","<ip>",,,"<server>","<ip>",129,,"<ip>","<server>",,,5,,1,2,4,,0,"311 1 <ip> 05/26/2018 16:09:57 100364",,,,,,,,,"100365",,,,,,,,,1,1,"<ip>","<ip>",,,,,,,"MSRASV5.20",311,,,,,"Microsoft Routing and Remote Access Service Policy",1,,,,
""",
          "FQUser": "<user>",
          "FramedIPAddress": null,
          "AcctInterimInterval": null,
          "AcctOutputPackets": null,
          "AcctStatusType": null,
          "NASIPAddress": "<ip>",
          "ReasonCode": "0",
          "host": "<host>",
          "UserName": "<user>",
          "TunnelMediumType": "1",
          "NASIdentifier": "<server>",
          "@timestamp": "2018-11-24T05:14:17.000Z",
          "AcctDelayTime": null,
          "MSMPPEEncryptionPolicy": null,
          "PacketType": "1",
          "RemoteServerAddress": null,
          "ClientFriendlyName": "<server>",
          "CallingStationID": "<ip>",
          "Time": "03:14:17",
          "MSRASVendor": "311",
          "MSRASClientName": null,
          "ProviderType": "1",
          "ClientVendor": null,
          "@version": "1",
          "MSAcctEAPType": null,
          "CalledStationID": "<ip>",
          "AcctInputOctets": null,
          "AcctSessionTime": null,
          "ServiceName": "RAS",
          "type": "rras_log",
          "EAPFriendlyName": null,
          "TunnelAssignmentID": null,
          "TunnelServerEndpt": "<ip>",
          "TunnelPreference": null,
          "MSCHAPError": null,
          "MSCHAPDomain": null,
          "Class": "311 1 <ip> 05/26/2018 16:09:57 100364",
          "PolicyName": null,
          "ServiceType": "2",
          "TunnelType": "1",
          "path": "/var/leitura/entrada_vpn_full.csv",
          "TerminationAction": null,
          "AcctOutputOctets": null,
          "NASPort": "129",
          "IdleTimeout": null,
          "AcctSessionID": "100365",
          "AcctMultiSsnID": null,
          "TunnelClientEndpt": "<ip>",
          "NASPortType": "5",
          "Date": "11/24/2018",
          "FramedProtocol": "1"
        }
      }
    ]
  }
}

```

The idea here is to link fields from a document with PacketType:3 (connection refused) to it's origin PacketType:1 (connection request), since some fields aren't supplied.

---

<div class="post-metadata">

**Author:** ![joaociocca](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joaociocca/32/15827_2.png) [@joaociocca](https://discuss.elastic.co/u/joaociocca)\
**Post date:** [March 11, 2020, 11:14pm UTC](https://discuss.elastic.co/t/es-lookups/220276/8 "2020-03-11T23:14:29Z")

</div>

FML. I found what the problem is! The `<user>` field contains a backslash. When Logstash tries to pass it as a `%{[field]}` parameter, they don't get escaped! THAT is why the Elasticsearch lookup was failing!

So I thought: "hmmm a `gsub => [field, "\\", "\\\\"]` should work", right? Wrong, debug was showing me I was getting a gsub error for `field, \"\\\", \""`. Where were my double backslashes going to?!

That's when I remembered [seeing something about using capture groups on gsub](https://discuss.elastic.co/t/replace-double-backslashes-with-one-in-a-string/165858/4)! But I had to do it the other way, instead of replace double backslashes, I had to duplicate one!

```auto
    mutate {
      gsub => ["FQUser", "([\\])", "\1\1" ]
    }

```

This did the job. There comes the `Source_*` fields!

```auto
[2020-03-11T22:50:03,535][DEBUG][logstash.pipeline] filter received {"event"=>{"ServiceType"=>nil, "EventTimestamp"=>nil, "TunnelMediumType"=>nil, "path"=>"/var/leitura/entrada_vpn_full.csv", "message"=>"\"<server>\",\"RAS\",11/24/2018,03:14:17,3,,\"<user>\",,,,,,,,,,,,,,,,,4,,48,\"311 1 <ip> 05/26/2018 16:09:57 100364\",,,,,,,,,\"100365\",,,,,,,,,,,,,,,,,,,,,,,,,\"Microsoft Routing and Remote Access Service Policy\",1,,,,\r", "MSRASClientName"=>nil, "RemoteServerAddress"=>nil, "AcctDelayTime"=>nil, "MSAcctEAPType"=>nil, "AcctOutputPackets"=>nil, "FQUser"=>"<user>", "PortLimit"=>nil, "AcctAuthentic"=>nil, "CallingStationID"=>nil, "AcctTerminateCause"=>nil, "UserName"=>nil, "ServiceName"=>"RAS", "MSCHAPDomain"=>nil, "TerminationAction"=>nil, "Date"=>"11/24/2018", "@timestamp"=>2018-11-24T05:14:17.000Z, "NASPortType"=>nil, "MSMPPEEncryptionTypes"=>nil, "NASIdentifier"=>nil, "PacketType"=>"3", "NASPort"=>nil, "TunnelPreference"=>nil, "AcctOutputOctets"=>nil, "type"=>"rras_log", "AcctTunnelConn"=>nil, "MSRASClientVersion"=>nil, "MSMPPEEncryptionPolicy"=>nil, "TunnelServerEndpt"=>nil, "MSCHAPError"=>nil, "MSRASVersion"=>nil, "PolicyName"=>nil, "FramedProtocol"=>nil, "Time"=>"03:14:17", "TunnelType"=>nil, "ComputerName"=>"<server>", "EAPFriendlyName"=>nil, "TunnelClientEndpt"=>nil, "ClientVendor"=>nil, "SessionTimeout"=>nil, "AcctMultiSsnID"=>nil, "host"=>"ccssvitrlx033", "AcctSessionID"=>"100365", "TunnelAssignmentID"=>nil, "Class"=>"311 1 <ip> 05/26/2018 16:09:57 100364", "ProviderName"=>nil, "AcctSessionTime"=>nil, "AcctInputPackets"=>nil, "@version"=>"1", "TunnelPvtGroupID"=>nil, "AcctInterimInterval"=>nil, "ClientIPAddress"=>nil, "FramedIPAddress"=>nil, "ClientFriendlyName"=>nil, "IdleTimeout"=>nil, "MSRASVendor"=>nil, "ProxyPolicyName"=>"Microsoft Routing and Remote Access Service Policy", "MSAcctAuthType"=>nil, "NASIPAddress"=>nil, "CalledStationID"=>nil, "AuthenticationType"=>"4", "AcctInputOctets"=>nil, "ProviderType"=>"1", "CallbackNumber"=>nil, "AcctLinkCount"=>nil, "ReasonCode"=>"48", "ConnectInfo"=>nil, "AcctStatusType"=>nil}} [2020-03-11T22:50:03,589][DEBUG][logstash.filters.elasticsearch] Querying elasticsearch for lookup {:params=>{:index=>"rras_vpn-2018.11.24", :body=>{"size"=>1, "query"=>{"bool"=>{"must"=>[{"match"=>{"PacketType"=>1}}, {"match"=>{"AcctSessionID"=>"100365"}}, {"match"=>{"FQUser"=>"<user>"}}]}}, "sort"=>["@timestamp"], "_source"=>["MSRASClientName", "FQUser", "UserName", "TunnelClientEndpt", "CallingStationID"]}}}

[2020-03-11T22:50:03,604][INFO][logstash.filters.elasticsearch] New ElasticSearch filter client {:hosts=>["http://10.122.151.127:9200"]} C:/Users/c070054/Downloads/logstash-6.8.6/vendor/bundle/jruby/2.5.0/gems/logstash-filter-fingerprint-3.2.1/lib/logstash/filters/fingerprint.rb:181: warning: constant ::Fixnum is deprecated

[2020-03-11T22:50:03,936][DEBUG][logstash.pipeline] output received {"event"=>{"ServiceType"=>nil, "EventTimestamp"=>nil, "TunnelMediumType"=>nil, "path"=>"/var/leitura/entrada_vpn_full.csv", "message"=>"\"<server>\",\"RAS\",11/24/2018,03:14:17,3,,\"<user>\",,,,,,,,,,,,,,,,,4,,48,\"311 1 <ip> 05/26/2018 16:09:57 100364\",,,,,,,,,\"100365\",,,,,,,,,,,,,,,,,,,,,,,,,\"Microsoft Routing and Remote Access Service Policy\",1,,,,\r", "MSRASClientName"=>nil, "RemoteServerAddress"=>nil, "AcctDelayTime"=>nil, "MSAcctEAPType"=>nil, "Source_CallingStationID"=>"<ip>", "AcctOutputPackets"=>nil, "FQUser"=>"<user_with_double_backslashes>", "PortLimit"=>nil, "AcctAuthentic"=>nil, "CallingStationID"=>nil, "AcctTerminateCause"=>nil, "UserName"=>nil, "ServiceName"=>"RAS", "MSCHAPDomain"=>nil, "TerminationAction"=>nil, "Date"=>"11/24/2018", "@timestamp"=>2018-11-24T05:14:17.000Z, "NASPortType"=>nil, "MSMPPEEncryptionTypes"=>nil, "NASIdentifier"=>nil, "PacketType"=>"3", "NASPort"=>nil, "TunnelPreference"=>nil, "AcctOutputOctets"=>nil, "type"=>"rras_log", "AcctTunnelConn"=>nil, "MSRASClientVersion"=>nil, "MSMPPEEncryptionPolicy"=>nil, "TunnelServerEndpt"=>nil, "MSCHAPError"=>nil, "Source_FQUser"=>"<user>", "Source_TunnelClientEndpt"=>"<ip>", "MSRASVersion"=>nil, "PolicyName"=>nil, "FramedProtocol"=>nil, "Time"=>"03:14:17", "TunnelType"=>nil, "Source_UserName"=>"<user>", "ComputerName"=>"<server>", "EAPFriendlyName"=>nil, "TunnelClientEndpt"=>nil, "Source_MSRASClientName"=>nil, "ClientVendor"=>nil, "SessionTimeout"=>nil, "AcctMultiSsnID"=>nil, "host"=>"ccssvitrlx033", "AcctSessionID"=>"100365", "TunnelAssignmentID"=>nil, "Class"=>"311 1 <ip> 05/26/2018 16:09:57 100364", "ProviderName"=>nil, "AcctSessionTime"=>nil, "AcctInputPackets"=>nil, "@version"=>"1", "TunnelPvtGroupID"=>nil, "AcctInterimInterval"=>nil, "ClientIPAddress"=>nil, "FramedIPAddress"=>nil, "ClientFriendlyName"=>nil, "IdleTimeout"=>nil, "MSRASVendor"=>nil, "ProxyPolicyName"=>"Microsoft Routing and Remote Access Service Policy", "MSAcctAuthType"=>nil, "NASIPAddress"=>nil, "CalledStationID"=>nil, "AuthenticationType"=>"4", "AcctInputOctets"=>nil, "ProviderType"=>"1", "CallbackNumber"=>nil, "AcctLinkCount"=>nil, "ReasonCode"=>"48", "ConnectInfo"=>nil, "AcctStatusType"=>nil}}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 8, 2020, 11:14pm UTC](https://discuss.elastic.co/t/es-lookups/220276/9 "2020-04-08T23:14:30Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.