Getting Data In

DBX inputs - Filter Events to NullQueue

rijutha
Explorer

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

0 Karma
1 Solution

jblanton_splunk
Splunk Employee
Splunk Employee

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

View solution in original post

0 Karma

jblanton_splunk
Splunk Employee
Splunk Employee

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

0 Karma

rijutha
Explorer

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

[abc_defghi_jklmnop_keepstatus]
REGEX=QRS_MSG_STATUS="((ACC)|(RECEIVE)|(SPAWN)|APPROVED|ABWAITQ|CANCELED|COMPLETE|EXPEX|DEWAITQ|HELD|TRWAITQ|TRP|PAYSET|REJECTED|RELEASE|REPAIR|RTGSSYSQ|SCHEDULE|SECEX|ANIDX|VERIFY|WAITBULK)"
DEST_KEY = queue
FORMAT = indexQueue

0 Karma

jblanton_splunk
Splunk Employee
Splunk Employee

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

rijutha
Explorer

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. :(.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...