Splunk Search

Strange error with subsearch

rlautman
Path Finder

I have a query with a subquery that I am using to identify a set of transactions that contain a string - from those transactions I am extracting a unique identifier that will find all other transactions that are related to it - the final part of my query is to create a table showing all the related transactions, the Date each one took place on and the message string for each.

I am getting a strange error claiming that there is an invalid argument for a field which I have not used or referenced in my search - my search is below, can anyone tell me where I have went wrong? I feel it may be as a result of having two table commands but I am unsure of how to construct the search without these.

index=b2bapps "*Order not fulfulled*" | table OrderID [search earliest=-40d | rex "String>(?<OrderUpdate>[^<]*)<" | eval Date=strftime(_time,"%d-%m-%Y") | where OrderUpdate not null | dedup transactID,OrderUpdate | sort _time]| table, transactID, Date, OrderUpdate
0 Karma

lguinn2
Legend

Okay, I assume that this search will find the orders of interest

index=b2bapps "*Order not fulfilled*"

And that then you want to use the OrderID field from these events to locate the related transactions. You do have some syntax problems, which is probably what confused Splunk. But I think the larger problem is the overall structure of your search. I propose the following

earliest=-40d [ search index=b2bapps "*Order not fulfilled*" | dedup OrderID | fields OrderID ]
| rex "String>(?<OrderUpdate>[^<]*)<" 
| where isnotnull(OrderUpdate)
| dedup transactId, OrderUpdate
| sort _time
| table transactID, _time, OrderUpdate

Line by line explanation: (1) the subsearch returns a list of OrderIDs that meet the criteria. This list is logically joined by ORs, so it might return (100023 OR 2012308 OR 294112) Also in line 1, the subsearch results are combined with the earliest date criteria and the base search is run.

(2) the OrderUpdate field is extracted

(3) events with null values of OrderUpdate are discarded

(4) Only the most recent value of each event is kept based on transactId and OrderUpdate

(5) The events are reorded oldest to newest

(6) The values are displayed in table form

Another possible issue: a subsearch, by default, returns a maximum of 100 results. There is a good reason for this, since larger subsearch results will cause the base search to run more slowly. If you might have more than 100 OrderIDs, you could run your search over a shorter time period than 40 days. But you can increase the number of results as follows. Edit the first line of the search to be

earliest=-40d [ search index=b2bapps "*Order not fulfilled*" | dedup OrderID | fields OrderID ]| format maxresults = 1000 ]

and you have upped the limit. But you cannot increase maxresults above 10500.

0 Karma

somesoni2
Revered Legend

Try following

earliest=-40d [search index=b2bapps "*Order not fulfulled*" 
| stats count by OrderID | fields OrderID] 
| rex "String>(?<OrderUpdate>[^<]*)<" 
| eval Date=strftime(_time,"%d-%m-%Y") 
| where OrderUpdate not null 
| table transactID, Date, OrderUpdate
0 Karma

linu1988
Champion

Hello,
Could you try with this. The sub search needs to have [|search ... ] and the field actually you are comparing. Means the subsearch should return orderID instead of 2 different fields.

index=b2bapps "Order not fulfulled" | table OrderID|where  OrderID[| search earliest=-40d | rex "String>(?<orderupdate>[^<]*)<" | eval Date=strftime(_time,"%d-%m-%Y") | where OrderID not null]| table transactID, Date, OrderUpdate

If you have OrderID from some source type to compare with then you will definitely get the result.

0 Karma

linu1988
Champion

Updated with your new query, and I see you have table, which shouldn't be there with table.

0 Karma

rlautman
Path Finder

I have changed the sub query to include BuyersID but I am still getting the error 'error in table command: Invalid argument'

index=b2bapps "Order not fulfulled" | table OrderID [| search earliest=-40d | rex "String>(?[^<]*)<" | eval Date=strftime(_time,"%d-%m-%Y") | where OrderID not null]| table, transactID, Date, OrderUpdate

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!