topic Re: Source type to use for postgresql log in Getting Data In

What would be the source type I use for postgresql logs?

lupindeterd — Wed, 05 Apr 2023 17:04:12 GMT

Hi,

What would be the source type I use for postgresql logs?

Here's a single line on the log file.

2013-02-27 18:36:01.103858 EST,"builder","dw",p13175,th-846481664,"10.24.33.81","44591",2013-02-27 18:35:54 EST,6827524,con243135,cmd70,seg-1,,dx1628096,x6827524,sx1,"LOG","00000","statement: SELECT quote_ident(E'subscribersnapshot_sample_ss539')",,,,,,"SELECT quote_ident(E'subscribersnapshot_sample_ss539')",0,,"postgres.c",1542,

Thanks,

lupin

Re: Source type to use for postgresql log

khourihan_splun — Mon, 28 Sep 2020 15:38:51 GMT

Splunk only has pretrained sourcetypes for mysql, specifically these data sources:

Databases mysqld, mysqld_error, mysqld_bin

For Postgresql, you should make a new sourcetype, I use "postgresql" then set the appropriate props.conf to that sourcetype to extract the fields properly.

Re: Extract Action field to use for postgresql log

shruti14 — Wed, 05 Apr 2023 16:22:12 GMT

Hi all,

In postgresql logs we get most of field extracted but i need to have column for action as well which needs to be extracted :

example :

2023-04-05 1:42:25 UTC [25804]: [3-1] user=pgmon,db=postgres,app=psql,client=[local] LOG: AUDIT: SESSION,1,1,MISC,BEGIN,,,BEGIN;SET statement_timeout=100;COMMIT;SELECT version() AS

2023-04-05 1:42:25 UTC [25804]: [2-1] user=pgmon,db=postgres,app=[unknown],client=[local] LOG: connection authorized: user=pgmon database=postgres

so if you see i want to have field extracted as Action and value to be Audit/connection authorized etc

values are different all across logs.

Re: What would be the source type I use for postgresql logs?

woodcock — Thu, 06 Apr 2023 18:31:10 GMT

Don't rely on built-in stuff, build it yourself. Pick a sourcetype that makes sense to you. The emerging standard is "vendor:product:type" but it doesn't matter. Then build out your field extractions yourself. Start with setting "KV_MODE=auto" and build out the rest from there. Here is some of that done in SPL but you should NOT do it in SPL, do it in KOs:

| makeresults | eval _raw="2023-04-05 1:42:25 UTC [25804]: [3-1] user=pgmon,db=postgres,app=psql,client=[local] LOG: AUDIT: SESSION,1,1,MISC,BEGIN,,,BEGIN;SET statement_timeout=100;COMMIT;SELECT version() AS" | append [| makeresults | eval _raw="2023-04-05 1:42:25 UTC [25804]: [2-1] user=pgmon,db=postgres,app=[unknown],client=[local] LOG: connection authorized: user=pgmon database=postgres" ] | eval _time = strptime(_raw, "%Y-%m-%d %H:%M:%S %Z") | kv | rex "\]\s+LOG:\s+(?<action>[^:]+)"