Splunk Search
Highlighted

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

Explorer

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

table name | Index | Trigger
grupooper | typeidx | 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?

0 Karma
Highlighted

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

Legend

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

0 Karma
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

Explorer

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?

0 Karma
Highlighted

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

Legend

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

0 Karma
Highlighted

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

Explorer

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

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

Legend

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

0 Karma
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

Legend

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

0 Karma
Highlighted

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

Explorer

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