topic Re: DBX inputs - Filter Events to NullQueue in Getting Data In

DBX inputs - Filter Events to NullQueue

rijutha — Tue, 29 Sep 2020 20:44:53 GMT

Hi,

I have a dbx input that runs a stored procedure and spits out results into an index. I would like to add a props.conf and transforms.conf on the Heavy Forwarder to drop events that dont match a particular field value.

All the fields are automatically extracted since its from the database.

I would like to add a filter to drop all events except the ones that match ABC_MSG_TYPE= 000 or 111 or 222

Another filter to drop all events except the ones that match ABC_MSG_STATUS = (COMPLETE) or REPAIR

I used this in the transforms, but it doesnt work. Could someone please help me with a solution?

[abc_defghi_jk_discardtype]
REGEX = .
DEST_KEY = queue
FORMAT = nullQueue

[abc_defghi_jk_keeptype]
REGEX=(101|102|103|200|202|203)
DEST_KEY = queue
FORMAT = indexQueue

Sample Events from Database

2018-08-07 16:24:46.490, PK_PK_EVENTS="427072", UPDATE_TIME="2018-08-07 16:24:46.4904597", ABC_AMOUNT="4.3000", ABC_BASE_AMOUNT="4.3000", ABC_DEF="0", ABC_CREATE_DATE="2018-08-07 00:00:00.0", ABC_CURRENCY="ABC", ABC_DEPARTMENT="DEF", ABC_DIRECTION="O", ABC_MID="00000000000C000", ABC_MOP="XYZABC", ABC_MSG_STATUS="(COMPLETE)", ABC_MSG_TYPE="000", ABC_NON_STP="1", ABC_ORIG_MOP="FEEDER", ABC_ORIG_MT="000", ABC_ORIG_REFERENCE="ABCDE0000000000", ABC_ORIG_SENDER="ABCBDE3MXXX", ABC_REFERENCE="DEFAB83109613434", ABC_TIME="15:34:42", ABC_UPDTIME="2018-08-07 16:24:39.0", ABC_VALUE_DATE="2018-08-07 00:00:00.0", ABC_SERVICE="ONL", GHI1000_ACC_NO="000000000000000", GHI1000_BBK="ABC DEF , N.A.", GHI1000_BBK_BIC="XXXXXX33XXX", GHI1000_BNF="int jkl mt000", GHI1000_BNF_ID="0
2018-08-07 16:24:46.490, PK_PK_EVENTS="427072", UPDATE_TIME="2018-08-07 16:24:46.4904597", ABC_AMOUNT="4.3000", ABC_BASE_AMOUNT="4.3000", ABC_DEF="0", ABC_CREATE_DATE="2018-08-07 00:00:00.0", ABC_CURRENCY="ABC", ABC_DEPARTMENT="DEF", ABC_DIRECTION="O", ABC_MID="00000000000C000", ABC_MOP="XYZABC", ABC_MSG_STATUS="REPAIR", ABC_MSG_TYPE="000", ABC_NON_STP="1", ABC_ORIG_MOP="FEEDER", ABC_ORIG_MT="000", ABC_ORIG_REFERENCE="ABCDE0000000000", ABC_ORIG_SENDER="ABCBDE3MXXX", ABC_REFERENCE="DEFAB83109613434", ABC_TIME="15:34:42", ABC_UPDTIME="2018-08-07 16:24:39.0", ABC_VALUE_DATE="2018-08-07 00:00:00.0", ABC_SERVICE="ONL", GHI1000_ACC_NO="000000000000000", GHI1000_BBK="ABC DEF , N.A.", GHI1000_BBK_BIC="XXXXXX33XXX", GHI1000_BNF="int jkl mt000", GHI1000_BNF_ID="0

Re: DBX inputs - Filter Events to NullQueue

jblanton_splunk — Tue, 29 Sep 2020 20:49:04 GMT

Hello rijutha,

You'll need to set the quotation marks as part of your Regex capture, and you'll probably want to add the field name as well to prevent it from grabbing other matching strings on accident.

Try something like:
[abc_defghi_jk_keeptype]
REGEX=ABC_MSG_TYPE="(101|102|103|200|202|203)"
DEST_KEY = queue
FORMAT = indexQueue

Re: DBX inputs - Filter Events to NullQueue

rijutha — Tue, 29 Sep 2020 20:49:33 GMT

Thanks a ton! That worked for MSG_TYPE. But does not work for MSG_STATUS, am thinking its because of the paranthesis for some values. Would you know a solution?

[abc_defghi_jklmnop_discardstatus]
REGEX = .
DEST_KEY = queue
FORMAT = nullQueue

Re: DBX inputs - Filter Events to NullQueue

jblanton_splunk — Thu, 09 Aug 2018 13:54:42 GMT

You're very welcome!

You are correct, you'll have to 'escape' the parenthesis you're needing to match for some of the status responses with a backslash character, for example:

REGEX=QRS_MSG_STATUS="(\(ACC\)|\(RECEIVE\)|CANCELLED)"

Re: DBX inputs - Filter Events to NullQueue

rijutha — Mon, 13 Aug 2018 02:14:04 GMT

Hi jblanton,

I tried escaping the paranthesis as you mentioned. It is dropping all events into the nullqueue and not picking up the events that are in scope or valid. :(.