topic Re: Using splunk to create and view table metadata in All Apps and Add-ons

Using splunk to create and view table metadata

sjanwity — Wed, 01 Oct 2014 15:15:49 GMT

I am completely new to Splunk!

I have a database where updates are performed in a very ad-hoc way: I delete records and insert a new record with the new values. I hook this up to another 'logging' database which records each insert/delete operation and the details of the affected record (e.g. the timestamp of the operation, the ID of the record, etc.) . I'd like to use splunk to group these update operations and view them - what field was changed when - , bearing in mind that I also do normal inserts and deletes.

I am aware that you can input SQL and use splunk to present the data, but how do I go about almost creating a new field by itself?

For example:

Table Customers:

Customer ID | Customer Name | Customer Address |
001 | John F | 213 Privet Drive
002 | Kyle A | 16 Gammon Road


Table Customers-History:

TIMESTAMP         | OPERATION | Customer ID | Customer Name | Customer Address
1-Dec-2010 09:52:1232| INSERT | 002       | Kyle A          | 10 Gammon Road
2-Dec-2010 09:54:9500| DELETE| 002         | Kyle A         | 10 Gammon Road
2-Dec-2010 09:54:9500| INSERT | 002         | Kyle A        | 16 Gammon Road
2-Dec-2010 09:55:9921| DELETE | 003         | Josh C        | 21 Drury Lane

In the above example, the 2nd and 3rd logs of the Customers-History table show an edit operation. I want splunk to go through and record all the changes to the Customers table, automatically grouping up the edit operations as well. How should I do this?

Re: Using splunk to create and view table metadata

aweitzman — Wed, 01 Oct 2014 15:57:45 GMT

What you want to do is quite doable, but if you're completely new to Splunk, then you should start with the tutorial. Your first order of business will be getting your logs into Splunk and ensuring that the fields that you want to appear are being extracted properly. Then your question about a search that will group update operations will be easier to answer.

Re: Using splunk to create and view table metadata

sjanwity — Wed, 01 Oct 2014 16:03:17 GMT

@aweitzman: sorry I should mention: I've already got the data into splunk, just unsure how to create the query.

Re: Using splunk to create and view table metadata

aweitzman — Wed, 01 Oct 2014 17:00:26 GMT

If the data is already in Splunk, then can you please edit your post to include some sample records and field values? That will make it far easier to figure out how to create the query you want.

Re: Using splunk to create and view table metadata

aweitzman — Thu, 02 Oct 2014 13:04:04 GMT

OK, now that you've supplemented your post with sample records, I noticed the "dbconnect" tag in your initial post. Since you're representing your data as tables rather than Splunk search results, it would appear that you are accessing this data entirely through dbquery instead of importing your "logging" database data into Splunk and having it indexed there. Is that the case?

I'm also unclear on what you mean by grouping. Do you just want the data from your logging database sorted by customer ID and time? Or something more than that?

Re: Using splunk to create and view table metadata

sjanwity — Thu, 02 Oct 2014 13:36:26 GMT

@aweitzman It seems I've completely misunderstood splunk...yes that is the case, I am viewing this table through dbquery. Yes I would like it imported on splunk, but my understanding was that I would use splunk to display this data first, then try to create a scheduler later to get data into splunk itself. I thought, though, that I could form a dbquery which indexes the database data automatically....is this not the case?

By 'grouping' I mean, have splunk see that the insert-delete operations are actually edit operations and present the information as such. For example on the data above splunk would show 2 records of 'changes' for Customer 'Kyle A': an insert on 1-Dec-2010 9:52 and an edit on 2-Dec-2010 9:54

Re: Using splunk to create and view table metadata

aweitzman — Thu, 02 Oct 2014 13:58:58 GMT

No, you can only use dbquery to get data out of your database, not into Splunk. To get your database data into Splunk, you need to configure a database input. See this page for details: http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Configuredatabasemonitoring

As for your grouping, what you want is to be able to take the DELETE-INSERT combination and interpret it as a single EDIT action, correct? If so, that can be done in Splunk using the transaction command. You would define a transaction as something that starts with a DELETE, ends with INSERT, has the same Customer ID value, and happens within a very short timespan. So something like:

main search goes here 
| transaction "Customer ID"  maxspan=1s maxevents=2 startswith="DELETE" endswith="INSERT"

See here for more details: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transaction

Re: Using splunk to create and view table metadata

sjanwity — Thu, 02 Oct 2014 15:33:38 GMT

@aweitzman My key is a compound key though (it has to get ID and transaction time so it doesn't mistake seperate independent insert-deletes as edits). When trying your snippet out I get a command="dbquery", A database error occurred: ORA-00933: SQL command not properly ended when appending the code to the end. Am I missing something? When you say 'main search goes here' what do you mean?

Re: Using splunk to create and view table metadata

aweitzman — Thu, 02 Oct 2014 16:43:09 GMT

By "main search goes here" I mean the search to pull your table into the Splunk search context. Like:

| dbquery search-to-get-the-Customers-History-table

The transaction command isn't part of your SQL query. It's a Splunk command designed to manipulate the data in Splunk. So what I meant was something like this:

| dbquery search-to-get-the-Customers-History-table | transaction "Customer ID" maxspan=1s maxevents=2 startswith="DELETE" endswith="INSERT"

Note the pipe between the dbquery clause and the transaction clause. It's to take the results of your first search (the dbquery part, where you get stuff out of your SQL database) and use it as the basis for manipulation by the second part (the transaction part, to extract the DELETE-INSERT pairs).

You really should read the tutorial.

Re: Using splunk to create and view table metadata

sjanwity — Fri, 03 Oct 2014 08:48:04 GMT

@aweitzman I did, just abit confused how the db connector works! I know the searches and the search commands but I am completely unsure as to how to use it on the db connector context. I thought at first that the dbconnector is a seperate app where you can simply combine SQL and splunk syntax together and it'll automatically help you query your database then display the data (with the data being stored on splunk as an interim), but I think from your messages this is not the case?

Re: Using splunk to create and view table metadata

aweitzman — Fri, 03 Oct 2014 15:03:12 GMT

You understand things perfectly. You use the dbquery command for your SQL syntax to get some results, and then you pipe those results through Splunk commands.

However, I must note I informed you incorrectly about transaction. I just realized that since dbquery returns a table rather than events, transaction won't work. (However, that just means your search should get you 0 results rather than an error.)

What that means for you is that your ultimate solution here is going to involve creating a database input, so that all of your logs are in Splunk natively. Then you'll be able to work on the data using the transaction command to get the answers you want.

(Just to be clear, using dbquery to get a table and then piping it through a Splunk command to get another table, or a chart, will work with many, many Splunk commands, but transaction is not one of them. Unfortunately, transaction is the capability you're asking for in your question, so you'll need to go the database input route to solve that problem.)

Re: Using splunk to create and view table metadata

sjanwity — Mon, 06 Oct 2014 08:45:32 GMT

By "creating a database input", which tutorial topic should I be looking at, and do you mean through dbconnect or native splunk? I'm confused because I don't know whether this is from a dbconnector perspective or a native splunk perspective (in fact I think that's the root of my problem - I jumped right into dbconnect before trying to understand splunk itself, so now I don't know what is unique to which perspective...)

Re: Using splunk to create and view table metadata

aweitzman — Mon, 06 Oct 2014 12:48:51 GMT

Database inputs are a dbconnect concept. Look here: http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Configuredatabasemonitoring

That said, understanding Splunk itself is key, especially understanding the ways in which it differs from SQL. In any Splunk setup there is native Splunk data you can see by doing the search index=_internal. Exercise the concepts you learned in the tutorial by spending some time doing searches on this data to better "get" it, and then apply what you know about dbconnect to bring more data into the system to work with.

Re: Using splunk to create and view table metadata

sjanwity — Mon, 06 Oct 2014 16:51:26 GMT

I think I've gotten the data in - I've posted a screenshot of my dbconnect app and a sample code sql query I tried (with meaningful data blotted out, sorry!): http://pbrd.co/1q4a4Sa

What would my next steps be - use transactions? Note that a data model has already been built
Would this task be easier editing the splunk config files rather than on the dbconnector itself?

Re: Using splunk to create and view table metadata

aweitzman — Mon, 06 Oct 2014 17:07:30 GMT

Your picture shows you doing a database query within the DB Connect app, which appears to be the functional equivalent of running dbquery from the search box. So this will not help you.

Once you have created a database input as described in the link above, you should be able to search your data entirely within Splunk, without using dbquery or the Database Query part of the DB Connect app. Once this is established, you should be able to run regular Splunk commands (like transaction) on the raw events and get useful responses.

Re: Using splunk to create and view table metadata

sjanwity — Mon, 06 Oct 2014 17:26:07 GMT

Oh alright, I think I understand now...

Wouldn't this be easier defining in the config files, and if so, I'm guessing on outputs.conf? Also, if I get the data into splunk, how do I search by a specific table? Or will that be indexed as one of the fields, i.e. sourcetype?

Re: Using splunk to create and view table metadata

aweitzman — Tue, 07 Oct 2014 12:47:37 GMT

Wouldn't what be easier, exactly? Creating a database input? I think at the level you're at right now, using the UI to help you as much as you can is your best bet.

There aren't really tables in Splunk the way you're thinking about them. It's a very different mindset than SQL. You can use the sourcetype to segregate your data into meaningful sections. It would probably work for you at this point to assign a different sourcetype to each table/view you input. (Over time, you might realize that you may get more performant searches if some tables/views were grouped together into a single sourcetype, but you can attack that problem later.)

Re: Using splunk to create and view table metadata

sjanwity — Mon, 28 Sep 2020 17:50:38 GMT

Thanks for all your help so far! I've gotten the data in splunk and gotten transactions to work. My splunking adventure is almost complete, however the data is not displaying as I want it, though. I've also changed the output abit: I think it is better to see the changes on a field as well as grouping update events. My transaction command is currently
transaction TYPE_NAME FIELD_NAME OBJECT_KEY
but it's not displaying the data as I want.

Currently the data is displayed like this:
http://i.stack.imgur.com/RZwib.jpg

But in reality the database is this:
http://i.stack.imgur.com/ulV2A.jpg

As you can see, the initial insert has completely disappeared and the last delete as well.

Why is the last delete event and initial insert event missing? It should group the insert and delete event that occured on 11-May-11 but if I use maxspan then the initial insert and subsequent delete becomes a seperate event. How should I do this, and how do I make splunk rename the INSERT-DELETE event to an UPDATE?

Re: Using splunk to create and view table metadata

aweitzman — Thu, 09 Oct 2014 16:56:16 GMT

The last delete and initial insert are missing because they're not part of your defined transactions. If you want to include them, add keeporphans=true to your transaction command.

As for renaming the event, create a new field based on the contents of the OPERATION field and use that instead. Add the following to the end of your search:

| eval TOTALOPERATION=if(OPERATION=="INSERT" AND OPERATION=="DELETE","UPDATE",OPERATION)

This creates a new field TOTALOPERATION which contains either UPDATE if your original OPERATION value contains both INSERT and DELETE, or the original value of the OPERATION field if it doesn't. (I know it seems strange to test for equality against two different values in the same logical operation, but it works in Splunk because the field is multivalued.)

Re: Using splunk to create and view table metadata

sjanwity — Fri, 10 Oct 2014 09:36:23 GMT

Thanks for the TOTALOPERATIONfield, it worked nicely! However keeporphans is still returning me this result:

http://i.stack.imgur.com/ulV2A.jpg

My transaction command is currently | transaction TYPE_NAME FIELD_NAME OBJECT_KEY keeporphans=true |, am I doing something wrong?

It's also not showing the operation on records which were changed multiple times:
http://i.stack.imgur.com/NgaHw.jpg