Splunk DB Connect V3 - How create Automated / Programmatic creation of connections and inputs

[gjanders]

Please note that I'm answering this question in addition to posting it, if you find the answer useful please feel free to vote/award points!

---

The problem

After upgrading from Splunk DB Connect V2 to Splunk DB Connect V3 I was disappointed to find that some of the previously useful REST API's I was using to programatically create new connections/data inputs were no longer functional.

After logging a support ticket I was advised these were never supported, and therefore my request is now an "enhancement".

After quite a bit of work I did find a way to automate the DB Connect V3, it is likely unsupported by the official application but it works and provides a nice workaround until they officially support automation via REST API or similar.

Solution

Please see the answer below, comments/better solutions are welcome!

[ehudb]

You can add new Identities with encryption in DB Connect 3.x:
Use the REST endpoint:
https://splunkserver:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/dbxproxy/identities

Example:
curl -k -X POST -u admin:password https://splunkserver:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/dbxproxy/identities -d "{\"name\":\"myuser\",\"username\":\"myuser\",\"password\":\"mypassword\"}"

[sloshburch]

Anyone spot where this is documented? I've not been able to find it in the latest official docs and I was hoping to add the cross reference for posterity.

[gjanders]

It is not documented in the current documentation, you can find it mentioned in the web/rest configuration files of the application which is where I suspect it may have been found originally.

I have also edited/updated my main post to reference these examples as they work in my testing and are a better solution than my previous attempt...also refer to the reply from jcoates in this thread!

I believe offering a documented REST API for automation of any Splunk application like this should be considered a best practice. It should be fairly obvious that when creating database connections / inputs that may need automation (for larger environments)

[gjanders]

That works, thankyou!

[gsrivastava]

Now a similar endpoint is also available for creating connections -
https://splunkserver:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/dbxproxy/connections

We do not need to use a workaround of editing the db_connections.conf file and then triggering reloads to the server.

[gjanders]

Updated the main post to include this!

[gsrivastava]

https://splunkserver:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/dbxproxy/inputs

Similar REST endpoint in working fine for creating DB inputs as well.

[gjanders]

Does it work better than https://localhost:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/inputs ?
Can you skip creating the checkpoint file et cetera? If so I'll update the main post

If you have an example that's even better 🙂

[gsrivastava]

Yes we can skip checkpoint file creation and even admin server and inputs reload.

All we need to give checkpoint value in proper JSON format while hitting the REST endpoint -
https://splunkserver:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/dbxproxy/inputs

Here is a sample JSON for creating the input -

{
        "name": "ABCD",
        "query": "select * from ABCD",
        "interval": "*17 * * * *",
        "index": "test",
        "mode": "rising",
        "connection": "abcd",
        "rising_column_index": 1,
        "timestamp_column_index": 1,
        "timestampType": "dbColumn",
        "sourcetype": "abcd",
        "checkpoint": {"value":"2018-03-22 00:00:00.000","appVersion":"3.1.1","columnType":93,"timestamp":"2018-03-22T11:06:11.000+05:30"}
    }

[gjanders]

Updated the main post & up-voted your post, thankyou!

[gsrivastava]

Its working fine, thank you!

[jcoates_splunk]

Cool. FWIW, we don't document this yet because it's not stable, as you've found. Glad it worked for you!

[gjanders]

Yes, I would like to see future verisons include automation by default as there is rarely a use case where we don't want some level of automation within an application!

[gjanders · Accepted solution]

The solution I have managed to use for connections/database inputs is below, improved solutions are welcome. If you are using DB Connect V2 you might want to try the REST API solution I found https://answers.splunk.com/answers/452618/how-do-i-use-the-restful-webservices-to-setup-new.html#ans... note that this solution does not work for DB Connect V3!

EDIT: 2018-03-20 updated to include comments from ehudb and gsrivastava regarding a new REST endpoint in DB Connect 3.x

---

Identities

Quoting the answer in this thread from ehudb, use the REST endpoint:
https://localhost:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/dbxproxy/identities

Example

curl -k -X POST -u admin:changeit https://localhost:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/dbxproxy/identities -d "{\"name\":\"myuser\",\"username\":\"myuser\",\"password\":\"mypassword\"}"

---

Connections

The workaround solution of editing the db_connections.conf file and then trigger a get request to:
https://localhost:8089/servicesNS/nobody/splunk_app_db_connect/configs/conf-db_connections/_reload

Is no longer required, as per the comments by gsrivastava you can use:
https://localhost:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/dbxproxy/connections

---

Database Inputs

Quoting/paraphrasing the comments from gsrivastava, the URL of:
https://localhost:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/dbxproxy/inputs

Can be used to create DB connect inputs

JSON data is required, for example:

{
"name": "ABCD",
"query": "select from ABCD",
"interval": "17 ",
"index": "test",
"mode": "rising",
"connection": "abcd",
"rising_column_index": 1,
"timestamp_column_index": 1,
"timestampType": "dbColumn",
"sourcetype": "abcd",
"checkpoint": {"value":"2018-03-22 00:00:00.000","appVersion":"3.1.1","columnType":93,"timestamp":"2018-03-22T11:06:11.000+05:30"}
}

[clamarkv]

This post has been super helpful, and in the interests of making this easier for others, i've created some examples for use with the VS Code HTTP rest client.

https://gist.github.com/mark-vandenbos/c550d3f29f6e5991d4c13a5c75c8e358

thanks!

[sandrosov_splun]

Hi @gjanders ,

The Connections section remains unclear for v3, let's add some clarity.

By accessing the following endpoint, we will get an JSON array with all the connections:

curl -X GET -k -u admin:adminadmin https://localhost:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/dbxproxy/connections

Based on the output format we could add our own definitions via POST requests. e.g.

curl -X POST  -H "Content-Type: application/json" -d  @body.json -k -u admin:changeme https://localhost:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/dbxproxy/connections

----
body.json
{"name": "mysql_TEST",
"host": "host1_db01_1",
"port": 3306,
"jdbcUseSSL": false,
"readonly": false,
"useConnectionPool": true,
"disabled": false,
"connection_type": "mysql",
"database": "teradata",
"identity": "admin",
"customizedJdbcUrl": null,
"fetch_size": 100000,
"informixserver": null}

The successful answer will be a HTTP 200 reply with all the settings for newly created connection in the JSON format.

[mchristopherson]

Is there an endpoint for updating an existing identity - or for deleting one?  We are working on an ansible module to make this easy for our DBA's but currently short of deleting the contents out of config files, reloading, and then adding again I am not finding a way to update an identity (changing password) or delete a no longer needed identity.

When we send a post for an already existing identity we get a 409 response.

The same would go for connections and Inputs but we have not gotten to that part of the puzzle yet.

Edit: Got around our issues was not hitting the name of each object after creation /facepalm.

[JorgeFT]

Hi!

How did you managed to make it work?
I'm struggling with this right now, trying to update the identities passwords but I'm getting "Splunkd error: HTTP 409 -- An object with name=test_identity already exists".

I'm lost here 😞

Thank you and regards!

[mchristopherson]

We did not actually find a way to update attributes (including passwords) in the traditional sense with identities.  The workaround we used was to delete the identity and re-create it.  Updating connections and inputs works properly.

So far deleting the identity and recreating it has not caused any issues for us.  The DB Connect connections reference the Stanza name of the Identity not some UID that gets generated.  In our experience this has caused 0 issues for when changing the password on an account.  It could cause an issue if in the split second the account is deleted and being re-added the job referencing it tries to fire off - but it should fire off successfully on its next run.  We have not run into that corner case or tested specifically for it.

Since we wrote an Ansible module - we just added an extra parameter called force_recreation that by default is set to false - but we set to True for resetting of passwords.

[bazman]

I found a solution to edit connections and identities and put it here.   

I hope it will be useful to you.