transaction startswith from a lookup file

[MVK1]

Hello I have the following sample log lines from a splunk search query 

line1
line2
line3: field1 : some msg
line4
line5
status: PASS
line6
line7
line3: field2: some msg
line8
line9:
status: PASS
line1
line2
line3: field3: some msg
line4
line5:
status: PASS
line1
line2
line3: field4: some msg
line4
line5:
status: PASS

I want to write a transaction to return lines between

field1, status: PASS 

field2, status: PASS

field3: status:PASS

and so-on

I have tried the following search query with multiple startswith values

index="test1" source="test2" run="test3"
| transaction source run startswith IN ("field1", "field2", "field3") endswith="status: PASS"

Instead of using IN keyword for startswith, I want to use a csv lookup table messages.csv

Sample messages.csv content

id,Message
1,field1
2,field2
3,field3
4,field4

I want to write splunk transaction command with startswith parameter containing each Message field from messages.csv

My inputlookup CSV file may have 100 different rows with different messages

There is also a chance that my splunk search results may not have any entries with lines containing field1, field2, field3, field4

Can someone please help on how to write splunk transaction where startswith needs to be run for each Message in messages.csv?

[yuanliu]

The following may look like voodoo but give it a try:-)

index="test1" source="test2" run="test3"
| transaction source run startswith IN 
    [inputlookup messages.csv
    | fields Messages
    | rename Messages as search
    | format "(" "\"" "" "\"" "," ")"
    | rex field=search mode=sed "s/ *\" */\"/g"]
    endswith="status: PASS"

#forematmagic👽

[MVK1]

Thanks for the response @yuanliu 

May I know what this block is doing? 

| format "(" "\"" "" "\"" "," ")"
| rex field=search mode=sed "s/ *\" */\"/g"

I don't see lines starting with startswith but see correct lines ending with endswith

when I run this command separately 

|inputlookup messages.csv
| fields Messages
| rename Messages as search
| format "(" "\"" "" "\"" "," ")"
| rex field=search mode=sed "s/ *\" */\"/g"

I see a column with name search and value (""field1"")

Do we need to have field1 inside parentheses and two double quotes?

[MVK1]

My guess of incorrect search results could be because of having spaces in my Message field in CSV

my input lookup CSV Message filed has a string "My input search message" 

I need to match all lines that start with entire line between "My input search message"  and a given endswith

Currently I guess it is individually looking for events "My"  "input" "search" "message" separately

Can you please help how to match entire message in startswitb ?

[yuanliu]

---

I see a column with name search and value (""field1"")

Do we need to have field1 inside parentheses and two double quotes?

---

Field label "search" in a subsearch is a pseudo keyword for "use as is literal" in a search command.  No, they should NOT have two quotation marks on each side.  Maybe your lookup values insert one additional set of double quotes?  If so, we can get rid of one set.

Here is my emulation

| makeresults format=csv data="id,Messages
,a
,b
,c
,d"
``` the above emulates
| inputlookup messages.csv
```
| fields Messages
| rename Messages as search
| format "(" "\"" "" "\"" "," ")"
| rex field=search mode=sed "s/ *\" */\"/g"

Output only contains one set of double quotes

search

("a","b","c","d")

[MVK1]

Assuming my messages.csv has a single row with Messages field "My input search message"

I dont see any double quotes added until these 3 lines

| inputlookup messages.csv
| fields Messages
| rename Messages as search 

I see My input search message

After adding 4th line

| inputlookup messages.csv
| fields Messages
| rename Messages as search 

| format "(" "\"" "" "\"" "," ")"

I see the following 

( " "My input search message" " )

After adding 5th line

| inputlookup messages.csv
| fields Messages
| rename Messages as search 

| format "(" "\"" "" "\"" "," ")"

| rex field=search mode=sed "s/ *\" */\"/g"

I see the following result with two doublequotes

(""My input search message"")

[yuanliu]

---

---

Yes, breaker characters such as white spaces force Splunk to add quotation marks.  If you have mixed values with and without breaker characters, the rex needs to handle both.

| inputlookup messages.csv
| fields Messages
| rename Messages AS search
| format "(" "\"" "" "\"" "," ")"
| rex field=search mode=sed "s/ *\" */\"/g s/\"\"/\"/g"

Here is my emulation

| makeresults format=csv data="Messages
a
b c
d
e f g"
``` the above emulates
| inputlookup messages.csv
```

My result is now

search

("a","b c","d","e f g")

[MVK1 · Accepted solution]

Thank you for your time and response. I now don't see double quotes in the search query. This is helpful.

startswith="my start msg" endswith="my end msg" --> works

startswith IN ("my start msg1", "my start msg2", "my start msg3") endswith="my end msg"  ---> This is honoring only endswith flag and not returning events starting with my start msg lines "my start msg1" or "my start msg2" or "my start msg3" 

I notice that splunk search returns events before these matching startswith fields 

I will open a different question for that.