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
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.
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
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.
Updated with your new query, and I see you have table, which shouldn't be there with table.
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>(?