topic Re: How to extract fields between backslashes and quotes with rex command? in Splunk Search

How to extract fields between backslashes and quotes with rex command?

kjonesdba_lm — Mon, 08 Jun 2020 17:27:30 GMT

These rows have a field that begins and ends with a quote, but have different meanings between the backslashes.

1st and 2nd rows are: 'Server_Name\Instance_Name'
from 'vmpit-ugzcg8xk\MSSQLSERVER'
from 'vmpit-ugzcg8xk.lm.lmig.com\MSSQLSERVER'

3rd and 4th rows are: 'AOAG_Name\Server_Name\Instance_Name'
from 'rbrk_ag1\vmpit-ugzcg8xk\MSSQLSERVER'
from 'rbrk_ag1\vmpit-ugzcg8xk.lm.lmig.com\MSSQLSERVER'

I need to be able to have a rex command that finds Server_Name, Instance_Name, and AOAG_Name from these 4 rows ( AOAG_Name would not have a value in the rows where it is not applicable).

My 'old' rex command before the data changed was:

| rex field=_raw "from [\'](?[^\']\w+-\w+)"

This is probably pretty easy for someone who is good with rex, but I am not and have not yet figured out how to do it.

Would anyone be able to help with this?

Re: How to extract fields between backslashes and quotes with rex command?

grittonc — Wed, 03 Jun 2020 01:33:01 GMT

This should work:

index="storage" sourcetype="rubrik:prod" "status=Failure" ndc="MSSQL_DB_BACKUP_" NOT "is not online." AND "eventSeverity=Critical"
| rex field=_raw "from '((?<AOAG_Name>[^\\\]+)\\\)?(?<Server_Name>[^\\\]+)\\\(?<Instance_Name>[^\\\]+)'. "    
| search NOT [ | inputlookup Servers_Pending_Deletion.csv | fields SERVER_NAME ]
| dedup SERVER_NAME sortby SERVER_NAME
| table SERVER_NAME _time _raw
| eval lm_action="ticket"
| eval lm_assigned_group="HS-AE-DATABASE-ALERTS"
| eval lm_summary=(SERVER_NAME." Rubrik status=MSSQL PR DB Failure")
| eval lm_ci=SERVER_NAME
| eval lm_severity="CRITICAL"
| eval lm_status="OPEN"
| eval lm_market="Hosting"
| eval lm_notes=(SERVER_NAME." "._raw)
| eval lm_env="PR"
| table lm*

The first capturing group for AOAG_Name (first set of nested parentheses) is optional, as denoted by the ? after the capturing group. This regex will match one or more non-backslash characters between backslashes for Server_Name and Instance_Name. Matching backslashes is always tricky in regexes, but triple-backslashes worked for me on Splunk Cloud 7.2.

Re: How to extract fields between backslashes and quotes with rex command?

kjonesdba_lm — Wed, 30 Sep 2020 05:35:53 GMT

This is actually the _raw data in splunk and original splunk query.. hopefully this will help with the changes to the query

1st and 2nd rows format
2020-06-02T22:15:58+00:00 10.177.121.152 1 2020-06-02T22:15:58.707Z RVMHM188S014111 Rubrik-JobFetcherLoop - EVENT [mdc@18060 instanceId="-1" jobId="" jobType="" ndc="MSSQL_DB_BATCH_BACKUP_84c7801c-91b9-4a21-ae97-16a09b68b47f_01d2ffcd-c800-41df-8d61-854d0c5d3171:::2" pid="21544" profile="false" taskId=""] [eventdetail@49929 eventType=Backup eventName=Snapshot.BackupFromLocationFailed objectType=Mssql objectName=TEST1 eventId=1591136158613-30269267-ed66-4fd2-be78-f43db513975f eventSeriesId=fa25e08b-7149-4846-b852-1d94b0beebdd objectId=6d16756a-196f-45d7-a34e-bb80ede64268 status=Failure eventSeverity=Critical locationName=- clusterName=rubrik_pdc_dev nodeId=RVMHM188S014111 nodeIpAddress=10.176.50.81] Failed backup of Microsoft SQL Server Database 'TEST1' from 'VMPIT-H4RBRK13.lm.lmig.com\MSSQLSERVER'. Reason: Internal server error 'Protecting the 'TEST1' database requires 'NT AUTHORITY\SYSTEM' to be a sysadmin or have the db_backupoperator role for that database.'

2020-06-02T22:00:09+00:00 10.177.121.152 1 2020-06-02T22:00:09.775Z RVMHM188S014111 Rubrik-JobFetcherLoop - EVENT [mdc@18060 instanceId="-1" jobId="" jobType="" ndc="MSSQL_DB_BATCH_BACKUP_2140df0f-dd16-4d37-8828-ed017862369b_3ea7bcf7-9528-48ad-bd89-a593c109ce74:::0" pid="21544" profile="false" taskId=""] [eventdetail@49929 eventType=Backup eventName=Snapshot.BackupFromLocationStarted objectType=Mssql objectName=master eventId=1591135209716-d5f5301a-99d7-4cdc-bc51-688b3c8c9895 eventSeriesId=21978596-fd3b-46d9-8820-a8b417076c9f objectId=70fb4b7b-37c4-486a-84a2-cf86d5a64b4a status=Running eventSeverity=Informational locationName=- clusterName=rubrik_pdc_dev nodeId=RVMHM188S014111 nodeIpAddress=10.176.50.81] Creating backup of Microsoft SQL Server Database 'master' from 'vmpit-h4rbrk04\MSSQLSERVER'

3rd and 4th rows
2020-06-01T22:09:32+00:00 10.177.121.152 1 2020-06-01T22:09:32.620Z RVMHM188S014111 Rubrik-JobFetcherLoop - EVENT [mdc@18060 instanceId="-1" jobId="" jobType="" ndc="MSSQL_DB_BATCH_BACKUP_2140df0f-dd16-4d37-8828-ed017862369b_f9977671-210b-4483-9422-2d8ccb34723f:::0" pid="21544" profile="false" taskId=""] [eventdetail@49929 eventType=Backup eventName=Snapshot.BackupFromLocationSucceeded objectType=Mssql objectName=random eventId=1591049372335-0534736f-1a57-40c6-8030-28ef1cf3b2aa eventSeriesId=b62d38d2-cc31-4eb4-baac-b68a5f04187c objectId=f68cf817-5e94-498d-acd0-1715839e7d52 status=Success eventSeverity=Informational locationName=- clusterName=rubrik_pdc_dev nodeId=RVMHM188S014111 nodeIpAddress=10.176.50.81] Completed backup of Microsoft SQL Server Database 'random' from 'RBRK_AG1\vmpit-h4rbrk04\MSSQLSERVER'

original splunk query
index="storage" sourcetype="rubrik:prod" "status=Failure" ndc="MSSQL_DB_BACKUP_" NOT "is not online." AND "eventSeverity=Critical"
| rex field=_raw "from \'"

Re: How to extract fields between backslashes and quotes with rex command?

grittonc — Wed, 03 Jun 2020 15:01:54 GMT

Can you paste your search using the "code sample" button?

Re: How to extract fields between backslashes and quotes with rex command?

kjonesdba_lm — Wed, 03 Jun 2020 16:22:27 GMT

I am really sorry but I dont see the "code sample" button. I have only used answers.splunk.com once before

Re: How to extract fields between backslashes and quotes with rex command?

kjonesdba_lm — Wed, 30 Sep 2020 05:37:16 GMT

I will add the 'correct' old(current) splunk query here...

index="storage" sourcetype="rubrik:prod" "status=Failure" ndc="MSSQL_DB_BACKUP_" NOT "is not online." AND "eventSeverity=Critical"
| rex field=_raw "from \'"

Re: How to extract fields between backslashes and quotes with rex command?

kjonesdba_lm — Wed, 03 Jun 2020 17:22:29 GMT

the bold should look like this | rex field=_raw "from \'"

Re: How to extract fields between backslashes and quotes with rex command?

kjonesdba_lm — Wed, 03 Jun 2020 17:23:21 GMT

index="storage" sourcetype="rubrik:dev" "status=Failure" ndc="MSSQL_DB_*BACKUP_*"  NOT "is not online." AND  "eventSeverity=Critical"
| rex field=_raw "from [\'](?<SERVER_NAME>[^\']\w+-\w+)" 

| search NOT [ | inputlookup Servers_Pending_Deletion.csv | fields SERVER_NAME ]
| dedup SERVER_NAME sortby SERVER_NAME
| table SERVER_NAME _time _raw
| eval lm_action="ticket"
| eval lm_assigned_group="HS-AE-DATABASE-ALERTS"
| eval lm_summary=(SERVER_NAME." Rubrik status=MSSQL PR DB Failure")
| eval lm_ci=SERVER_NAME
| eval lm_severity="CRITICAL"
| eval lm_status="OPEN"
| eval lm_market="Hosting"
| eval lm_notes=(SERVER_NAME." "._raw)
| eval lm_env="PR"
| table lm*

Re: How to extract fields between backslashes and quotes with rex command?

grittonc — Wed, 03 Jun 2020 18:08:20 GMT

what happens if you substitute:

| rex field=_raw ".*from '((?<AOAG_Name>[^\\\]+)\\\)?(?<Server_Name>[^\\\]+)\\\(?<Instance_Name>[^\\\]+)'.*"

for the rex statement that you have?

Re: How to extract fields between backslashes and quotes with rex command?

kjonesdba_lm — Wed, 30 Sep 2020 05:37:21 GMT

It is not quite working it gives this for SERVER_NAME (lm_ci)

lm_ci
MSSQLSERVER'. Reason: Internal server error 'Protecting the 'MSP_T1_11_EXP' database requires 'NT AUTHORITY

Re: How to extract fields between backslashes and quotes with rex command?

kjonesdba_lm — Wed, 30 Sep 2020 05:37:25 GMT

for this _raw data

2020-06-02T22:15:50+00:00 10.177.121.152 1 2020-06-02T22:15:50.797Z RVMHM188S014111 Rubrik-JobFetcherLoop - EVENT [mdc@18060 instanceId="-1" jobId="" jobType="" ndc="MSSQL_DB_BATCH_BACKUP_84c7801c-91b9-4a21-ae97-16a09b68b47f_01d2ffcd-c800-41df-8d61-854d0c5d3171:::2" pid="21544" profile="false" taskId=""] [eventdetail@49929 eventType=Backup eventName=Snapshot.BackupFromLocationFailed objectType=Mssql objectName=MSP_T1_11_EXP eventId=1591136150660-60fb3bcb-b292-4914-a105-dd294abf5a47 eventSeriesId=b2f70082-c262-417c-bacf-ad3e04cb5ae5 objectId=28bad07f-97f7-4e5b-b2c5-110d25ad3380 status=Failure eventSeverity=Critical locationName=- clusterName=rubrik_pdc_dev nodeId=RVMHM188S014111 nodeIpAddress=10.176.50.81] Failed backup of Microsoft SQL Server Database 'MSP_T1_11_EXP' from 'VMPIT-H4RBRK13.lm.lmig.com\MSSQLSERVER'. Reason: Internal server error 'Protecting the 'MSP_T1_11_EXP' database requires 'NT AUTHORITY\SYSTEM' to be a sysadmin or have the db_backupoperator role for that database.'

Re: How to extract fields between backslashes and quotes with rex command?

kjonesdba_lm — Wed, 30 Sep 2020 05:37:29 GMT

I think the issue is that AOAG_Name sometimes does not exist in _raw - or - Server_Name is not always the first value in the string.. When AOAG_Name exists then Server_Name is the second value in the string.. When AOAG_Name doesn't exist then Server_Name is the first value in the string

Re: How to extract fields between backslashes and quotes with rex command?

grittonc — Thu, 04 Jun 2020 11:54:44 GMT

OK, fixed. Please see answer below.

Re: How to extract fields between backslashes and quotes with rex command?

kjonesdba_lm — Thu, 04 Jun 2020 13:09:23 GMT

Awesome still testing this out and working so far!... I made these changes .. made server_name upper case and stripped out the .lm.lmig.com.. I will get back this afternoon.

| rex field=_raw "from '((?<AOAG_Name>[^\\\]+)\\\)?(?<SERVER_NAME>[^\\\]+)\\\(?<Instance_Name>[^\\\]+)'. "  
| rex field=SERVER_NAME "(?<SERVER_NAME>\w+-\w+)"

Re: How to extract fields between backslashes and quotes with rex command?

kjonesdba_lm — Thu, 04 Jun 2020 18:02:31 GMT

Thank you so much grittonc and to4kawa.. The answer is perfect. and the explanations.! This will help me down the road.