how to exclude several patterns by IN function?

[cheriemilk]

Hi team,

I have below sample events in splunk.

2021-04-09 07:12:41,323 PLV=EVENT DT=MANUALEVENT CIP=0.0.0.1CMID=shangThai CMN="shanghai"

2021-04-08 07:12:41,323 PLV=EVENT DT=MANUALEVENT CIP=0.0.0.1CMID=shanghai CMN="shanghai"

2021-04-08 07:11:57,929 PLV=EVENT DT=MANUALEVENT CIP=0.0.0.2 CMID=shanghaiT1 CMN="shanghai"

2021-04-08 07:11:33,056 PLV=EVENT DT=MANUALEVENT CIP=0.0.0.2 CMID=chengdu CMN="chengdu"

2021-04-08 07:11:53,871 PLV=EVENT DT=MANUALEVENT CIP=0.0.0.2 CMID=chengduT3 CMN="chengdu"

2021-04-08 07:11:33,056 PLV=EVENT DT=MANUALEVENT CIP=0.0.0.2 CMID=city CMN="city"

2021-04-08 07:11:33,056 PLV=EVENT DT=MANUALEVENT CIP=0.0.0.2 CMID=beijing CMN="beijing"

2021-04-09 07:11:33,056 PLV=EVENT DT=MANUALEVENT CIP=0.0.0.2 CMID=Tbeijing CMN="beijing"

2021-04-08 07:11:33,056 PLV=EVENT DT=MANUALEVENT CIP=0.0.0.2 CMID=shenyang CMN="shengyang"

2021-04-08 07:11:33,056 PLV=EVENT DT=MANUALEVENT CIP=0.0.0.2 CMID=shenyangD CMN="shengyang"

In the base query, I want to filter out below events with CMID value match below patterns: 

1) *T

2) *T#:   # is a wildcard stands for a number.

3) *D

4) *D#:   # is a wildcard stands for a number.

and i am trying to filter them out by IN function, but failed.

NOT CMID IN ("*T", "*T#", " *D", "*D#" )

Questions: how to achieve this by IN function?

Thanks,

Cherie

[manjunathmeti · Accepted solution]

hi @cheriemilk,

Filtering on *T# and *D# is not possible in IN function as fields as it only supports asterisk ( * ) character as a wildcard in field values. You can use the where command with the match function.

https://docs.splunk.com/Documentation/Splunk/latest/Search/Wildcards 

index=index NOT CMID IN("*T", "*D") | where NOT match(CMID, "(T|D)\d+$")

If this reply helps you, a like would be appreciated.