First off, the phrase "doesn't work" conveys little information in the best of cases and should be banished. Describe your data and illustrate the output, then explain why the output is different fr...
See more...
First off, the phrase "doesn't work" conveys little information in the best of cases and should be banished. Describe your data and illustrate the output, then explain why the output is different from desired output unless it is painfully obvious. (See my Four Commandments below.) Back to your search. You already say that search does not meet your requirement. Why insist on using append? To get unique buildings in index events, you lookup any matching value, then exclude those matching events. What is left are events with unmatched buildings. Not only is this approach more semantic, but using lookup is also more efficient because that's a binary tree search. About that roomlookup_buildings.csv, have you defined a lookup to use this file? In Splunk, a lookup definition can be independent of lookup file, meaning you need a definition. (The lookup definition doesn't have the use the same name as the file, but must use the file as source. My convention is to name a lookup without .csv but that's up to you. I will assume that your definition is called roomlookup_buildings.csv.) Are the column buildings containing one value per row? (I will assume yes. There is no good reason not to.) What are those escaped quotation marks? Are they part of field value or do you simply use them to signal that between quotes are the values? (I will assume the values are between quotes.) If you have already defined a lookup, let's also call it roomlookup_buildings.csv; and let's assume that each row contains one value for building, i.e., buildings Aachen 1 Almanor 1 Almanor 2 Antara Further assume that your index search has these events: building_from_search1 request_unique_id Aachen 1 ID 1 Almanor 1 ID 2 Almanor 2 ID 2 Amsterdam ID 3 Then, you run | lookup roomlookup_buildings.csv buildings as building_from_search1 output buildings as matching_building This should give you building_from_search1 matching_building request_unique_id Aachen 1 Aachen 1 ID 1 Almanor 1 Almanor 1 ID 2 Almanor 2 Almanor 2 ID 2 Amsterdam ID 3 Apply the filter, | lookup roomlookup_buildings.csv buildings as building_from_search1 output buildings as matching_building
| where isnull(matching_building) This results in building_from_search1 matching_building request_unique_id Amsterdam ID 3 Then, apply stats to the whole thing index= buildings_core "Buildings updated in database*"
| rex "REQUEST_UNIQUE_ID:(?<request_unique_id>[^ ]+)"
| rex "Buildings updated in database:\s(?<buildings>\{[^}]+\})"
| eval buildings = replace(buildings, "[{}]", "") | eval buildings = split(buildings, ",")
| mvexpand buildings
| eval building_from_search1 = mvindex(split(buildings, ":"), 1)
| lookup roomlookup_buildings.csv buildings as building_from_search1 output buildings as matching_building
| where isnull(matching_building)
| stats values(building_from_search1) as unmatching_buildings by request_unique_id That mock data gives request_unique_id unmatching_buildings ID 3 Amsterdam Is this what you expect from that mock data? Here, I am illustrating four golden rules of asking an answerable question in data analytics, which I call Four Commandments: Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search that volunteers here do not have to look at. Illustrate the desired output from illustrated data. Explain the logic between illustrated data and desired output without SPL. If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious. Here is an emulation for you to play with and compare with real data. This emulation is used to generate the above mock data. If your real data (including lookup) is different, you need to carefully describe them. | makeresults format=csv data="building_from_search1, request_unique_id
Aachen 1, ID 1
Almanor 1, ID 2
Almanor 2, ID 2
Amsterdam, ID 3"
``` the above emulates
index= buildings_core "Buildings updated in database*"
| rex "REQUEST_UNIQUE_ID:(?<request_unique_id>[^ ]+)"
| rex "Buildings updated in database:\s(?<buildings>\{[^}]+\})"
| eval buildings = replace(buildings, "[{}]", "") | eval buildings = split(buildings, ",")
| mvexpand buildings
| eval building_from_search1 = mvindex(split(buildings, ":"), 1)
```