topic Re: create table from database schema in Splunk Search

create table from database schema

indeed_2000 — Thu, 28 Nov 2019 19:04:34 GMT

hi
i have database schema, and want to extract a table like in picture.
i try to use regular expression but it's not work, explain here; https://answers.splunk.com/answers/786057/field-extract-1.html
and field extraction of splunk is so limited because could not accept multi value and special character!

any recommendation ?

primary key (code)  constraint "informix".pk_code

create index "informix".account_index on "informix".account
    (pu_date) using ct  in indexdbs;/create 

create unique index "informix".account_uidx on "informix".account
    (uno,sub_no) using ct in indexdbs;

create trigger "informix".accounttrigger insert on "informix"

alter table "informix".account add constraint (foreign key
    (account_fk) references "informix".etc
     constraint "informix".acc_type);



create table "informix".customer

primary key (id)  constraint "informix".pk.id

create index "informix".customer_index on "informix".customer
    (pu_date) using ct  in indexdbs;/create 

create unique index "informix".customer_uidx on "informix".customer
    (uno,sub_no) using ct in indexdbs;

create trigger "informix".customertrigger insert on "informix"

alter table "informix".customer add constraint (foreign key
    (customer_fk) references "informix".etc
     constraint "informix".acc_type);


create table "informix".merchant

Re: create table from database schema

woodcock — Fri, 29 Nov 2019 03:32:14 GMT

Why did you give up on the other question? I posted an answer 2 days ago that works fine.

Re: create table from database schema

woodcock — Fri, 29 Nov 2019 04:12:06 GMT

Like this:

| makeresults 
|  eval _raw="create table \"informix\".account

primary key (code)  constraint \"informix\".pk_code

create index \"informix\".account_index on \"informix\".account
    (pu_date) using ct  in indexdbs;/create 

create unique index \"informix\".account_uidx on \"informix\".account
    (uno,sub_no) using ct in indexdbs;

create trigger \"informix\".accounttrigger insert on \"informix\"

alter table \"informix\".account add constraint (foreign key
    (account_fk) references \"informix\".etc
     constraint \"informix\".acc_type);



create table \"informix\".customer

primary key (id)  constraint \"informix\".pk.id

create index \"informix\".customer_index on \"informix\".customer
    (pu_date) using ct  in indexdbs;/create 

create unique index \"informix\".customer_uidx on \"informix\".customer
    (uno,sub_no) using ct in indexdbs;

create trigger \"informix\".customertrigger insert on \"informix\"

alter table \"informix\".customer add constraint (foreign key
    (customer_fk) references \"informix\".etc
     constraint \"informix\".acc_type);


create table \"informix\".merchant"

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution."

| eval raw = split(_raw, "create table")
| fields raw
| fields - _*
| mvexpand raw
| rex field=raw mode=sed "s/^/create table/"
| rex field=raw max_match=0 "create (?<object>\S+)[^\.]+\.(?<value>\S+)"
| rex field=raw max_match=0 "\(foreign key[\r\n\s]+\((?<foreign_key>[^\)]+)"
| eval raw = mvzip(object, value, "=")
| rename raw AS _raw
| kv
| where isnotnull(table)
| table table index trigger foreign_key

Re: create table from database schema

indeed_2000 — Fri, 29 Nov 2019 04:14:42 GMT

Not give up, Giuseppe told this is new question, I try to create more clear question 🙂

Re: create table from database schema

indeed_2000 — Fri, 29 Nov 2019 05:56:50 GMT

Error in 'makeresults' command: This command must be the first command of a search.

Re: create table from database schema

indeed_2000 — Fri, 29 Nov 2019 06:37:25 GMT

Re: create table from database schema

to4kawa — Fri, 29 Nov 2019 11:36:01 GMT

kv is so cool.

Re: create table from database schema

woodcock — Fri, 29 Nov 2019 17:34:17 GMT

Read what I wrote on line #39. Your solution starts on line #41. As far as the error, if you are testing everything, you missed the leading pipe ( | ).

Re: create table from database schema

woodcock — Fri, 29 Nov 2019 17:34:31 GMT

Yes, indeed.

Re: create table from database schema

indeed_2000 — Fri, 29 Nov 2019 18:56:28 GMT

Sorry it was my fault , after i wrote comment see that part, but after check that part it only fill table name, other columns were empty!

Re: create table from database schema

woodcock — Fri, 29 Nov 2019 19:50:29 GMT

Then there is something different about your events than the data that you posted. Take my full answer with fake events data, break it apart, learn how it does what it does, then adjust it to fit your real data. The core concepts and functional components are all there to do what you need.

Re: create table from database schema

woodcock — Sat, 30 Nov 2019 04:20:45 GMT

I give up. My answer gives you all of the building blocks that you need to make a working solution. There is something about your sample data that is different from your real data.

Re: create table from database schema

indeed_2000 — Sat, 30 Nov 2019 17:48:42 GMT

First of all thank you so much for that time you spend on this issue,
Second I Just copy and past here portion of real db schema. And I got confused why only first column appear! And why first row is 0! there is no table with that name!

While I can grep in bash “index”, “trigger”, “create table”, and other fields without problem.

Re: create table from database schema

woodcock — Sat, 30 Nov 2019 17:58:42 GMT

I am only giving up because there is nowhere else for us to go. My solution works for the data you posted but if it doesn't work in your environment, I would actually have to see it on your Search Head and you probably aren't ready to hire me to come in for that.

Re: create table from database schema

indeed_2000 — Sat, 30 Nov 2019 18:35:58 GMT

You right, Thank you again for your answer. I should review your solution again and splunk documents to find out why it’s not work correctly when I run.
Regard,

Re: create table from database schema

to4kawa — Sat, 30 Nov 2019 23:40:58 GMT

It is better to check the results by executing one line at a time.
You need to check if the field is extracted as a result of rex.
Good luck.

Re: create table from database schema

indeed_2000 — Sun, 01 Dec 2019 15:27:12 GMT

It work separately but when I add another one mess up.
And I think “index” is a reserved keyword in splunk, when change index column name it will appear.

Re: create table from database schema

to4kawa — Sun, 01 Dec 2019 16:56:27 GMT

| makeresults
| eval _raw="source=test, index=abc"
| extract
| append 
    [| makeresults 
    | eval _raw="abd, test"
    | rex "(?<index>\w+), (?<source>\w+)"]

That is not the case.