# Oracle Audit Trail

**URL:** <https://discuss.elastic.co/t/oracle-audit-trail/89950>\
**Category:** Logstash\
**Created:** [June 19, 2017, 2:30pm UTC](https://discuss.elastic.co/t/oracle-audit-trail/89950 "2017-06-19T14:30:59Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![GambitK](https://avatars.discourse-cdn.com/v4/letter/g/47e85d/32.png) [@GambitK](https://discuss.elastic.co/u/GambitK)\
**Post date:** [June 19, 2017, 2:30pm UTC](https://discuss.elastic.co/t/oracle-audit-trail/89950/1 "2017-06-19T14:30:59Z")

</div>

Hello, I'm trying to get oracle audit trail using logstash, what options I have that could help me achieve that.

I wanted to use audit trail to OS but the columns sql\_bind, sql\_text are not written so I only have two options: DB, EXTENDED and XML, EXTENDED has anyone here had any experience with getting audit logs out of oracle using logstash or another open source tool.

Thanks.

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [June 22, 2017, 5:25am UTC](https://discuss.elastic.co/t/oracle-audit-trail/89950/2 "2017-06-22T05:25:43Z")

</div>

People here know Logstash but typically not Oracle. If you explain how Oracle audit logs are extracted in the general case we can help you figure out how to do it with Logstash.

---

<div class="post-metadata">

**Author:** ![GambitK](https://avatars.discourse-cdn.com/v4/letter/g/47e85d/32.png) [@GambitK](https://discuss.elastic.co/u/GambitK)\
**Post date:** [July 4, 2017, 12:45pm UTC](https://discuss.elastic.co/t/oracle-audit-trail/89950/3 "2017-07-04T12:45:30Z")

</div>

This an example of the format of the file. I've been trying a combination of multiline with xml filter but haven't been able to.

> <https://gist.github.com/GambitK/38c9894018672fef8f9d1955a962fb30>

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [July 4, 2017, 1:07pm UTC](https://discuss.elastic.co/t/oracle-audit-trail/89950/4 "2017-07-04T13:07:40Z")

</div>

If you show us what you have so far it'll be easier to help.

---

<div class="post-metadata">

**Author:** ![GambitK](https://avatars.discourse-cdn.com/v4/letter/g/47e85d/32.png) [@GambitK](https://discuss.elastic.co/u/GambitK)\
**Post date:** [July 4, 2017, 1:12pm UTC](https://discuss.elastic.co/t/oracle-audit-trail/89950/5 "2017-07-04T13:12:39Z")

</div>

Here is my current config:

```
input {
  file {
    type => "oracle_listener"
    path => "/opt/app/oracle/admin/tcrdj/adump/*.xml"

    codec => multiline {
      pattern => "<AuditRecord>"
      negate => true
      what => "previous"
    }

    start_position => "beginning"
    sincedb_path => "/opt/logstash/oracle_listener_sincedb"
  }
}

filter {
    mutate {
      gsub => ["message", "\u0000", ""]
      gsub => ["message", "\u0000\n", ""]
      #gsub => ["message", "\xD3N", "ON"]
      gsub => ["message", ">=", "GE"]
      gsub => ["message", "<>", "NE"]
      gsub => ["message", "<=", "LE"]
      gsub => ["message", " < ", "LT"]
      gsub => ["message", " > ", "GT"]
    }

    xml {
      source => "message"
      target => "xmlresult"
    }
}

output {
    stdout {
      codec => "rubydebug"
    }
}
```

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [July 4, 2017, 1:38pm UTC](https://discuss.elastic.co/t/oracle-audit-trail/89950/6 "2017-07-04T13:38:55Z")

</div>

Okay, that doesn't look too bad. What do you get when you run that configuration?

---

<div class="post-metadata">

**Author:** ![GambitK](https://avatars.discourse-cdn.com/v4/letter/g/47e85d/32.png) [@GambitK](https://discuss.elastic.co/u/GambitK)\
**Post date:** [July 4, 2017, 2:00pm UTC](https://discuss.elastic.co/t/oracle-audit-trail/89950/7 "2017-07-04T14:00:25Z")

</div>

Seems like sometimes there's an unwanted close tag that breaks standard xml, it happens because the files are generated and may be written again if the file exists.

Here's the output using the input and conf that I have already posted.

> <https://gist.github.com/GambitK/850804b4e6827ab0034528583e2c3014>

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [July 4, 2017, 2:31pm UTC](https://discuss.elastic.co/t/oracle-audit-trail/89950/8 "2017-07-04T14:31:08Z")

</div>

Yeah, I guess you're picking up the final at the end of each file. I suggest you parse each file in one swoop and use a split filter after the xml filter to splice the field containing the list of AuditRecord entries so you get one AuditRecord per event.

---

<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:** [August 1, 2017, 2:31pm UTC](https://discuss.elastic.co/t/oracle-audit-trail/89950/9 "2017-08-01T14:31:16Z")

</div>

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