Child pages
  • Audit To Database Plugin
Skip to end of metadata
Go to start of metadata

Plugin Information

View Audit to Database on the plugin site for more information.

Download latest release from jenkins-ci.org

Download latest continuous integration release from CloudBees 

Plugin Info

This plugin provides database audit functionality to Jenkins. It allows recording build information to database, including the build parameters (if any), the node where the build is executed, and the user who started the build.
Jenkins activity is already being stored in XML files. However, historic activity can be easily lost due to configuration (e.g. keep only the latest "n" builds), and is not easy to report off. As Jenkins is already much more than just a continuous integration platform, this plugin will help those organisations that have strict audit and reporting requirements. For example, if a job is meant to deploy artifacts to a production environment, organisations may want to record that job's activity and report off historical data for compliance purposes, showing when the job was executed, by whom, with what parameters, and on which Jenkins slave.

Installation

Database connections are established via JDBC, so you have to ensure a valid JDBC driver for your database can be found by this plugin. This can be accomplished in two ways:

Use the Jenkins classpath

If Jenkins is running as a standalone application, you can put the JDBC driver package in the war/WEB-INF/lib directory. If Jenkins is running inside a J2EE container (e.g. Tomcat) you can use the container's classpath instead (consult the container's documentation for details).

Use the plugin's classpath

Regardless of whether jenkins is running as a standalone application or as a web application inside a J2EE container, you can put the JDBC driver package in $JENKINS_HOME/plugins/audit2db/WEB-INF/lib. This directory will be created the first time you run the plugin inside Jenkins, so if you can't see it (and assuming you have actually already installed the audit to database plugin), then try restarting Jenkins.

MS SQL Server and Integrated Authentication

If you want to use MS SQL Server with Integrated Authentication, you might want to check this article from the MSDN for troubleshooting a common situation.

This plugin has been tested with the following JDBC drivers:

org.hsqldb.jdbc.JDBCDriver
oracle.jdbc.driver.OracleDriver
com.microsoft.sqlserver.jdbc.SQLServerDriver

JDBC Driver Redistribution

This plugin does NOT ship with any JDBC drivers. Yes, it may be useful to ship supported drivers with the plugin, but it can quickly become a bit of a headache to manage redistribution licenses so we'll keep it simple instead... or at least until we get the time (and will) to study the relevant licensing terms.

Usage

Before you can use the plugin you need to set up the audit database. In the Jenkins global configuration page, enter the JDBC connection details for your audit database and test the connection.

If the connection is successful, click on the Advanced button and the GenerateDDL button will appear. This will allow you to generate the data definition script to set up the audit database.

The DDL is generated as a temporary file in the plugin's home folder, and its contents displayed. The temporary file gets deleted right away. Because of this, the account used to run Jenkins must have full rights on the plugin's folder. This should already be the case, but it's something to check if an error occurs during the DDL generation. In any case, the audit database schema is given below on this page.

If you have any DBAs, it is a good idea to pass this script over to them now. It is also a good idea to discuss in detail your audit reporting requirements with your DBAs, so that they can configure the appropriate indexes on the audit tables and help you build your reporting queries.

Once your audit database is ready, you can enable this plugin in the Post-build Actions section of the job configuration. This step might look slightly differently from the screenshots below, depending on the version of Jenkins being used.

The Audit Reports

This plugin comes with some basic reports to help the audit activity.

By default, only users with administrative rights are able to access the reports. This can be changed by using the Jenkins configuration page and enabling Matrix-based security.

The Audit Database Schema

If you need to build other reports, then you can build them outside of Jenkins by queryng the audit database. The audit database schema is extremely simple and becomes very clear once you generate the DDL script. It consists of 3 tables:

JENKINS_BUILD_DETAILS

Field Name

Type

Description

ID

String

Primary Key.

NAME

String

The name of the project that the build refers to.

FULLNAME

String

Full name of the build, includes the project name and build number. E.g. "TestProj #44".

NODE_URL

String

Foreign key on JENKINS_BUILD_NODE.

STARTDATE

Date

The build's start date.

ENDDATE

Date

The build's end date.

DURATION

Number

The build's duration in milliseconds.

USERID

String

The id of the user who started the build (NULL for Anonymous).

USERNAME

String

The name of the user who started the build.

RESULT

String

The build result.

JENKINS_BUILD_NODE

Field Name

Type

Description

URL

String

Primary Key.

NAME

String

The internal name of the node where builds get executed.

DISPLAYNAME

String

The name of the node as displayed to the user. This is not always the same as the node's internal name. For example, the master's name is blank, but its displayname is 'master'.

MASTERHOSTNAME

String

The hostname of the master that owns the build node.

MASTERADDRESS

String

The IP address of the master that owns the build node.

LABEL

String

The label associated with this node in Jenkins

DESCRIPTION

String

The description of this node in Jenkins

JENKINS_BUILD_PARAMS

Field Name

Type

Description

ID

String

Primary Key

NAME

String

The parameter name

VALUE

String

The parameter value

BUILDDETAILS_ID

String

Foreign key on JENKINS_BUILD_DETAILS


TODO List

  • Support JNDI datasource.
  • Write more audit reports to display in Jenkins.

Known Issues


Changelog

Version 0.5

Fixed a bug in Jobs By Date report that prevented users from applying filter criteria.

Version 0.4

Added Jobs By Param report to show jobs that have been executed with the same parameter value.

Version 0.3

Now scrambling datasource password in Jenkins configuraton XML file.

Added Jobs By Date report to show jobs executed between two dates (defaults to current month activity).

Added Audit Reports page to list all available reports.

Added audit reports RUN permission option in matrix-based Jenkins security.

Version 0.2

Added master hostname and IP address in the build node details.

Added build result in the build details.

Version 0.1

Initial working version.

16 Comments

  1. I don't understand how to exploit your database. Maybe, it is a first step into a more global solution.

    From an abstract point of view of continuous integration, Jenkins information are aimed at providing to team developers the current projects status. Therefore, Jenkins information are volatile and are not designed to give trend with historical of several days, weeks and months (however, there are dedicated tools to persist build result metrics in order to have some trends and more).

    What are your use case for using this plugin?

    1. Sorry about the delay in replying.

      If Jenkins is used exclusively for CI, then I agree that persisting build information is only of marginal value. However, when you use Jenkins also as a deployment tool, whether it is for continuous delivery or simply to automate deployments to secured and controlled environments, then you might want to track what is being deployed when, where, by whom, using what parameters, etc. This is especially useful in highly controlled/regulated organisations where auditing changes to controlled environments is of paramount importance.

      From an Auditor's perspective in these organisations, it would be important to run a report (say once a month), check it against the expected activity, print it out, stamp it, sign it, have it counter-signed by another auditor or a manager, then file it.

      If this information is in a bunch of XML files scattered around the Jenkins file system, then it becomes quite difficult to report off it. The problem is also exacerbated by the fact that those XML files are not likely to live there forever, so data persistence to a relational database makes things a lot easier.

      M.

  2. Could you have a look at this.

    ERROR: Publisher org.jenkins.plugins.audit2db.internal.DbAuditPublisherImpl aborted due to exception
    org.springframework.dao.InvalidDataAccessResourceUsageException: could not load an entity: org.jenkins.plugins.audit2db.internal.model.BuildDetailsImpl; SQL select builddetai0_.id as id15_1_, builddetai0_.duration as duration15_1_, builddetai0_.endDate as endDate15_1_, builddetai0_.fullName as fullName15_1_, builddetai0_.name as name15_1_, builddetai0_.node_url as node10_15_1_, builddetai0_.result as result15_1_, builddetai0_.startDate as startDate15_1_, builddetai0_.userId as userId15_1_, builddetai0_.userName as userName15_1_, buildnodei1_.url as url17_0_, buildnodei1_.description as descript2_17_0_, buildnodei1_.displayName as displayN3_17_0_, buildnodei1_.label as label17_0_, buildnodei1_.masterAddress as masterAd5_17_0_, buildnodei1_.masterHostName as masterHo6_17_0_, buildnodei1_.name as name17_0_ from JENKINS_BUILD_DETAILS builddetai0_ left outer join JENKINS_BUILD_NODE buildnodei1_ on builddetai0_.node_url=buildnodei1_.url where builddetai0_.id=?; nested exception is org.hibernate.exception.SQLGrammarException: could not load an entity: org.jenkins.plugins.audit2db.internal.model.BuildDetailsImpl
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:635)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
    at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
    at org.springframework.orm.hibernate3.HibernateTemplate.get(HibernateTemplate.java:512)
    at org.springframework.orm.hibernate3.HibernateTemplate.get(HibernateTemplate.java:506)
    at org.jenkins.plugins.audit2db.internal.data.BuildDetailsHibernateRepository.getBuildDetailsById(BuildDetailsHibernateRepository.java:121)
    at org.jenkins.plugins.audit2db.internal.data.BuildDetailsHibernateRepository.getBuildDetailsForBuild(BuildDetailsHibernateRepository.java:307)
    at org.jenkins.plugins.audit2db.internal.DbAuditPublisherImpl.perform(DbAuditPublisherImpl.java:108)
    at hudson.tasks.BuildStepMonitor$1.perform(BuildStepMonitor.java:19)
    at hudson.model.AbstractBuild$AbstractBuildExecution.perform(AbstractBuild.java:804)
    at hudson.model.AbstractBuild$AbstractBuildExecution.performAllBuildSteps(AbstractBuild.java:779)
    at hudson.model.Build$BuildExecution.cleanUp(Build.java:192)
    at hudson.model.Run.execute(Run.java:1587)
    at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:46)
    at hudson.model.ResourceController.execute(ResourceController.java:88)
    at hudson.model.Executor.run(Executor.java:236)
    Caused by: org.hibernate.exception.SQLGrammarException: could not load an entity: org.jenkins.plugins.audit2db.internal.model.BuildDetailsImpl
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.loadEntity(Loader.java:1957)
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:86)
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:76)
    at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3270)
    at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:496)
    at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:477)
    at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:227)
    at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:285)
    at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:152)
    at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:1080)
    at org.hibernate.impl.SessionImpl.get(SessionImpl.java:997)
    at org.hibernate.impl.SessionImpl.get(SessionImpl.java:990)
    at org.springframework.orm.hibernate3.HibernateTemplate$1.doInHibernate(HibernateTemplate.java:519)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
    ... 14 more
    Caused by: java.sql.SQLException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:754)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:813)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1051)
    at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1156)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3460)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1869)
    at org.hibernate.loader.Loader.doQuery(Loader.java:718)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
    at org.hibernate.loader.Loader.loadEntity(Loader.java:1953)
    ... 27 more

    1. Sorry about the late reply. Have you already fixed this? The error is in the stacktrace "ORA-00942: table or view does not exist". The database needs to be set up first.

  3. This plugin appears to be working but the Jenkins jobs are marking it as a failure:

    BUILD SUCCESSFUL
    Total time: 0 seconds
    Archiving artifacts
    Build step 'Audit job info to Database' marked build as failure

    Here is the catalina.out

    Apr 24, 2013 10:48:25 AM org.jenkins.plugins.audit2db.internal.DbAuditPublisherImpl perform
    SEVERE: null
    java.lang.UnsupportedOperationException
            at hudson.tasks.BuildStepCompatibilityLayer.perform(BuildStepCompatibilityLayer.java:95)
            at hudson.tasks.BuildStepCompatibilityLayer.perform(BuildStepCompatibilityLayer.java:59)
            at org.jenkins.plugins.audit2db.internal.DbAuditPublisherImpl.perform(DbAuditPublisherImpl.java:120)
            at hudson.tasks.BuildStepMonitor$1.perform(BuildStepMonitor.java:19)
            at hudson.model.AbstractBuild$AbstractBuildExecution.perform(AbstractBuild.java:802)
            at hudson.model.AbstractBuild$AbstractBuildExecution.performAllBuildSteps(AbstractBuild.java:774)
            at hudson.model.Build$BuildExecution.cleanUp(Build.java:192)
            at hudson.model.Run.execute(Run.java:1620)
            at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:46)
            at hudson.model.ResourceController.execute(ResourceController.java:88)
            at hudson.model.Executor.run(Executor.java:237)
    Apr 24, 2013 10:48:25 AM org.jenkins.plugins.audit2db.internal.DbAuditPublisherImpl perform

    SEVERE: null

    java.lang.UnsupportedOperationException

            at hudson.tasks.BuildStepCompatibilityLayer.perform(BuildStepCompatibilityLayer.java:95)

            at hudson.tasks.BuildStepCompatibilityLayer.perform(BuildStepCompatibilityLayer.java:59)

            at org.jenkins.plugins.audit2db.internal.DbAuditPublisherImpl.perform(DbAuditPublisherImpl.java:120)

            at hudson.tasks.BuildStepMonitor$1.perform(BuildStepMonitor.java:19)

            at hudson.model.AbstractBuild$AbstractBuildExecution.perform(AbstractBuild.java:802)

            at hudson.model.AbstractBuild$AbstractBuildExecution.performAllBuildSteps(AbstractBuild.java:774)

            at hudson.model.Build$BuildExecution.cleanUp(Build.java:192)

            at hudson.model.Run.execute(Run.java:1620)

            at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:46)

            at hudson.model.ResourceController.execute(ResourceController.java:88)

            at hudson.model.Executor.run(Executor.java:237)

    I originally had issues with some values that were required not to be 'null' in the database.  I had our DBA's remove that constraint and it appears the reports are working.  Is there any way to output these to a PDF or some other format?  Any ideas on why this is erroring out?

    Thanks!

    Rob

    1. Yes I have seen the message "Build step 'Audit job info to Database' marked build as failure" too, but I have not figured out where it originates exactly. Given the fact that it still works and generates a successful build, I have "parked" this as a low priority issue so I'll get into it when the circumstances will allow.

      Regarding different output formats for the reports, I encourage you to file an enhancement on GitHub, so that other users may flag it as useful. As of now, there are no plans to output the reports to any other format using this plugin. What I might suggest at this point in time is to use a free PDF writer to produce PDF reports from the browser (File -> Print). See http://www.techsupportalert.com/best-free-pdf-writer.htm for some ideas.

  4. Hi,

    I have Jenkins (Master) in Linux server and slave agents (one in Windows and another in Linux ).

    I have configured the plugin and verified the connection setup. The plugin is working if the job is executed in Slave agents (Linux and Windows).
    It doesn't work if the Job is executed in Master machine (Linux). Below is the error message when the job is executed in Master.

    Started by user anonymous
    EnvInject - Loading node environment variables.
    Building on master in workspace /sanfs/mnt/vol01/local/.hudson/jobs/Test_Param_Job/workspace
    ERROR: Publisher org.jenkins.plugins.audit2db.internal.DbAuditPublisherImpl aborted due to exception
    java.lang.NullPointerException
    at org.jenkins.plugins.audit2db.internal.DbAuditPublisherImpl.perform(DbAuditPublisherImpl.java:110)
    at hudson.tasks.BuildStepMonitor$1.perform(BuildStepMonitor.java:19)
    at hudson.model.AbstractBuild$AbstractBuildExecution.perform(AbstractBuild.java:717)
    at hudson.model.AbstractBuild$AbstractBuildExecution.performAllBuildSteps(AbstractBuild.java:692)
    at hudson.model.Build$BuildExecution.cleanUp(Build.java:192)
    at hudson.model.Run.execute(Run.java:1546)
    at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:46)
    at hudson.model.ResourceController.execute(ResourceController.java:88)
    at hudson.model.Executor.run(Executor.java:236)

    I see the above error in my local standalone Jenkins as well. Please let me know if anyone faced this issue.

    Regards,
    Jude

    1. https://github.com/jenkinsci/audit2db-plugin/blob/master/src/main/java/org/jenkins/plugins/audit2db/internal/DbAuditPublisherImpl.java)

      The plugin works by storing a build definition in the database during the "prebuild" stage, and then storing the results in the "perform" stage.

      The line throwing the exception is trying to set the build result in a data object during the "perform" stage.

      However, it looks like the plugin is unable to find your build definition in the database (which should have already been saved previously, during the "prebuild" stage).

      Is the data actually being populated in the database?

      What JDBC driver are you using?

      Can you enable FINE level logging for org.jenkins.plugins.audit2db?

    2. I see this as well.

      I have master on linux, slave running example job on Windows.

      In addition, the plugin fails to generate the ddl - I have verified that the account running the master has full access (read & write) to the plugin folder - so I have manually created the schema (SQLServer 2012)

  5. Hi - Does this plugin by default have all the three tables in DB?

    How about, if I just need to have one table and only few details like JOB_NAME and BUILD_NUMBER and some other parameters which are generated during Build like COVERAGE_PERCENTAGE?

    Thanks!!!

    1. Hi.

      Please take a look at the Usage section on this page.

      Also, if you want to change db schema you may file an enhancement request providing a detailed use case.

      Hope this helps.

  6. Hello,

    I think the schema should be further normalized. Split up the Job from the Builds.

    JENKINS_JOB

    ID

    String

    Primary Key.

    NAME

    String

    The name of the project that the build refers to.

     

     

     

    JENKINS_BUILD_DETAILS

    JENKINS_JOB_ID  String  Foreign key on JENKINS_JOB

    BUILD_NUMBER Number The build number

    NODE_URL

    String

    Foreign key on JENKINS_BUILD_NODE.

    STARTDATE

    Date

    The build's start date.

    ENDDATE

    Date

    The build's end date.

    DURATION

    Number

    The build's duration in milliseconds.

    USERID

    String

    The id of the user who started the build (NULL for Anonymous).

    USERNAME

    String

    The name of the user who started the build.

    RESULT

    String

    The build result.

  7. i am trying connect Oracle DB using this plugin. connection to Oracle DB is successful. but still i am getting below error. 

    ERROR: Publisher org.jenkins.plugins.audit2db.internal.DbAuditPublisherImpl aborted due to exceptionjava.lang.NullPointerException
    at org.jenkins.plugins.audit2db.internal.DbAuditPublisherImpl.perform(DbAuditPublisherImpl.java:110)
    at hudson.tasks.BuildStepMonitor$1.perform(BuildStepMonitor.java:20)
    at hudson.model.AbstractBuild$AbstractBuildExecution.perform(AbstractBuild.java:772)
    at hudson.model.AbstractBuild$AbstractBuildExecution.performAllBuildSteps(AbstractBuild.java:736)
    at hudson.model.Build$BuildExecution.cleanUp(Build.java:192)
    at hudson.model.Run.execute(Run.java:1786)
    at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:43)
    at hudson.model.ResourceController.execute(ResourceController.java:88)
    at hudson.model.Executor.run(Executor.java:234)
    Finished: FAILURE

    please do let me know if any extra modification required while connecting to Oracle using Audit2DB.

    1. Have you fixed this? I am also getting same error :(

      1. Any chance you were able to fix the above error?

  8. With regards to the NullPointerException raised when using the Oracle DB driver. You might want to check this known issue out. 

    https://github.com/jenkinsci/audit2db-plugin/issues/9

    I had to modify DDL of the JENKINS_BUILD_NODE table, so that instead of the "name" field being "varchar2(255 char) not null" it would be "name varchar2(255 char)".  I have removed the constraint in the DDL below.

    create table JENKINS_BUILD_NODE (

            url varchar2(255 char) ,

            description varchar2(255 char),

            displayName varchar2(255 char) not null,

            label varchar2(255 char),

            masterAddress varchar2(255 char) not null,

            masterHostName varchar2(255 char) not null,

            name varchar2(255 char),

            primary key (url)

        );

    Good luck!