I have the following rex to test a new field extraction:
This will give me SQLText without the comments. It's working fine.
The Problem is, when I compared the new(a_action) and the old field(action) this :
| eval diff=if(a_action=action,0,1) | eval nospace=substr(a_action , 57,58)| where diff=1 | table action a_action nospace | eval x=length(action) | eval y=length(a_action) | eval z=length(nospace)
tells me, that the a_action is 1 character longer than action field. I figured that there is a space on the end of the string of the a_action field. I can't tell why, is there something wrong in my regex?
So here is a sample of the Table I created with the query :
field names: action : a_action :nospace : x : y : z vlaues: begin dbms_application_info.set_module(:1 , :2 ); end; begin dbms_application_info.set_module(:1 , :2 ); end; d; 56 57 3
There you can see that "z" only has 2 charakters in it plus the "invisible" space
Since you have not provided sample log or log example, I was just guessing based on SQL_txt field that the data is coming from SQL Server. Nevertheless, the reason why I asked to check (?s) was because:
1) You have already verified that there is no space in original field, by testing trim.
2) Sometimes non special characters like Line Feed(\r) and New Lines(\n) are displayed as space. The (?s) specified tells rex command to extract from only single line and you have used .* as the extractor. Which fetches until newline character is found unless mode is single line or (?s).
Now that you have mentioned that even this does not help, may imply that you do not have new line character in the end of data. Just try .+ instead of .* once and see.
If this does not help you will have to give some mock data or anonymized data if it is confidential, or reach out to Splunk support directly.
Can you perform the following on action field as well?
eval finalCharacter=substr(action , 57,58)
Also see if rex can be changed to the following:
Have you tried to run rex command with and without (?s)?
If possible check directly in SQL Server DB whether the extracted value SQL_Text in the table/column has new line CHAR(13) and/or linefeed CHAR(10) characters or not.