Splunk Search

create table from database schema

indeed_2000
Motivator

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 ?

alt text

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 
0 Karma

woodcock
Esteemed Legend

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
Ultra Champion

kv is so cool.

woodcock
Esteemed Legend

Yes, indeed.

0 Karma

indeed_2000
Motivator

alt text

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

indeed_2000
Motivator

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

indeed_2000
Motivator

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,

0 Karma

to4kawa
Ultra Champion

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.

0 Karma

indeed_2000
Motivator

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.

0 Karma

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

That is not the case.

0 Karma

indeed_2000
Motivator

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

indeed_2000
Motivator

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!

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

woodcock
Esteemed Legend

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

0 Karma

indeed_2000
Motivator

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

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...