topic Re: How to edit my rex search to extract a Transaction ID and display this as _time, trans, and status? in Splunk Search

How to edit my rex search to extract a Transaction ID and display this as _time, trans, and status?

leomedina — Tue, 18 Apr 2017 22:49:22 GMT

Hello all,

I am attempting to extract a Transaction ID and display this as _time, trans, status

index=datapower environment=Prod  "HTTP response code*" 'http://ResellerCheck/'  
| lookup oauth_http_response_codes.csv response_code OUTPUT status, description 
| search trans 
| rex field=_raw "trans(?P<AppName>[^\s]+): trans" 
| stats count response_code by _time, trans, status

I have been working on this for the past three hours and went through the main page for search and rex but don't get any "Statistical" extract. I, however, see the Events. What am I doing wrong here?

Thanks!

Re: How to edit my rex search to extract a Transaction ID and display this as _time, trans, and status?

skoelpin — Tue, 18 Apr 2017 23:05:47 GMT

I'm not sure rex is what you think it is.. Rex will do a search time field extraction which allows you to use that field.. In your case, your rex command is telling you to extract a field called AppName that is at the beginning of a line with a space prefixed with trans..

If your intent is to extract a field from the raw data, can you provide some sample data that you want to extract?

Re: How to edit my rex search to extract a Transaction ID and display this as _time, trans, and status?

lguinn2 — Wed, 19 Apr 2017 04:08:34 GMT

Also, why are you using single quotes in your search?

Re: How to edit my rex search to extract a Transaction ID and display this as _time, trans, and status?

niketn — Tue, 29 Sep 2020 13:42:39 GMT

@leomedina... if trans is a field in your events and not lookup you should add trans to base search rather than | search trans. If AppName is the field you need to extract then perform stats by AppName not trans. Do the events being correlated have the same _time? If they are different you would need min(_time) EarliestTime and max(_time) as LatestTime statistical functions for aggregating _time.

Please add some sample mock events for the community to help you with the same.

Re: How to edit my rex search to extract a Transaction ID and display this as _time, trans, and status?

gcusello — Wed, 19 Apr 2017 06:55:17 GMT

Hi leomedina,
could you share some log example?
Bye.
Giuseppe

Re: How to edit my rex search to extract a Transaction ID and display this as _time, trans, and status?

leomedina — Wed, 19 Apr 2017 07:13:50 GMT

Hi Niketnilay,

I did some modifications to it to reflect your suggestion, but I am still not getting what I want. Trans is part of a field named ApplicationName. However, there is a lot more data than what I need within this field; ie. trans(12345678) gtid(87654321). Would it be possible to trim down the data to only obtain what I need?

index=datapower environment=Prod "HTTP response code*" "http://ResellerCheck/" "trans"
| lookup oauth_http_response_codes.csv response_code OUTPUT status, description  
| sort  -_time | eval  Time=_time | convert  timeformat="%b-%d-%y %I:%M:%S %p" ctime(Time)  
| search trans
| rex field=_raw "trans(?P<trans>[^\s]+): trans" 
| table  Time, trans, response_code, description
| rename response_code as "Response Code", description as "Description", trans as "TransactionID"

Ultimately what I am looking for is

Time                         TransactionID          Response Code                Description
Apr-18-17 11:51:27 PM        123456789                403                             Forbidden
Apr-18-17 11:51:27 PM        123456788                200                             OK

Greatly appreciate the help and assistance.

Re: How to edit my rex search to extract a Transaction ID and display this as _time, trans, and status?

leomedina — Wed, 19 Apr 2017 07:57:58 GMT

Hi Guiseppe,

Please see below example log.

Apr 19 06:51:27 myhost04 [WebApp][0x80e0015b][mpgw][info] mpgw(ResellerCheck): trans(1162505423) gtid(3083100428): HTTP response code 200 for "http://ResellerCheck/"
Apr 18 21:31:20 myhost03 [WebApp][0x80e0015b][mpgw][info] mpgw(ResellerCheck): trans(278913012) gtid(2705343391): HTTP response code 200 for "http://ResellerCheck/"
Apr 18 13:20:50 myhost03 [WebApp][0x80e0015b][mpgw][info] mpgw(ResellerCheck): trans(355305813)[127.0.0.2] gtid(2667779775): HTTP response code 200 for "http://ResellerCheck/"
Apr 18 13:18:35 myhost03 [WebApp][0x80e0015b][mpgw][info] mpgw(ResellerCheck): trans(355302277) gtid(2667591343): HTTP response code 403 for "http://ResellerCheck/"
Apr 18 08:34:06 myhost03 [WebApp][0x80e0015b][mpgw][info] mpgw(ResellerCheck): trans(354804325)[127.0.0.2] gtid(2643772783): HTTP response code 200 for "http://ResellerCheck/"

Thanks for the help.

Re: How to edit my rex search to extract a Transaction ID and display this as _time, trans, and status?

gcusello — Wed, 19 Apr 2017 08:09:51 GMT

Hi leomedina,
you can see below the regex to extract transactionId that I imagine is the number after "trans"

trans\((?<transactionId>[^\)]*)

you can test it at in https://regex101.com/r/UMpQlI/1

In addition you could move the search for the "trans" word in the beginning (it's a best practice to filter as more as possible in the first search), something like this:

index=datapower environment=Prod  "HTTP response code*" 'http://ResellerCheck/'  trans 
 | lookup oauth_http_response_codes.csv response_code OUTPUT status, description 
 | rex "trans\((?<transactionId>[^\)]*)" 
 | stats count response_code by _time, transactionId, status

Bye.
Giuseppe

Re: How to edit my rex search to extract a Transaction ID and display this as _time, trans, and status?

leomedina — Wed, 19 Apr 2017 17:02:23 GMT

Hi Giuseppe!

Thank you, that is exactly what I was looking for! 🙂