topic Re: extract field between two single quotes in Getting Data In

extract field between two single quotes

sam90651 — Tue, 16 Jan 2024 21:14:52 GMT

Sorry i am a noob to regex and splunk regex especially.

Regex to extarct all that is between the two single quotes. there will never be a single quote in the name.

EG extract the client code after word client and same for transaction

2024-01-16 15:04:22.7117 [135] INFO [javalang] Starting Report for client '0SD45' user 'user1' for transaction '123456'

@fieldextraction @Anonymous

Re: extract field between two single quotes

ITWhisperer — Tue, 16 Jan 2024 23:07:28 GMT

Try something like this

| rex "client\s'(?<client>[^']*)'" | rex "transaction\s'(?<transaction>[^']*)'"

Re: extract field between two single quotes

sam90651 — Wed, 17 Jan 2024 01:20:56 GMT

Great this works. i went ahead and added eval to it table client,transaction | eval user_transaction = client . "-" . transaction

now the second query returns below result

2024-01-16 19:08:13.3284 [43] INFO [.ServiceClassTraCack] 0LO19-1901631 Report Finished successfully at 7:08:13 PM on 1/16/2024

my first query is returning result as 0LO19-1901631, i want to match these results to above query along with Report Finished snippet

Re: extract field between two single quotes

ITWhisperer — Wed, 17 Jan 2024 13:38:27 GMT

Try something like this (although to be fair, you haven't shared any sample events or details of your current searches, so this may not work)

| rex "(?<user_transaction>\S+)\sReport Finished successfully" | eval user_transaction = if(isnull(user_transaction), client . "-" . transaction, user_transaction) | stats latest(_raw) as _raw by user_transaction

Re: extract field between two single quotes

sam90651 — Wed, 17 Jan 2024 14:20:32 GMT

oh i am sorry my current search returns below sample

0BI96-auto-4826143

I need to match this result and correlate if its matching 0BI96-auto-4826143 Report finished and return as finished column, basically comparing two strings

Re: extract field between two single quotes

ITWhisperer — Wed, 17 Jan 2024 14:31:49 GMT

Again, without seeing your actual data, this may not work

| rex "(?<user_transaction>\S+)\sReport Finished successfully" | eval user_transaction = if(isnull(user_transaction), client . "-auto-" . transaction, user_transaction) | stats latest(_raw) as _raw by user_transaction

Re: extract field between two single quotes

sam90651 — Wed, 17 Jan 2024 15:00:46 GMT

This is my first query which returns a table user_transaction in order 0BI96-auto-4826143

index="index" sourcetype=host=hq " Mark transaction results" "port = 2022"| rex "client\s'(?<client>[^']*)'" | rex "transaction\s'(?<transaction>[^']*)'" | rex "user\s'(?<user>[^']*)'" | table client,transaction,user | eval user_transaction = client . "-" . user . "-" . transaction | table user_transaction

2024-01-17 08:41:35.9174 [94] INFO [.java..TransLogCallback] OBI96-auto-4826143 Report Finished successfully at 8:41:35 AM on 1/17/2024

this is my actual data i want to match too

Re: extract field between two single quotes

sam90651 — Wed, 17 Jan 2024 15:13:28 GMT

This is my first query which returns a table user_transaction in order 0BI96-auto-4826143

2024-01-17 08:41:35.9174 [94] INFO [.java..TransLogCallback] OBI96-auto-4826143 Report Finished successfully at 8:41:35 AM on 1/17/2024

this is my actual data i want to match too

Re: extract field between two single quotes

ITWhisperer — Wed, 17 Jan 2024 15:18:20 GMT

If you aren't going to share your events, it is difficult to advise you further than I have already, especially when you appear to be ignoring my suggestions.

Re: extract field between two single quotes

sam90651 — Wed, 17 Jan 2024 15:40:08 GMT

Apologies here are events

Event 1:

2024-01-17 09:35:10.3370 [44] INFO[.java..TransLogCallback] Starting Report for client 'OBI96' user 'auto' for transaction '4826143 '' Report ID '222' - Retry #1 Date : 1/17/2024 Time : 9:35:10 AM Message : Mark transaction results: 1, Query : UPDATE transactions SET queued = 0, processing = 1, serviceip = ? , timestarted = now() WHERE clientcode = ? AND username = ? AND transid = ? (100.00.000.00, OBI96, auto, 4826143 ), port = 2222^^-------------------------------------------------------------------^^

Event 2:

2024-01-17 08:41:35.9174 [94] INFO [.java..TransLogCallback] OBI96-auto-4826143 Report Finished successfully at 8:41:35 AM on 1/17/2024 ^^-----------------------------------------------

Re: extract field between two single quotes

ITWhisperer — Wed, 17 Jan 2024 15:57:59 GMT

So the Report Completed message occurs before the Report Started message?

Assuming it is actually the latest (by _time) that you want to keep, try something like this

index="index" sourcetype=host=hq " Mark transaction results" "port = 2022" | rex "client\s'(?<client>[^']*)'" | rex "transaction\s'(?<transaction>[^']*)'" | rex "user\s'(?<user>[^']*)'" | rex "(?<user_transaction>\S+)\sReport Finished successfully" | eval user_transaction = if(isnull(user_transaction), client . "-" . user . "-" . transaction, user_transaction) | stats latest(_raw) as _raw by user_transaction

Re: extract field between two single quotes

sam90651 — Thu, 18 Jan 2024 18:41:14 GMT

yes it does. this actually worked. appreciate a ton