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?
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
Hi Anam,
Unfortunately non of the answers resolve issue!
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
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
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?
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
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
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
nope! it just give me same result!
Any recommendation?
Hi @mehrdad_2000,
build you regex step by step:
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
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+)
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
another problem is when create table does not match row with each other, e.g.
table name | Index | Trigger
grupo_oper | |
| | upload
| type_idx |
Hi @mehrdad_2000,
This should be another question, anyway could you share your search?
Ciao.
Giuseppe
sure here is another post with more details and screenshot.