Splunk Search

transaction startswith from a lookup file

MVK1
Path Finder

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?

Labels (2)
0 Karma
1 Solution

MVK1
Path Finder

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.

View solution in original post

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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👽

Tags (1)
0 Karma

MVK1
Path Finder

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?

0 Karma

MVK1
Path Finder

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 ?

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

MVK1
Path Finder

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"")

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust


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

MVK1
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...