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. ... View more

have i make anything in the above query ... View more

yeah this worked also there is one query with left join so i have to get the db count with creating join on one column but post applying join the numbers are not correct . | inputlookup abc.csv where DECOMMISSIONED=N ENVIRONMENT=XYZ | fields ACTIVE_DC APP_NAME HOST_NAME DATABASE DB_VERSION DB_ROLE ENVIRONMENT | eval Reference=ISD | rename HOST_NAME as host | join type=left host [ search index=cb_* sourcetype="*dbx*" | stats count as SPLEvents latest(_time) as LastSeen by host | eval age=round((now()-LastSeen)/3600,1) | eval Status=case( LastSeen>(now()-(3600*2)),"Low", LastSeen<(now()-(3600*2+1)) AND LastSeen>(now()-(3600*8)) ,"Medium", LastSeen<(now()-(3600*8+1)) AND LastSeen>(now()-(3600*24)),"High", 1=1,"Critical") | convert ctime(LastSeen) timeformat="%d-%m-%Y %H:%M:%S" | eval Reference="SPL"] | fields DB_VERSION APP_NAME DATABASE ACTIVE_DC host Status SPLEvents | rex mode=sed field=host "s/\..*$//g" | fillnull value=Missing Status | fillnull value=Null | search APP_NAME="*" DATABASE="*" host="*" DB_VERSION="*" | eval DBStatus=if(SPLEvents="Null","missing","ok") | search DBStatus="ok" | stats dc(host) as dbcount ... View more

how we can make it common i guess problem is regex i have wriiten its working only when sourcetype is like : oracle:audit:json12 oracle:audit:sql11 but when oracle:audit:json its not able to extract json/sql value  oracle:audit:json12 oracle:audit:json11 oracle:audit:json oracle:audit:sql11 can you help me with regex which can extract json and sql from able sourcetypes  My regex is oracle:audit:(.*)\d\d ... View more

index="abc" `abc_sourcetype` host="$Database$" | eval sourcetype=lower(sourcetype) | rex mode=sed field=sourcetype "s/oracle:audit:(.*)\d\d/\1/g" | rex mode=sed field=USERHOST "s/\..*//g" | eval ACTION_NAME=if(isnull(ACTION_NAME) OR ACTION_NAME="", "TBD", upper(ACTION_NAME)) | eval DBUSERNAME=if(isnull(DBUSERNAME) OR DBUSERNAME="" OR DBUSERNAME="TBD", "TBD", upper(DBUSERNAME)) | eval OS_USERNAME=if(isnull(OS_USERNAME) OR OS_USERNAME="" OR OS_USERNAME="TBD", "TBD", lower(OS_USERNAME)) | eval RETURN_CODE=if(isnull(RETURN_CODE) OR RETURN_CODE="", 0, RETURN_CODE) | eval OBJECT_NAME=if(isnull(OBJECT_NAME) OR OBJECT_NAME="", "TBD", upper(OBJECT_NAME)) | eval USERHOST=if(isnull(USERHOST) OR USERHOST="", "TBD", lower(USERHOST)) | eval TERMINAL=if(isnull(TERMINAL) OR TERMINAL="" OR TERMINAL="TBD", "TBD", upper(TERMINAL)) | eval query=if(isnull(query) OR query="", "TBD", lower(query)) | stats dc(time) as Events, latest(time) as LastSeen, earliest(time) as FirstSeen by Database, sourcetype, ACTION_NAME, DBUSERNAME, OS_USERNAME, USERHOST,OBJECT_NAME, RETURN_CODE, query, TERMINAL | convert ctime(*Seen) timeformat="%m-%d-%Y %H:%M:%S"   here abc_sourcetype is macros   so this search is working fine when sourcetype is in format oracle:audit:json12/11 but failing to load data for oracle:audit:json   ... View more

I am using source editor ... View more

Dashboard is simple xml but there also | rex field=source "\/home\/mysqld\/(?&lt;Database1&gt;.*)\/audit\/" | rex field=source "\/mydata\/log\/(?&lt;Database2&gt;.*)\/audit\/" not giving me result. ... View more

Error in 'rex' command: Encountered the following error while compiling the regex '\/home\/mysqld\/(?&lt;Database1&gt;.*)\/audit\/': Regex: syntax error in subpattern name (missing terminator).   ... View more

ok and if sourcetype is different then probably if i give below one that would be fine : (sourcetype=A OR sourcetype=B)   | rex field=source "/home/mysqld/(?<DB_NAME1>[^/]+)/" | rex field=source "/mydata/log/(?<DB_NAME2>[^/]+)/" ```Combine the two DB_NAME fields``` | eval DB_NAME = coalesce(DB_NAME1, DB_NAME2) | fields - DB_NAME1, DB_NAME2 ... View more

"record":{"name”:"abc","record":"3055975400_2022-05-28T18:13:38","timestamp":"2022-08-24T11:33:47 UTC","connection_id":"3316408","status":0,"user":"exporter","user":"exporter","os_login":"","proxy_user":"","host”:”abc.com","ip”:"xxxxx","db":""}} this raw text ... View more

yeah but i have 2 soucre giving databases and they both have common value but comes at different path alsi in raw data i dont have database name. ... View more

Also i don't want to harcode it. ... View more

Yeah it is almost there is there way we can get username and destination also extracted as fields since they also have value i mean this regex gives extra field with value as username and destination but if you can see i have username :admin and destination as bucket name does that further breakage is possible ? username: admin "destination":{"bucket_name":"dbabucket","type":"s3","subdir":"radar2","filename":""}} But yes the regex shared by you is really helpful and i understood what mistake i am doing  thanks ITWhisperer   ... View more

I want to extract them at indexing time. So I want to get these field extracted during the index so i get the fields created and then create base search to build dashboards for visualisation.  The logs are not in regular json format . ... View more

Hi,  But these logs are not in json format, i.e why i have to parse them manually by writing regex ... View more