How to create a search string that can count the number of occurrences for the prefixes on [txn_key]?

[jugarugabi]

Hi, 

I am having the following output:

[txn_key] field2 field3 status thread [time1] time2 time3 status2

[IDMS-TJ_TJG022092200005GN00017] 332950 311551 OK 2 [133369] 342 29 OK
[ZVKK_R1000001-235CDC24E191DBCE4906CCD0ND0000001] 498728 488378 OK 1 [133564] 509 9 OK
[PE_CZ_R19.6_2226500012123062] 342295 331477 OK 2 [133365] 353 49 OK
[BAFIROPC_R1.1_186951760] 289068 282128 OK 1 [133392] 295 5 OK
[GALILEO_R19.4_MTA_03FH220922110216] 394234 383672 OK 2 [133537] 405 11 OK
[DBINTERNET_R19.4_HU_RE02209223-06008] 187797 168329 OK 2 [133526] 201 7 OK
[IDMS_1-I0781_944e2c3cafc0487db56f6b8d3a6a6e231] 193581 178804 OK 2 [133576] 206 4 OK
[....]

I need to create a search string that can count the number of occurrences for the prefixes on [txn_key]. 
Therefore, I would need to have the output similar to: 

txn_key	count of txns
IDMS-TJ	1
ZVKK	543
PE_CZ_R19.6	0
BAFIROPC_R1.1	231
GALILEO_R19.4	12
DBINTERNET_R19.4_HU	212312
[...]

Tried so far using following logic
| stats count(eval(tnx_key=="ZVKK")) as ZVKK, count(eval(tnx_key=="GALAPAC")) as GALAPAC by tnx_key

but it doesn't produce the desired output.

A bit of help please?

[gcusello · Accepted solution]

Hi @jugarugabi,

you have to extract the txn_key using a regex (that you can test at https://regex101.com/r/lGY1Um/1) and then use a search like this:

<your_search>
| rex "^\[(?<txn_key>.*)_|-\w+\]"
| stats count AS txn_count BY txn_key

 Ciao.

Giuseppe

[gcusello]

Hi @jugarugabi,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉