create table from database schema

[indeed_2000]

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 

[woodcock]

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

[to4kawa]

kv is so cool.

[woodcock]

Yes, indeed.

[indeed_2000]

[woodcock]

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.

[indeed_2000]

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.

[woodcock]

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.

[indeed_2000]

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,

[to4kawa]

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.

[indeed_2000]

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.

[to4kawa]

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

That is not the case.

[indeed_2000]

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

[woodcock]

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 ( | ).

[indeed_2000]

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!

[woodcock]

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.

[woodcock]

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

[indeed_2000]

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