How to extract a field from IBM Informix schema with Splunk?

[indeed_2000]

Hi, I have IBM Informix schema and want to extract data with Splunk from it like this:

table name | Index | Trigger
grupo_oper | type_idx | upload

Here is the sample schema:

grant dba to "informix";

{ TABLE "informix".grupo_oper row size = 8 number of columns = 2 index size = 0 }
create table "informix".**grupo_oper**
  (
    cod_gru_operat integer,
    cod_operatoria integer
  );
revoke all on "informix".grupo_oper from "public";

create index "informix".**type_idx** on "informix".utility (type)
create trigger "informix".**upload** insert on "informix"

grant select on "informix".grupo_oper to "public" as "informix";
grant update on "informix".grupo_oper to "public" as "informix";
grant insert on "informix".grupo_oper to "public" as "informix";
grant delete on "informix".grupo_oper to "public" as "informix";
grant index on "informix".grupo_oper to "public" as "informix";

create procedure "informix".sgc_var_param_var( c char(30)) returning smallint;
-- created by valau
return 1;
end procedure;

grant  execute on "informix".sgc_var_param_var to "public" as "informix";

Any recommendation?

[Anam]

Hi @mehrdad_2000

My name is Anam Siddique and I am the Community Content Specialist for Splunk Answers.

If any of the answers worked for you for this part of the question, please go ahead and accept them.

Thanks

[indeed_2000]

Hi Anam,
Unfortunately non of the answers resolve issue!

[woodcock]

Like this:

| makeresults 
|  eval _raw="grant dba to \"informix\";

 { TABLE \"informix\".grupo_oper row size = 8 number of columns = 2 index size = 0 }
 create table \"informix\".grupo_oper
   (
     cod_gru_operat integer,
     cod_operatoria integer
   );
 revoke all on \"informix\".grupo_oper from \"public\";

 create index \"informix\".type_idx on \"informix\".utility (type)
 create trigger \"informix\".upload insert on \"informix\"

 grant select on \"informix\".grupo_oper to \"public\" as \"informix\";
 grant update on \"informix\".grupo_oper to \"public\" as \"informix\";
 grant insert on \"informix\".grupo_oper to \"public\" as \"informix\";
 grant delete on \"informix\".grupo_oper to \"public\" as \"informix\";
 grant index on \"informix\".grupo_oper to \"public\" as \"informix\";

 create procedure \"informix\".sgc_var_param_var( c char(30)) returning smallint;
 -- created by valau
 return 1;
 end procedure;

 grant  execute on \"informix\".sgc_var_param_var to \"public\" as \"informix\";"

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

 | rex max_match=0 "create (?<object>\S+)[^\.]+\.(?<value>\S+)"
 | eval _raw = mvzip(object, value, "=")
 | kv
 | table table index trigger

[gcusello]

Hi @mehrdad_2000,
you could extract field using a regex like the following:

(?ms)create\s+table\s+\"\w+\"\.(?<table_name>\w*).*create\s+index\s+\"\w+\"\.(?<index>\w+).*create\s+trigger\s+\"\w+\"\.(?<trigger>\w+)

That you can test at https://regex101.com/r/DDgdkG/1

Ciao.
Giuseppe

[indeed_2000]

I’m using your rex but not work as expected in splunk!
Screenshot attached.

https://answers.splunk.com/storage/attachments/277637-0a21677e-2094-499e-bdd9-afadb94f3ee2.jpeg

Any recommendation?

[gcusello]

Hi @mehrdad_2000,
in regex101 the above regex is working!
now in Splunk, please, try this

(?ms)\".*create table \"\w+\"\.(?<table_name>\w+).*create index \"\w+\"\.(?<index>\w+).*create trigger \"\w+\"\.(?<trigger>\w+)

Ciao.
Giuseppe

[indeed_2000]

try this but not work!
Any recommendation?

source="/opt/logs/file.sql"  | rex (?ms)\".*create table \"\w+\"\.(?<table_name>\w+).*create index \"\w+\"\.(?<index>\w+).*create trigger \"\w+\"\.(?<trigger>\w+) | table table_name, index, trigger

[gcusello]

Hi @mehrdad_2000,
please try:

source="/opt/logs/file.sql" 
| rex "(?ms)\".*create table \"\w+\"\.(?<table_name>\w+).*create index \"\w+\"\.(?<index>\w+).*create trigger \"\w+\"\.(?<trigger>\w+)"
| table table_name index trigger

Ciao.
Giuseppe

[indeed_2000]

nope! it just give me same result!
Any recommendation?

[gcusello]

Hi @mehrdad_2000,
build you regex step by step:

• at first run the main search,
• then use the rex command and build a simple regex (not the full regex), e.g. | rex "(?ms)\".*create (?\w+)
• then add step by step all the parts of your regex until the full regex, e.g. | rex "(?ms)\".*create table \"(?\w+)

I followed this way to create the last version of my regex.
The problem is in quotes so you have to find them and escape all.

Ciao.
Giuseppe

[indeed_2000]

in field extraction of Splunk I try different way, it seems , they work separately like this:

(?ms)\".*create table \"\w+\"\.(?<table_name>\w+)

but when I add next it will be mess up!

(?ms)\".*create table \"\w+\"\.(?<table_name>\w+).*create index \"\w+\"\.(?<index>\w+)

[gcusello]

Hi @mehrdad_2000,
if the first regex is working, you can create three different field extractions.
Before the field extraction, you can test them using the rex command.

Ciao.
Giuseppe

[indeed_2000]

another problem is when create table does not match row with each other, e.g.

table name | Index    | Trigger
grupo_oper |           | 
           |            | upload
           | type_idx     | 

[gcusello]

Hi @mehrdad_2000,
This should be another question, anyway could you share your search?
Ciao.
Giuseppe

[indeed_2000]

sure here is another post with more details and screenshot.

https://answers.splunk.com/answers/786478/create-table-from-database-schema.html?minQuestionBodyLeng...

[indeed_2000]

I’m using your rex but not work as expected! Screenshot attached.
Any recommendation?