Getting Data In

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

lupindeterd
Engager

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

Labels (1)

woodcock
Esteemed Legend

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>[^:]+)"
0 Karma

khourihan_splun
Splunk Employee
Splunk Employee

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.

0 Karma

shruti14
Explorer

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.

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Security Professional: Sharpen Your Defenses with These .conf25 Sessions

Sooooooooooo, guess what. .conf25 is almost here, and if you're on the Security Learning Path, this is your ...

First Steps with Splunk SOAR

Our first step was to gather a list of the playbooks we wanted and to sort them by priority.  Once this list ...

How To Build a Self-Service Observability Practice with Splunk Observability Cloud

If you’ve read our previous post on self-service observability, you already know what it is and why it ...