Splunk Search

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

kjonesdba_lm
Explorer

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?

Labels (2)
1 Solution

grittonc
Contributor

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.

View solution in original post

kjonesdba_lm
Explorer

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

0 Karma

kjonesdba_lm
Explorer

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 \'"

| 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*

0 Karma

grittonc
Contributor

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

0 Karma

kjonesdba_lm
Explorer

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

0 Karma

kjonesdba_lm
Explorer

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 \'"

| 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*

0 Karma

kjonesdba_lm
Explorer

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

0 Karma

kjonesdba_lm
Explorer
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*
0 Karma

grittonc
Contributor

what happens if you substitute:

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

for the rex statement that you have?

0 Karma

kjonesdba_lm
Explorer

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

0 Karma

kjonesdba_lm
Explorer

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.'

0 Karma

kjonesdba_lm
Explorer

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

0 Karma

grittonc
Contributor

OK, fixed. Please see answer below.

0 Karma

kjonesdba_lm
Explorer

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+)"
0 Karma

grittonc
Contributor

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.

View solution in original post

Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!