This challenge was first posted on Slack #puzzles channel This puzzle is based on a letter grid containing tangled words. The diagram was created in draw.io which can use XML documents to export and import diagrams. The challenge is to process the XML document using SPL to find the tangled words. This article contains spoilers! In fact, the whole article is a spoiler as it contains solutions to the puzzle. If you are trying to solve the puzzle yourself and just want some pointers to get you started, stop reading when you have enough, and return if you get stuck again, or just want to compare your solution to mine! Diagram source There are two main types of nodes in the XML documents. Some example nodes look like this: <mxCell id="f1ab-4f63" edge="1" parent="1" source="f9ed-7857" style="edgeStyle=orthogonalEdgeStyle;rounded=0;orthogonalLoop=1;jettySize=auto;html=1;fillColor=#88B8B8;strokeColor=#88B8B8;startArrow=none;startFill=0;endArrow=classic;endFill=1;" target="813f-6d2d" value=""> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="f69a-54d7" edge="1" parent="1" source="813f-6d2d" style="edgeStyle=orthogonalEdgeStyle;rounded=0;orthogonalLoop=1;jettySize=auto;html=1;fillColor=#88B8B8;strokeColor=#88B8B8;startArrow=none;startFill=0;endArrow=classic;endFill=1;" target="bdcf-6cdb" value=""> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="04b0-70ca" parent="1" style="whiteSpace=wrap;html=1;aspect=fixed;fontSize=30;" value="P" vertex="1"> <mxGeometry height="80" width="80" x="640" y="440" as="geometry" /> </mxCell> <mxCell id="0cd5-73fa" parent="1" style="whiteSpace=wrap;html=1;aspect=fixed;fontSize=30;" value="S" vertex="1"> <mxGeometry height="80" width="80" x="720" y="440" as="geometry" /> </mxCell> <mxCell id="2151-6ffe" parent="1" style="whiteSpace=wrap;html=1;aspect=fixed;fontSize=30;" value="O" vertex="1"> <mxGeometry height="80" width="80" x="720" y="360" as="geometry" /> </mxCell> The full document can be found here. Parsing XML Taking the full document as a single event, the first thing to do is to split it into separate mxCell events: ``` Use makeresults to create a copy of the whole XML document in _raw ``` ``` Convert single line mxCell nodes to have closing tags ``` | rex mode=sed max_match=0 "s/(?P<mxCell>\<mxCell )(?P<attr>[^\/\>]+)\/\>/\1\2><\/mxCell>/g" ``` Extract all the mxCell nodes into separate events ``` | rex max_match=0 "(?ms)(?<mxCell>\<mxCell .+?\<\/mxCell\>)" | fields - _raw | mvexpand mxCell Extract the cell id attribute and drop the header nodes (ids 0 and 1) | spath input=mxCell output=id path=mxCell{@id} | where len(id) > 1 Extract the source, target and value attributes and drop the mxCell field | spath input=mxCell output=source path=mxCell{@source} | spath input=mxCell output=target path=mxCell{@target} | spath input=mxCell output=value path=mxCell{@value} | fields - mxCell This gives us a set of events where some events have source and target ids and some events have values associated with ids. Non-lookup-self-lookup Using a technique I like to call "non-lookup-self-lookup", we need to find the values associated with the source and target ids of each "connection" event. (We could have saved the second part of the data to a lookup file and used the lookup command to retrieve the values, but this potentially requires additional permissions and capabilities.) The heart of this technique is to triplicate the events so that the three ids can be stored in the same field for the three different events. | eval row=mvrange(0,3) | mvexpand row | eval name=case(row=0, id, row=1, source, row=2, target) Now we can use eventstats to gather the list of values associated with the source and target names | eventstats list(eval(if(value != "",value, null()))) as letters by name Index-based processing loop We can use eventstats again to gather the list of letters associated with each connection, drop the redundant rows, and create a 2-letter word fragment | eventstats list(letters) as letters by id | where row=0 | eval letters=if(mvcount(letters)>1,mvjoin(letters,""),null()) We use stats to create lists of word fragments and their associated source and target. We use list() rather than values() to preserve order and keep any duplicates. | stats list(letters) as word list(source) as source list(target) as target Next, we want to process each word fragment in turn and determine if it can be bound to another word fragment. We do this by first creating a multivalue field with indexes for all the word fragments. | eval binds=mvrange(0,mvcount(source)) Then, for each word fragment, we want to find another word fragment which has a target which is equal to the current source. This indicates that the two word fragments can be bound together. The next step is a little involved, so I will go through it step-by-step. | foreach mode=multivalue binds [ | eval joining=mvfind(target,mvindex(source,<<ITEM>>)), Note the comma at the end of the line. This allows us to extend the eval command with more assignments without breaking the restriction of only one command in the foreach subsearch when in multivalue mode. Create a joined word (fragment) from the found word fragment and the current word fragment (minus its initial letter). joined=if(isnotnull(joining),mvjoin(mvappend(split(mvindex(word,joining),""),mvindex(split(mvindex(word,<<ITEM>>),""),1,-1)),""),null()), Create a new word list, replacing the current word fragment with an empty string new_word=if(isnotnull(joining),mvappend(if(<<ITEM>>=0,null(),mvindex(word,0,<<ITEM>>-1)),"",mvindex(word,<<ITEM>>+1,-1)),new_word), Update the word list, replacing the found word fragment with the new word fragment word=if(isnotnull(joining),mvappend(if(joining=0,null(),mvindex(new_word,0,joining-1)),joined,mvindex(new_word,joining+1,-1)),word), Update the new source list, replacing the current source with an empty string new_source=if(isnotnull(joining),mvappend(if(<<ITEM>>=0,null(),mvindex(source,0,<<ITEM>>-1)),"",mvindex(source,<<ITEM>>+1,-1)),source), source=new_source, Create a new target  list, replacing the found target with the current target new_target=if(isnotnull(joining),mvappend(if(joining=0,null(),mvindex(target,0,joining-1)),mvindex(target,<<ITEM>>),mvindex(target,joining+1,-1)),target), Update the target  list, replacing the current target with  an empty string target=if(isnotnull(joining),mvappend(if(<<ITEM>>=0,null(),mvindex(new_target,0,<<ITEM>>-1)),"",mvindex(new_target,<<ITEM>>+1,-1)),new_target) ] Remove the empty strings | mvexpand word | where word!="" | table word Job done? Looking at the resulting list of words, something is not quite right. There are a couple of words which make sense (SPLUNK, BORE, XML), but others do not (OCSIC is CISCO spelt backwards!). So, what is going on? Perhaps there are some clues in the diagram. If you save the XML to a file and open it in draw.io, you can drag the letter tiles around and follow the arrows to see what the words should be. Taking a closer look Looking at the XML more closely, you can see that some of the connection mxCells have slightly different style attributes to the others. For example, some have style attributes including: startArrow=none;startFill=0;endArrow=classic;endFill=1; Whereas others have style attributes including: startArrow=classic;startFill=1;endArrow=none;endFill=0; In the first instance, the style for the connection indicates that the arrowhead is closer to the target, and in the second instance, the style for the connection indicates that the arrowhead is closer to the source. This means that values for source and target should be switched in the second instance. Another little nugget you may have noticed from a closer look at the style attributes (and indeed the draw.io diagram) is that some of the connections have different stroke and fill colours. These can actually be used to group the connections into word groups. | stats list(letters) as word list(source) as source list(target) as target by strokeColor I will leave you to work out how to extract and apply this information to your solution to get the correct list of words. As a final “Easter egg”, you may or may not have noticed that the ids for the letter mxCell nodes contain the ASCII code for the lowercase version of the corresponding letter, e.g. “0cd5-73fa” contains “73” which is the ASCII code for lowercase “s”, and “2151-6ffe” contains “6f” which is the ASCII code for lowercase “o”. If you had worked this out, you could have perhaps avoided the non-lookup-self-lookup step! Summary The techniques outlined in this solution, non-lookup-self-lookups and index-based processing loops, can be applied in multiple usecases and may provide solutions to some more complex scenarios. Have questions or thoughts? Comment on this article or in Slack #puzzles channel. Whichever you prefer. ... View more

Advent of Code In order to participate in these challenges, you will need to register with the Advent of Code site, so that you can retrieve your own datasets for the puzzles. When you get an answer, you will need to submit it to the Advent of Code site to determine whether the answer is correct. I have already completed the 2025 set using python, so I will know when my SPL generates the correct result. Day 4 Each day's puzzle is split into two parts; part one is usually easier than part two, and you cannot normally reach part two until you have successfully completed part one. Day 4 is about removing characters from a pattern depending on the surrounding locations. Please visit the website for full details of the puzzle. This article contains spoilers! In fact, the whole article is a spoiler as it contains solutions to the puzzle. If you are trying to solve the puzzle yourself and just want some pointers to get you started, stop reading when you have enough, and return if you get stuck again, or just want to compare your solution to mine! Part One As with all the Advent of Code puzzles, the description of the problem is aided by some example input; in this case, the input is grid showing where rolls of paper are placed. The aim of the puzzle is to determine, for your own dataset, how many rolls of paper can be removed in the first instance. Initialising your data The first thing to do is initialise your data. One way to do this is to save it to a csv file and use inputlookup to load it. Alternative, you could just use makeresults (as I have done here), and set a field to the data: | makeresults | fields - _time | eval _raw="..@@.@@@@. @@@.@.@.@@ @@@@@.@.@@ @.@@@@..@. @@.@@@@.@@ .@@@@@@@.@ .@.@.@.@@@ @.@@@.@@@@ .@@@@@@@@. @.@.@@@.@." The next step is to break the data up into separate events, | rex max_match=0 "(?<row>\S+)" | mvexpand row Interpreting the data Each position in the row represents either a roll of paper, designated by '@', or an empty space, designated by '.'. To determine whether the paper can be removed, the 8 surrounding positions have to be checked, so use streamstats to gather the rows above and below. | append [| stats count as row | eval row=null() ] | streamstats current=f last(row) as real_row | streamstats current=f last(real_row) as previous_row | rename row as next_row | rename real_row as row | where isnotnull(row) | table previous_row row next_row Note, adding an extra event with append and removing the first event (which does not have a prior row) keeps the number of events correct. At this point, it is very similar to the start of Gabriel's solution. Solving Part One Part One is looking for the number of rolls of paper that can be removed, i.e. those with fewer than four rolls in the eight adjacent positions (orthogonally and diagonally). Start by determining how many positions there are in each row, and initialise the total. | eval max_y=len(row) | eval y=mvrange(1,max_y+1) | eval total=0 For each position in the row, initialise the count. | foreach mode=multivalue y [ | eval count=0, Note the use of multivalue mode with the foreach command - there is a restriction in this mode that only one command is allowed, hence the use of the eval command, which can have multiple field assignments, separated by commas. The field being used has been set up to hold an index into the strings for each of the rows under consideration. If there is a prior row, check the positions before, equal and after the current position. count=if(isnotnull(previous_row), if(substr(row,<<ITEM>>,1)="@",if(<<ITEM>>>1, if(substr(previous_row,<<ITEM>>-1,1)="@",count+1, count), count), count), count), count=if(isnotnull(previous_row), if(substr(row,<<ITEM>>,1)="@",if(substr(previous_row,<<ITEM>>,1)="@",count+1, count), count), count), count=if(isnotnull(previous_row), if(substr(row,<<ITEM>>,1)="@",if(<<ITEM>><max_y, if(substr(previous_row,<<ITEM>>+1,1)="@",count+1, count), count), count), count), Check the positions before, and after the current position on the current row. count=if(substr(row,<<ITEM>>,1)="@",if(<<ITEM>>>1, if(substr(row,<<ITEM>>-1,1)="@",count+1, count), count), count), count=if(substr(row,<<ITEM>>,1)="@",if(<<ITEM>><max_y, if(substr(row,<<ITEM>>+1,1)="@",count+1, count), count), count), If there is a subsequent row, check the positions before, equal and after the current position. count=if(isnotnull(next_row), if(substr(row,<<ITEM>>,1)="@",if(<<ITEM>>>1, if(substr(next_row,<<ITEM>>-1,1)="@",count+1, count), count), count), count), count=if(isnotnull(next_row), if(substr(row,<<ITEM>>,1)="@",if(substr(next_row,<<ITEM>>,1)="@",count+1, count), count), count), count=if(isnotnull(next_row), if(substr(row,<<ITEM>>,1)="@",if(<<ITEM>><max_y, if(substr(next_row,<<ITEM>>+1,1)="@",count+1, count), count), count), count), If the count from all these positions is less than 4, this paper roll can be moved, so increment the total. total=if(substr(row,<<ITEM>>,1)="@" and count < 4, total+1, total) ] Now, simply sum the totals from all the rows. | stats sum(total) as total Part Two The second part of the puzzle requires that again we determine, for your own dataset, how many paper rolls can be removed, but, additionally, once a paper roll has been removed, this may free up other paper rolls, so that they too can be removed. We have to keep going until no more paper rolls can be removed. Solving Part Two One approach to this is to copy and paste the code from Part One, multiple times, adjusting the grid representing where the remaining rolls of paper are until no more paper rolls have been removed. But how many times would you need to do this? For the test data, there would be 9 iterations of the code, but who knows how many times your own dataset would require. Not only would this be a laborious process, your search might breach some limits e.g. time or memory, and/or seriously compromise your Splunk instance. An alternative to this is to reconstruct the search so that it can be iterated using a foreach command. A consequence of this is that only streaming commands can be used. Iterating with foreach command Can we just apply a foreach iteration loop around the Part One solution? If we try something like this: | foreach 1 2 3 4 5 6 7 8 9 [ ``` create previous and next rows for current rows ``` ... We hit an immediate problem because streamstats is not allowed in a foreach subsearch. OK, can we create the previous and next rows before the foreach iteration loop? Something like this: ``` create previous and next rows for current row ``` | foreach 1 2 3 4 5 6 7 8 9 [ ``` count neighbours for each roll in current row ``` ``` remove roll if neighbours less than 4 ``` ... This looks a little more promising, except what happens if we remove a roll from row 1. This is simple enough to do for the current row (row 1), but row 1 is also the previous row for row 2, and, as it stands, we have no way to change this as it is in a different event. This is the crux of the problem with using foreach to iterate over the solution to Part One. We need to go back to treating the problem as a single event. Start from the beginning The first thing to do is create a multi-value field with one value for each row of the grid. (If you have read your data in from a csv file, you may need to use a stats command with list() aggregate function to gather the rows of the grid into a multi-value field.) | rex max_match=0 "(?<rows>\S+)" | fields - _raw Now, we need a way to reference each row and column of the grid. Starting with the rows, in order to iterate over the rows, we can use field names. We could just count the rows and set up a hard-coded iteration over an appropriate number of field names (much like shown earlier for iterating the Part One solution). However, it would be better to dynamically create the appropriate number of fields, so we can iterate over them. So, start by determining the maximum value in this direction (I have called this the x-direction). | eval max_x=mvcount(rows) Now, using mvrange(), we can create a multi-value field with enough entries for each of the rows. | eval list=mvrange(0,max_x) We can now use this field in the by clause of a chart command to create the required fields. | chart values(eval(0)) as zero by list OK, not quite what we wanted, because the list has to be the second field in the by clause to be used to generate field names. | eval name="day_4" | chart values(eval(0)) as zero by name list useother=f limit=0 An alternative syntax for the same outcome could look like this: | eval name="day_4" | chart values(eval(0)) as zero over name by list useother=f limit=0 Note that over has the first field listed in the previous by clause and the by clause now just has one field listed. We have a set of fields names which we could use to iterate over the rows of data, but we no longer have the data. A simple way to get the field names without losing the data entirely is to use appendpipe to generate the field names in a subsearch. | eval max_x=mvcount(rows) | appendpipe [ | eval list=mvrange(0,max_x) | eval name="day_4" | chart values(eval(0)) as zero by name list useother=f limit=0 | fields - name ] | where isnotnull(max_x) Remember to drop the name field (it is only required for the chart command) and drop the appended event (the generated fields still exist even when they have no data in them). Now, we can list them in a foreach command. Since they are all just numbers this is not so easy, but we can solve this by tweaking the appendpipe subsearch to prepend the number with a known value (I have used 'f' in this instance). | appendpipe [ | eval list=mvrange(0,max_x) | mvexpand list | eval list="f".list | eval name="day_4" | chart values(eval(0)) as zero by name list useother=f limit=0 | fields - name ] | where isnotnull(max_x) Now we can use a wildcard on the foreach command to list all the relevant fields | foreach f* We can do a similar thing for the fields to iterate over the columns, but since there are the same number of columns as rows, we could reuse the same set of field names. | eval max_y=len(mvindex(rows,0)) Using the list of fields, we can process each row, finding the previous and next rows | foreach f* [ | eval x=substr("<<FIELD>>",2,len("<<FIELD>>")-1) | eval row=mvindex(rows,x) | eval previous_row=if(x > 0, mvindex(rows, x - 1), null()) | eval newt_row=if(x < max_x-1, mvindex(rows, x + 1), null()) ] Now, we can try to iterate over the columns in the rows using the same set of fields. Let us start by converting each character to either a zero (0) if it is a dot (.), or a one (1) if it is a roll of paper (@). | foreach f* [ | eval x=substr("<<FIELD>>",2,len("<<FIELD>>")-1) | eval row=mvindex(rows,x) | eval previous_row=if(x > 0, mvindex(rows, x - 1), null()) | eval newt_row=if(x < max_x-1, mvindex(rows, x + 1), null()) | eval new_row="" | foreach f* [ | eval y=substr("<<FIELD>>",2,len("<<FIELD>>")-1) | eval char=substr(row,y+1,1) | eval new_row=new_row.if(char=".","0","1") ] | eval new_rows=mvappend(new_rows,new_row) ] That did not turn out as intended 0000000000 1111111111 1111111111 1111111111 1111111111 1111111111 0000000000 1111111111 1111111111 0000000000 Each (new) row is made up of the same number depending on what the n-th character of the original row was, i.e. the first character of the first row, the second character of the second row, etc.. This shows that in the inner foreach, <<FIELD>> is not being changed for each field in the list. One way to get around this, is to determine the index into the row independently of the field name. | foreach f* [ | eval x=substr("<<FIELD>>",2,len("<<FIELD>>")-1) | eval row=mvindex(rows,x) | eval previous_row=if(x > 0, mvindex(rows, x - 1), null()) | eval newt_row=if(x < max_x-1, mvindex(rows, x + 1), null()) | eval new_row="" | eval y=0 | foreach f* [ | eval y=y+1 | eval char=substr(row,y,1) | eval new_row=new_row.if(char=".","0","1") ] | eval new_rows=mvappend(new_rows,new_row) ] We can use the same approach for the row index (x) so that, when we come to iterating multiple times, we are not dependent on the field name in <<FIELD>> | eval x=0 | foreach f* [ | eval row=mvindex(rows,x) | eval previous_row=if(x > 0, mvindex(rows, x - 1), null()) | eval newt_row=if(x < max_x-1, mvindex(rows, x + 1), null()) | eval new_row="" | eval y=0 | foreach f* [ | eval y=y+1 | eval char=substr(row,y,1) | eval new_row=new_row.if(char=".","0","1") ] | eval new_rows=mvappend(new_rows,new_row) | eval x=x+1 ] Now we can iterate over the set of rows multiple times until no more rolls can be removed | eval total=0 | foreach 1 2 3 4 5 6 7 8 9 [ | eval rows=new_rows | eval new_rows=null() | eval x=0 | foreach f* [ | eval row=mvindex(rows,x) | eval previous_row=if(x > 0, mvindex(rows, x - 1), null()) | eval next_row=if(x < max_x-1, mvindex(rows, x + 1), null()) | eval new_row="" | eval y=0 | foreach f* [ | eval y=y+1 | eval char=substr(row,y,1) | eval count=if(char="1",if(isnotnull(previous_row) and y > 1, tonumber(substr(previous_row,y - 1, 1)), 0), 0) | eval count=if(char="1",if(isnotnull(previous_row), count + tonumber(substr(previous_row,y, 1)), count), count) | eval count=if(char="1",if(isnotnull(previous_row) and y < max_y, count + tonumber(substr(previous_row,y + 1, 1)), count), count) | eval count=if(char="1",if(y > 1, count + tonumber(substr(row,y - 1, 1)), count), count) | eval count=if(char="1",if(y < max_y, count + tonumber(substr(row,y + 1, 1)), count), count) | eval count=if(char="1",if(isnotnull(next_row) and y > 1, count + tonumber(substr(next_row,y - 1, 1)), count), count) | eval count=if(char="1",if(isnotnull(next_row), count + tonumber(substr(next_row,y, 1)), count), count) | eval count=if(char="1",if(isnotnull(next_row) and y < max_y, count + tonumber(substr(next_row,y + 1, 1)), count), count) | eval total=if(char="1" and count < 4, total + 1, total) | eval new_row=new_row.if(char="1" and count < 4,"0", char) ] | eval new_rows=mvappend(new_rows,new_row) | eval x=x+1 ] ] Warning! Approach with caution! This approach works for the test data, but with the much larger dataset, if yours is anything like mine, even doubling up to 18 iterations breaks Splunk. Do not try this yourself! | eval total=0 | foreach 1 2 [ | foreach 1 2 3 4 5 6 7 8 9 [ Finding a better way If you had run the process on your dataset for just a few times, e.g. the 9 times in the foreach, you may have noticed that the number of paper rolls left rapidly diminishes at the start, so we are left with an increasingly sparsely populated grid. This gives us a clue to a different approach. Rather than keeping the whole grid throughout the process, we only need to keep track of where the remaining paper rolls are. The coordinates of where a paper roll is can be represented by the row and column. Given that the number of columns is less than a thousand (1000), we can multiply the row number by 1000 and add the column number to give a value that can easily be interpreted both visually and mathematically. For example, row 1 has a paper roll in column 3, therefore this can be represented by 1003, and row 10 has a paper roll in the first column, therefore this can be represented by 10001, etc. Using this process, we can build a string of all the coordinates of the paper rolls. | rex max_match=0 "(?<rows>\S+)" | fields - _raw | mvexpand rows | eval columns=mvrange(1,len(rows)+1) | streamstats count as row | eval paper="" | foreach mode=multivalue columns [ | eval paper=paper.if(substr(rows,<<ITEM>>,1)=="@",",".(row*1000+<<ITEM>>),"") ] | stats values(paper) as paper max(row) as max_x values(eval(len(rows))) as max_y | eval paper=mvjoin(paper,"")."," Now, to check the surrounding locations, we can generate the coordinates of the neighbouring positions and check whether they exist in the list. | eval paper_list=split(trim(paper,","),",") | foreach mode=multivalue paper_list [ | eval count=0, x=floor(<<ITEM>>/1000), y=<<ITEM>>%1000, other_x=if(x > 1, x-1, null()), other_y=if(isnotnull(other_x), if(y > 1, y-1, null()), null()), count=if(isnotnull(other_x) and isnotnull(other_y) and match(paper, ",".(other_x*1000+other_y).","), count+1, count), other_x=if(x > 1, x-1, null()), other_y=if(isnotnull(other_x), y, null()), count=if(isnotnull(other_x) and isnotnull(other_y) and match(paper, ",".(other_x*1000+other_y).","), count+1, count), I have just shown how to check a couple of locations, I will leave the rest for you to complete. Note that I have included the delimiting commas (,) on either side of the coordinate in the match to ensure false positives are not made. Having counted the neighbours, we can either increase the total or add the location to a new list ready for the next iteration. | eval total=0 | eval paper_list=split(trim(paper,","),",") | eval new_paper="," | foreach mode=multivalue paper_list [ | eval count=0, x=floor(<<ITEM>>/1000), y=<<ITEM>>%1000, other_x=if(x > 1, x-1, null()), other_y=if(isnotnull(other_x), if(y > 1, y-1, null()), null()), count=if(isnotnull(other_x) and isnotnull(other_y) and match(paper, ",".(other_x*1000+other_y).","), count+1, count), other_x=if(x > 1, x-1, null()), other_y=if(isnotnull(other_x), y, null()), count=if(isnotnull(other_x) and isnotnull(other_y) and match(paper, ",".(other_x*1000+other_y).","), count+1, count), ```...``` new_paper=if(count < 4, new_paper, new_paper.<<ITEM>>.","), total=if(count < 4, total+1, total) ] | eval paper=new_paper Good enough? Putting this new approach into nested foreach commands, can we iterate enough times to get an answer without breaking Splunk? For my data and environment, this still caused problems. So, how else could the process be optimised? If we consider the end of the process, we will have a number of paper rolls which cannot be removed; the only thing that would change this situation is if one of the rolls could be removed and by removing this roll we can only be potentially impacting the neighbouring locations. This means that, rather than processing a list of all the locations of paper rolls, we only need to process the list of locations which are neighbours to a paper roll that has just been removed in the previous iteration. (We still need to keep a list of all the current locations in order for us to be able to check (and count) them.) Now, the process involves keeping track of the locations where rolls have been removed, which locations were included in the count if the roll was removed, compiling a list of considered locations, then filtering out any location where a roll had been removed. | eval total=0 | eval iterations=0 | eval original_paper = paper | eval paper_list=split(trim(paper,","),",") | eval new_paper="," | eval removed_paper="," | foreach mode=multivalue paper_list [ | eval count=0, x=floor(<<ITEM>>/1000), y=<<ITEM>>%1000, considered_paper="", other_x=if(x > 1, x-1, null()), other_y=if(isnotnull(other_x), if(y > 1, y-1, null()), null()), count=if(isnotnull(other_x) and isnotnull(other_y) and match(original_paper, ",".(other_x*1000+other_y).","), count+1, count), considered_paper=if(isnotnull(other_x) and isnotnull(other_y) and match(original_paper, ",".(other_x*1000+other_y).","), considered_paper.(other_x*1000+other_y).",", considered_paper), other_x=if(x > 1, x-1, null()), other_y=if(isnotnull(other_x), y, null()), count=if(isnotnull(other_x) and isnotnull(other_y) and match(original_paper, ",".(other_x*1000+other_y).","), count+1, count), considered_paper=if(isnotnull(other_x) and isnotnull(other_y) and match(original_paper, ",".(other_x*1000+other_y).","), considered_paper.(other_x*1000+other_y).",", considered_paper), ```...``` next_paper=if(count < 4, new_paper.considered_paper, new_paper), new_paper=if(count < 4 and len(next_paper) > 1, ",".mvjoin(mvdedup(split(trim(next_paper, ","), ",")), ",").",", next_paper), removed_paper=if(count < 4, removed_paper.<<ITEM>>.",", removed_paper), total=if(count < 4, total+1, total) ] | eval paper_list=split(trim(new_paper, ","), ",") | eval paper="," | foreach mode=multivalue paper_list [ | eval paper=if(match(removed_paper, ",".<<ITEM>>.","), paper, paper.<<ITEM>>.",") ] | eval paper_list=split(trim(original_paper, ","), ",") | eval original_paper="," | foreach mode=multivalue paper_list [ | eval original_paper=if(match(removed_paper, ",".<<ITEM>>.","), original_paper, original_paper.<<ITEM>>.",") ] Putting this re-optimised approach into nested foreach commands, allowed me to iterate enough times to get an answer without breaking Splunk. It is worth noting that using strings to maintain the list rather than a multi-value field, also helped optimise the process. Summary With multiple iterations, searches with large field values and/or large multi-value fields, can become Splunk-killers; in order to avoid compromising the Splunk server instance, searches may need to be optimised to reduce these impacts, even if the resulting search looks more complicated. Have questions or thoughts? Comment on this article or in Slack #puzzles channel. Whichever you prefer. ... View more

Advent of Code In order to participate in these challenges, you will need to register with the Advent of Code site, so that you can retrieve your own datasets for the puzzles. When you get an answer, you will need to submit it to the Advent of Code site to determine whether the answer is correct. I have already completed the 2025 set using python, so I will know when my SPL generates the correct result. Day 3 Each day's puzzle is split into two parts; part one is usually easier than part two, and you cannot normally reach part two until you have successfully completed part one. Day 3 is about finding the largest number from sequences of digits without changing the order of the digits. Please visit the website for full details of the puzzle. This article contains spoilers! In fact, the whole article is a spoiler as it contains solutions to the puzzle. If you are trying to solve the puzzle yourself and just want some pointers to get you started, stop reading when you have enough, and return if you get stuck again, or just want to compare your solution to mine! Part One As with all the Advent of Code puzzles, the description of the problem is aided by some example input; in this case, the input is sequences of digits. The aim of the puzzle is to determine, for your own dataset, the largest 2-digit number formed by digits in the sequence without changing the order of the digits. Initialising your data The first thing to do is initialise your data. One way to do this is to save it to a csv file and use inputlookup to load it. Alternative, you could just use makeresults (as I have done here), and set a field to the data: | makeresults | fields - _time | eval _raw="987654321111111 811111111111119 234234234234278 818181911112111" The next step is to break the data up into separate events, | rex max_match=0 "(?<batteries>\S+)" | table batteries | mvexpand batteries Interpreting the data Each digit needs to be processed separately, so split the sequence into a multi-value field and prepare an empty string for the result. | eval new_batteries=split(batteries,"") | eval result="" Solving Part One Part One is looking for a 2 digit number, so iterate over the sequence of remaining digits twice. | eval digits=2 | foreach 0 1 [ For each iteration, we need to find the highest digit, whilst leaving room in the string for the remaining iterations. Firstly, determine how much of the current sequence to search. | eval count=mvcount(new_batteries)-digits+<<FIELD>> | eval test_batteries=mvindex(new_batteries,0,count)  Search the current string for the first match of the digits in descending order. | eval high_index=coalesce(mvfind(test_batteries,"9"),mvfind(test_batteries,"8"),mvfind(test_batteries,"7"),mvfind(test_batteries,"6"),mvfind(test_batteries,"5"),mvfind(test_batteries,"4"),mvfind(test_batteries,"3"),mvfind(test_batteries,"2"),mvfind(test_batteries,"1")) Since mvfind() returns null() if there is no match, coalesce() is used to get the index of the highest digit. Concatenate the highest digit to the result so far. | eval result=result.mvindex(new_batteries,high_index) Reduce the list of digits to be searched in the next iteration, starting after the digit just removed, until the end of the list. | eval new_batteries=mvindex(new_batteries,high_index+1,-1) ]  Now, simply sum the resulting numbers. | stats sum(result) as total Part Two Part Two is looking for a 12 digit number, so iterate over the sequence of remaining digits twelve times. Using the same approach Using the same approach as for Part One, this time the number of digits in the result is 12, so we need to iterate the process twelve times. | eval digits=12 | foreach 0 1 2 3 4 5 6 7 8 9 10 11 [ Summary Sometimes, part two is simply just a larger version of part one, so it is important to have an efficient process. Have questions or thoughts? Comment on this article or in Slack #puzzles channel. Whichever you prefer. Never miss a new post. Check out this short guide on how to subscribe to the blog and get updates.  ... View more

Advent of Code In order to participate in these challenges, you will need to register with the Advent of Code site, so that you can retrieve your own datasets for the puzzles. When you get an answer, you will need to submit it to the Advent of Code site to determine whether the answer is correct. I have already completed the 2025 set using python, so I will know when my SPL generates the correct result. Day 2 Each day's puzzle is split into two parts; part one is usually easier than part two, and you cannot normally reach part two until you have successfully completed part one. Day 2 is about finding numbers with repeating sequences of digits. Please visit the website for full details of the puzzle. This article contains spoilers! In fact, the whole article is a spoiler as it contains solutions to the puzzle. If you are trying to solve the puzzle yourself and just want some pointers to get you started, stop reading when you have enough, and return if you get stuck again, or just want to compare your solution to mine! Part One As with all the Advent of Code puzzles, the description of the problem is aided by some example input; in this case, the input is a series of number ranges which represent product ids. The aim of the puzzle is to determine, for your own dataset, the sum of the invalid ids that appear in the number ranges. For part one, an invalid id is a number that, when split two parts, both parts are made up of same sequence of digits. Initialising your data The first thing to do is initialise your data. One way to do this is to save it to a csv file and use inputlookup to load it. Alternative, you could just use makeresults (as I have done here), and set a field to the data: | makeresults | fields - _time | eval _raw="11-22,95-115,998-1012,1188511880-1188511890,222220-222224,1698522-1698528,446443-446449,38593856-38593862,565653-565659,824824821-824824827,2121212118-2121212124" The next step is to break the data up into separate events, | rex max_match=0 "(?<range>\d+\-\d+)" | mvexpand range Interpreting the data Using our good friend rex, parse the data to get the start and end of the range. | rex field=range "(?<start>\d+)\-(?<end>\d+)" | table start end Solving Part One Rather than checking each number in each range to see if it is invalid, my basic approach was to approach from the other side. That is, find a number that is made up from a repeated sequence of digits and check if it in the required range. From the problem description, we know that the range must include numbers with an even number of digits, i.e. either the start has an even number of digits, or the end has an even number of digits, or both, or that the difference in the number of digits is greater than 1 e.g. 1-101 while start and end have an odd number of digits, the range includes numbers with an even number of digits (10-99). | eval start_len=len(start) | eval end_len=len(end) | where start_len%2 = 0 or end_len%2 = 0 or abs(start_len-end_len)>1 Having removed ranges which have no possibility of invalid id, we can reset the start and end of the remaining ranges. There are three possibilities: The number of digits are different and the start has an even number of digits The number of digits are different and the start has an odd number of digits The number of digits are the same | eval case=case(start_len != end_len and start_len%2 == 0, 1, start_len != end_len and start_len%2 == 1, 2, true(), 0) For the first case, we can reset the end of the range to be largest number with the same number of digits as the start. | eval end=if(case = 1, pow(10, start_len) - 1, end) For the second case, we can reset the start of the range to be lowest number with the same number of digits as the end. In addition to this, since we know this will have an even number of digits, we can construct the first invalid id in this range. | eval start=if(case = 2, pow(10, start_len) + pow(10, floor(start_len / 2)), start) Recalculate the length of the start and end of each range. | eval start_len=len(start) | eval end_len=len(end) Determine the first half of the digits of start and end. This is done by dividing the number by 10 to the power of half the length e.g. if the number is a four digit number (between 1000 and 9999), dividing by 100 (10 to the power of 2) will give the first two digits. | eval half_start=floor(start/pow(10,start_len/2)) | eval half_end=floor(end/pow(10,end_len/2)) From this, we can generate a range of possible first halves of the invalid ids. | eval range=mvrange(half_start, half_end+1) | mvexpand range For each possible first half, generate a possible id and validate it is still in the original range. | eval possible=range.range | where possible<=end and possible>=start  Now, simply add all the invalid ids. | stats sum(possible) as invalid Part Two The second part of the puzzle requires that again we determine, for your own dataset, the sum of the invalid ids that appear in the number ranges, only this time the definition of what an invalid id has changed. Instead of it being a number which is made up of a duplicated sequence of digits, the digits could be repeated at least twice. Start the same way Using the same approach as for Part One, this time do not filter out the ranges without an even number of digits. | rex max_match=0 "(?<range>\d+\-\d+)" | mvexpand range | rex field=range "(?<start>\d+)\-(?<end>\d+)" | table start end | eval start_len=len(start) | eval end_len=len(end) Now we can determine the range of the number of digits in the number ranges. | eval length=mvrange(start_len, end_len+1) | mvexpand length For each length,  we want a list of possible factors. The simplest way to do this is to get a list from 1 to half the length (anything above half will not be a factor except the length itself), and eliminate any that do not create a zero modulus. | eval log_range=mvrange(1,floor(length/2)+1) | mvexpand log_range | where length%log_range == 0  Reset the start and end of the range for each possible length in a similar manner to that used in part one. | eval start=if(start_len<length,pow(10, end_len-1),start) | eval end=if(end_len>length,pow(10, start_len) - 1,end) Using the length of the repeating part of the number, find the range of possible start and end numbers  | eval possible_start=substr(start,1,log_range) | eval possible_end=substr(end,1,log_range) | eval possibles=mvrange(possible_start, possible_end+1) | mvexpand possibles Determine the number of times the digits are repeated and construct a possible invalid id,  and validate it is still in the original range. | eval reps=floor(length/log_range) | eval rep_range=mvrange(0,reps) | eval new_possible="" | foreach mode=multivalue rep_range [| eval new_possible=new_possible.possibles] | where new_possible<=end and new_possible>=start Now we can simply sum the invalid ids. | stats sum(new_possible) as invalid Job done? Well, not quite. It turns out that, the total comes out as being too high. So, what is going on? Which ones are we double-counting? Suppose that the original range is 3428 to 4981. Within this range is 4444. Now, 4444 can be generated as four 4's or two 44's, hence the double-counting. To eliminate these duplicates, we can simple gather the values of the possibilities for each number range (eliminating duplicates), before summing the possible invalid ids. | stats values(new_possible) as possible by start end | stats sum(possible) as invalid Summary By inverting the logic, we can build ranges of possible invalid ids before checking whether they are in range, thereby greatly reducing the number of ids we need to check. Have questions or thoughts? Comment on this article or in Slack #puzzles channel. Whichever you prefer.   If you’re not subscribed, you’re probably missing something good. Fix that!  ... View more

Advent of Code In order to participate in these challenges, you will need to register with the Advent of Code site, so that you can retrieve your own datasets for the puzzles. When you get an answer, you will need to submit it to the Advent of Code site to determine whether the answer is correct. I have already completed the 2025 set using python, so I will know when my SPL generates the correct result. Day 1 Each day's puzzle is split into two parts; part one is usually easier than part two, and you cannot normally reach part two until you have successfully completed part one. Day 1 is about opening a safe using a dial. Please visit the website for full details of the puzzle. This article contains spoilers! In fact, the whole article is a spoiler as it contains solutions to the puzzle. If you are trying to solve the puzzle yourself and just want some pointers to get you started, stop reading when you have enough, and return if you get stuck again, or just want to compare your solution to mine! Part One As with all the Advent of Code puzzles, the description of the problem is aided by some example input; in this case, the input is a series of left and right turns of the dial. The aim of the puzzle is to determine, for your own dataset, how many times the dial stops at zero. Initialising your data The first thing to do is initialise your data. One way to do this is to save it to a csv file and use inputlookup to load it. Alternative, you could just use makeresults (as I have done here), and set a field to the data: | makeresults | fields - _time | eval _raw="R50 L68 L30 R48 L5 R60 L55 L1 L99 R14 L82" Since the puzzle says that the dial starts at 50, I have included "R50" as the first line of the input data. The next step is to break the data up into separate events (if you have loaded from a csv file, your data may already be in separate events.) | rex max_match=0 "(?<line>\S+)" | fields - _raw | mvexpand line Interpreting the data Using our good friend rex, parse the data to get the direction and number of clicks. Use the direction to convert the count to a signed integer. | rex field=line "(?<direction>\w)(?<count>\d+)" | eval clicks=count%100 | eval clicks=clicks*if(direction="L",-1,1) | table direction count clicks Note the use of modulus 100. This is not strictly speaking necessary as we use modulus later on, and while the sample data does not have any large number, it is almost certain that your real data will (this is often the nature of the Advent of Code puzzles). Solving Part One We now have a set of movements for the dial which we can run through looking for zeroes. The simplest way to do this is with streamstats to get a rolling sum of all the values, and count how many times we end up with zero. | streamstats sum(clicks) as dial | eval dial=dial%100 | stats sum(eval(if(dial=0,1,0))) as zeroes This time the modulus 100 is required. Part Two The second part of the puzzle requires that we count the number of times the dial points at zero, not just stopping there. This sounds simple; if the movement is more than 100 clicks, include the number of 100 clicks there are in the movement as well. Start the same way Using the same approach as for Part One, this time find the number of multiples of 100 there are in the movement, before taking the modulus. | rex max_match=0 "(?<line>\S+)" | fields - _raw | mvexpand line | rex field=line "(?<direction>\w)(?<count>\d+)" | eval spins=floor(abs(count)/100) | eval clicks=count%100 | eval clicks=clicks*if(direction="L",-1,1) | streamstats sum(clicks) as dial Now we can add the number of spins to the number of times the dial is pointing to zero. | eval dial=dial%100 | eval zeroes=if(dial=0, 1, 0)+spins | stats sum(zeroes) as allzeroes Job done? Well, not quite. It turns out that, the total comes out as being too low. So, what is going on? Which ones are we missing? When the dial turns passes zero, we are not currently counting it. So, how do we pick up on this. Well, if we are turning the dial to the right and the number we end up on is less than the previous position, we must have passed through zero (in a clockwise direction). Similarly, if we are turning the dial to the left and the number we end up on is greater than the previous position, we must have passed through zero (in a counter-clockwise direction). | streamstats sum(clicks) as dial | eval dial=dial%100 | streamstats window=1 current=f last(dial) as previous | eval zeroes=if(dial=0, 1, if(direction="L", if(dial > previous, 1, 0), if(dial < previous, 1, 0))) | eval zeroes=zeroes+spins | stats sum(zeroes) as allzeroes Nearly there Now we are getting a number that is too high! We must be double counting some situations. Consider, when turning the dial to the left, when the previous position is pointing at zero. The previous position has been counted as a zero (from the first part of the if), and the new position will be counted as having passed through zero - which it did not! To fix this, we can tweak the if function to take the previous position into account. | streamstats window=1 current=f last(dial) as previous | eval zeroes=if(dial=0, 1, if(direction="L", if(dial > previous and previous != 0, 1, 0), if(dial < previous, 1, 0))) | eval zeroes=zeroes+spins | stats sum(zeroes) as allzeroes Summary The devil is in the detail! A common theme to the Advent of Code puzzles is that, hidden in your real data, there are gotchas which are put there to try and trip you up. Have questions or thoughts? Comment on this article or in Slack #puzzles channel. Whichever you prefer. Want first dibs on fresh content? Follow this quick guide to subscribe and get updates instantly.   ... View more

[Puzzles] Solve, Learn, Repeat: Unmerging HTML Tables For a previous puzzle, I needed some sample data, and while researching for this, I came across the data I was interested in, but it was in an HTML Table. This inspired me to create this puzzle. The challenge is to take an HTML table and convert it to a Splunk table. The HTML (your input) looks like this: <table><tbody> <tr><td colspan="5">1980s</td></tr> <tr><th>Imaged</th><th>Published</th><th>Name</th><th>Designation</th><th>Discoverers</th></tr> <tr><td>30 December 1985</td><td>9 January 1986</td><td>Puck</td><td>Uranus XV</td><td rowspan="7">Synnott, Voyager 2</td></tr> <tr><td rowspan="2">3 January 1986</td><td rowspan="6">16 January 1986</td><td>Juliet</td><td>Uranus XI</td></tr> <tr><td>Portia</td><td>Uranus XII</td></tr> <tr><td>9 January 1986</td><td>Cressida</td><td>Uranus IX</td></tr> <tr><td rowspan="3">13 January 1986</td><td>Desdemona</td><td>Uranus X</td></tr> <tr><td>Rosalind</td><td>Uranus XIII</td></tr> <tr><td>Belinda</td><td>Uranus XIV</td></tr> <tr><td rowspan="2">20 January 1986</td><td rowspan="3">27January 1986</td><td>Cordelia</td><td>Uranus VI</td><td rowspan="2">Terrile, Voyager 2</td></tr> <tr><td>Ophelia</td><td>Uranus VII</td></tr> <tr><td>23 January 1986</td><td>Bianca</td><td>Uranus VIII</td><td>Smith, Voyager 2</td></tr> <tr><th>Imaged</th><th>Published</th><th>Name</th><th>Designation</th><th>Discoverers</th></tr> </tbody></table> This can be crudely rendered like this: +------------------+-----------------+-----------+-------------+--------------------+ | 1980s | +==================+=================+===========+=============+====================+ | Imaged | Published | Name | Designation | Discoverers | +==================+=================+===========+=============+====================+ | 30 December 1985 | 9 January 1986 | Puck | Uranus XV | Synnott, Voyager 2 | +------------------+-----------------+-----------+-------------+ + | 3 January 1986 | 16 January 1986 | Juliet | Uranus XI | | + + +-----------+-------------+ + | | | Portia | Uranus XII | | +------------------+ +-----------+-------------+ + | 9 January 1986 | | Cressida | Uranus IX | | +------------------+ +-----------+-------------+ + | 13 January 1986 | | Desdemona | Uranus X | | + + +-----------+-------------+ + | | | Rosalind | Uranus XIII | | + + +-----------+-------------+ + | | | Belinda | Uranus XIV | | +------------------+-----------------+-----------+-------------+--------------------+ | 20 January 1986 | 27 January 1986 | Cordelia | Uranus VI | Terrile, Voyager 2 | + + +-----------+-------------+ + | | | Ophelia | Uranus VII | | +------------------+ +-----------+-------------+--------------------+ | 23 January 1986 | | Bianca | Uranus VIII | Smith, Voyager 2 | +==================+=================+===========+=============+====================+ | Imaged | Published | Name | Designation | Discoverers | +==================+=================+===========+=============+====================+ The challenge is to parse the HTML data, unmerge the cells and produce a table like this (using the header data for the field names): Imaged Published Name Designation Discoverers 1980s 1980s 1980s 1980s 1980s 30 December 1985 9 January 1986 Puck Uranus XV Synnott, Voyager 2 3 January 1986 16 January 1986 Juliet Uranus XI Synnott, Voyager 2 3 January 1986 16 January 1986 Portia Uranus XII Synnott, Voyager 2 9 January 1986 16 January 1986 Cressida Uranus IX Synnott, Voyager 2 13 January 1986 16 January 1986 Desdemona Uranus X Synnott, Voyager 2 13 January 1986 16 January 1986 Rosalind Uranus XIII Synnott, Voyager 2 13 January 1986 16 January 1986 Belinda Uranus XIV Synnott, Voyager 2 20 January 1986 27 January 1986 Cordelia Uranus VI Terrile, Voyager 2 20 January 1986 27 January 1986 Ophelia Uranus VII Terrile, Voyager 2 23 January 1986 27 January 1986 Bianca Uranus VIII Smith, Voyager 2   This article contains spoilers! In fact, the whole article is a spoiler as it contains solutions to the puzzle. If you are trying to solve the puzzle yourself and just want some pointers to get you started, stop reading when you have enough, and return if you get stuck again, or just want to compare your solution to mine! HTML Table data As you probably already know, HTML table data is structured hierarchically: <table> <tbody> <tr> <td> Cell data </td> </tr> </tbody> </table> The tr (table row) elements can be repeated multiple times, and the td (table data) elements can be repeated multiple times and/or replaced by th (table header) elements. The td (and th) elements can have optional spanning attributes to declare how many columns or rows the data is spread across (similar to merging cells in a spreadsheet). Given that the target format is a table with rows and columns, we need to extract the rows from the HTML data, then extract the cell data for each column from the row data. | makeresults | fields - _time | eval _raw="<table><tbody> <tr><td colspan=\"5\">1980s</td></tr> <tr><th>Imaged</th><th>Published</th><th>Name</th><th>Designation</th><th>Discoverers</th></tr> <tr><td>30 December 1985</td><td>9 January 1986</td><td>Puck</td><td>Uranus XV</td><td rowspan=\"7\">Synnott, Voyager 2</td></tr> <tr><td rowspan=\"2\">3 January 1986</td><td rowspan=\"6\">16 January 1986</td><td>Juliet</td><td>Uranus XI</td></tr> <tr><td>Portia</td><td>Uranus XII</td></tr> <tr><td>9 January 1986</td><td>Cressida</td><td>Uranus IX</td></tr> <tr><td rowspan=\"3\">13 January 1986</td><td>Desdemona</td><td>Uranus X</td></tr> <tr><td>Rosalind</td><td>Uranus XIII</td></tr> <tr><td>Belinda</td><td>Uranus XIV</td></tr> <tr><td rowspan=\"2\">20 January 1986</td><td rowspan=\"3\">27 January 1986</td><td>Cordelia</td><td>Uranus VI</td><td rowspan=\"2\">Terrile, Voyager 2</td></tr> <tr><td>Ophelia</td><td>Uranus VII</td></tr> <tr><td>23 January 1986</td><td>Bianca</td><td>Uranus VIII</td><td>Smith, Voyager 2</td></tr> <tr><th>Imaged</th><th>Published</th><th>Name</th><th>Designation</th><th>Discoverers</th></tr> </tbody></table>" ``` Extract each row and expand into separate events ``` | rex max_match=0 "(?ms)(?<row>\<tr.*?\<\/tr\>)" | fields - _raw | mvexpand row Next, give each row a unique identifier ``` Give each row a unique identifier ``` | streamstats count as rownum Now, extract the cell data from each row event ``` Extract the cell data from the row and expand into separate events ``` | rex field=row max_match=0 "(?ms)(?<data>\<(th|td).*?\<\/\2\>)" | fields - row | mvexpand data There could be spanning attributes, so extract those ``` Extract the spanning information ``` | rex field=data "colspan=\"(?<colspan>\d+)\"" | rex field=data "rowspan=\"(?<rowspan>\d+)\"" Unmerging columns In order to unmerge a column, we simply have to replicate the column data the requisite number of times. ``` Replicate cells which span multiple columns ``` | eval expand=mvrange(0, colspan) | mvexpand expand Now, give each column within the row a unique identifier ``` Give each column in the row a unique identifier ``` | streamstats count as colnum by rownum Note that this identifies the column within the HTML table row, and it may not be the final column number in the target Splunk table because cells from above may have been spanned and actually appear before this cell. Unmerging rows This is where things start to get tricky. When cells span multiple rows in HTML tables, there is no corresponding td element in the HTML tr structure in the spanned rows. This means that the unique identifier which is based on the count of td elements in the original row may not accurately reflect the desired column position. When rows are unmerged, cells are added to subsequent rows, but the columns in the rows have already been unmerged and tagged with unique identifiers! How do we know which order the td elements should appear in the row when the rows are unmerged? The first thing to do is to replicate the cell data the requisite number of times ``` Replicate cells which span multiple rows ``` | eval expand=mvrange(0, rowspan) | mvexpand expand Calculate a target row number based on original row number and whether it is part of a row span expansion ``` Calculate target row number ``` | eval new_rownum=rownum+expand Collating the row data To collate the row data, we need to know how many column fields are needed ``` Find the maximum number of columns ``` | eventstats max(colnum) as max_columns Create a multi-value field with indexes for each of the column fields ``` Create a multi--value field with indexes for each column ``` | eval max_columns=mvrange(0,max_columns) Duplicate the multi-value field and expand it to produce an event for each column in each row ``` Duplicate and expand column indexes ``` | eval columns=max_columns | mvexpand columns Since we need the fields to be named from the th elements, extract these names and create a list. If there are multiple rows with th elements, only names from the first row will be used. ``` Extract header names from the data and list them for each cell ``` | rex field=data "\<th>(?<th>[^<]+)" | eventstats list(th) as column by columns Now that we have extracted the field names from the th elements, these events are no longer required ``` Remove header rows as they are now redundant ``` | where isnull(th) Find the corresponding column name using the unique identifier for the column as an index into the list of possible column field names ``` Find the corresponding column name based on the unique identifier for the column ``` | eval colname=mvindex(column,columns) Create a new field using the unique identifier for the column (to maintain the order of the fields) and the discovered column field name ``` Create a new field based on the unique identifier and the column name holding the column index ``` | eval f_{columns}_{colname}=columns Note that this works because there are no more than 10 fields (indexed 0 - 9). Any more than this, and the unique identifier would need to be zero-padded to maintain the order lexicographically. Compress the cell information, gathering the cell data, the column field identifiers in the column field name fields and the multi-value field with all the column identifiers, by row and column ``` Compress the cell information ``` | stats values(data) as data values(f_*) as f_* values(max_columns) as max_columns by rownum colnum new_rownum Sort the events by original row number and column number to maintain correct precedence, i.e. the order in which the original rows (tr) were defined in the input ``` Sort by original row identifier and column identifier to ensure correct precedence when unmerging cells ``` | sort 0 rownum colnum Compress the row information using lists for column numbers, and data, and values for all the column identifiers and column field name fields by target row number ``` Compress the row information ``` | stats list(colnum) as colnum list(data) as data values(max_columns) as max_columns values(f_*) as f_* by new_rownum Notice that the rows are sorted lexicographically, so re-sort to fix that ``` Notice that the rows are sorted lexicographically so let us fix that ``` | sort 0 new_rownum Building the table We now have sufficient information about each row to be able to build the table. For each column field, we need to find the data associated with the left-most column, including those which have been spanned down from prior rows, where the column position is not too far to the right. Using nested loops (as previously used in another puzzle solution), we can search through the list of column numbers until we find one which does not exceed the column number we are looking for. When a suitable column is found, update the field (removing HTML tags in the process); remove the found data from the list of remaining (unclaimed) data; and, remove the corresponding original column number from the list of remaining (unclaimed) column numbers. ``` For each of the final fields ``` | foreach f_* [ ``` Create an ordered list of data where data for a column from a prior row takes precedence ``` ``` Set a flag to start the process of finding the correct data for the field ``` | eval found=0 ``` For each column index ``` | foreach mode=multivalue max_columns [ ``` Since multivalue mode only allows one command, there is a lot going on here: If still looking for field data: If current field index + 1 >= original column number: Update flag to say we have found the data index we are looking for If found the data index: Update field with the data we found, removing HTML tags at the same time Remove the found data from the remaining (unclaimed) data list Remove the found original column number from the remaining (unclaimed) column number list Update flag to say we have finished processing for this field ``` | eval found=if(found=0 AND <<FIELD>>+1>=tonumber(mvindex(colnum,<<ITEM>>)),1,found), <<FIELD>>=if(found=1,replace(mvindex(data,<<ITEM>>),"(\<td[^>]*>)(.*)(\<\/td>)","\2"),<<FIELD>>), data=if(found=1,if(<<ITEM>>=0,mvindex(data,1,-1),mvappend(mvindex(data,0,<<ITEM>>-1),mvindex(data,<<ITEM>>+1,-1))),data), colnum=if(found=1,if(<<ITEM>>=0,mvindex(colnum,1,-1),mvappend(mvindex(colnum,0,<<ITEM>>-1),mvindex(colnum,<<ITEM>>+1,-1))),colnum), found=if(found=1,2,found) ] ] Now, all the data is in the correct column field so we can discard all the other fields ``` Since all the data has been moved, reduce the events to just the fields we are interested in ``` | table f_* Tidying up with double transpose The fields we are left with contain the column names from the td elements but they also contain the column numbers so we want to rename them. Since we know how many fields we have, we could hard-code each rename | rename f_0_* as *, f_1_* as *, … However, this should be done without hard-coding. We could try copying the fields to fields with just the header name part (which is in the second match segment) | foreach f_*_* [| eval _name="<<MATCHSEG2>>" | eval {_name}=<<FIELD>>] | fields - f_* Unfortunately, this does not quite work because the fields in the table are re-ordered lexicographically. Instead, we can transpose the events twice, modifying the column name in between ``` Instead, we can use transpose twice, modifying the column field name in between ``` | transpose 0 column_name=header | eval header=mvindex(split(header,"_"),2) | transpose 0 header_field=header | fields - column This gives the renamed fields whilst maintaining the order they were listed in the th elements. Note that this double transpose method works because of the limited number of rows and columns. Summary In summary, the key to solving this puzzle is to recognise how column spanning and row spanning are encoded in HTML tables, and how precedence can be used to determine the order of the cells in each row. Have questions or thoughts? Comment on this article or in Slack #puzzles channel. Whichever you prefer. ... View more

This challenge was first posted on Slack #puzzles channel For a previous puzzle, I needed a set of fixed-length pipe-delimited events, so I took a public domain data set, which happened to be in XML format and converted it to the required format. The data set I chose came from Montgomery County, Maryland, specifically, the Troubled Properties Analysis. You can retrieve an export from here. There are currently just under 700 rows in the data set. This puzzle has been split into multiple parts. This final challenge is to bring the techniques used in the earlier parts to create a single SPL search to convert the XML events into a fixed-length, pipe-delimited format, whilst maintaining the order of the fields. (You should ignore (filter out) extraneous elements and attributes.) Note that numeric fields need to be right-justified, and non-numerics are left-justified. Also, the width of the fields is just wide enough to hold the widest value for that field in the complete data set. For example: <row _id="row-57xu.68ky~aurc" ><hlrslicensenumber>17550</hlrslicensenumber><communityname>Quebec Terrace, 1015</communityname><streetaddress>1015 QUEBEC TER</streetaddress><city>SILVER SPRING</city><zipcode>20903</zipcode><casenumber>191048</casenumber><longitude>-76.988449097</longitude><latitude>39.000106812</latitude><firstinspectiondate>2025-08-20T00:00:00</firstinspectiondate><nextinspectiondate>2026-03-20T00:00:00</nextinspectiondate><inspectionfrequency>1</inspectionfrequency><compliantind>1</compliantind><unitcount>4</unitcount><unitsinspected>5</unitsinspected><averageviolationsperunit>4.4</averageviolationsperunit><severityindex>2.22</severityindex><noviolationsobserved>0</noviolationsobserved><infestedunitspercentage>0.2</infestedunitspercentage><units_with_mold>0</units_with_mold><rating>Troubled</rating></row> Should become something like: | 17550|Quebec Terrace, 1015 |1015 QUEBEC TER |SILVER SPRING|20903|191048|-76.988449097|39.000106812|2025-08-20T00:00:00|2026-03-20T00:00:00|1|1| 4|5|4.4|2.22|0|0.2|0|Troubled | Your solution should work with a copy of the full dataset. This article contains spoilers! In fact, most of this article is a spoiler as it contains partial solutions to the puzzle. If you are trying to solve the puzzle yourself and just want some pointers to get you started, stop reading when you have enough, and return if you get stuck again, or just want to compare your solution to mine! Pulling it together This puzzle has been split into multiple parts. This final part is about creating a single search which converts the XML format data from the Montgomery County Troubled Properties Analysis data set into fixed-length (and pipe-delimited) events. Previous parts have looked at finding processes to determine a template and preparing the data so that it can be used to convert the XML data into the required fixed-length format. This final challenge is to bring the techniques used in the earlier parts to create a single SPL search to convert the XML events into a fixed-length, pipe-delimited format, whilst maintaining the order of the fields. (You should ignore (filter out) extraneous elements and attributes.) The Montgomery County Troubled Properties Analysis data set is occasionally updated, so the data set you retrieve may be slightly different to the one I used. In fact, I have two versions of the data set with slightly different but similar formats. Field templates Starting with the process found in part one, by changing the data used to be that from the Montgomery County Troubled Properties Analysis data set, we may find that we do not get the answer we were expecting. Working our way down the search, we soon find that the chart command needs to be modified to not use OTHER and to increase the limit of fields used. Making the following change seems to solve the problem, although the number of repetitions required for this data set may be lower than that required for the planets data set: ``` Chart the fields used in the fieldname sequences ``` | eval minimum=0 | chart max(minimum) by fieldnames fields useother=f limit=0 Switching to the process found in part two, by changing the data used to be that from the Montgomery County Troubled Properties Analysis data set, we may find that we do get the answer we were expecting, even without changing the chart command. In fact, depending on the data set you use, you may find that none of the field sequence alignment process is necessary, you may simply need to find the longest sequence and check that it contains all of the fields possible from the data set. This depends on the actual data set you are using so needs to be checked not assumed. Reprocessing events Both the field template discovery processes replace the data set with the template, so, how can the template be merged with the fixed-length data? For example, your fixed-length data search might look something close to this: index="montgomery" sourcetype="troubled_properties" | spath | fields - row.*{@*} row.:-* | fields row.* | foreach row.* [| eval len_<<FIELD>>=len(<<FIELD>>) | eval padding_<<FIELD>>=if(isnum(<<FIELD>>),"","-")] | eventstats max(len_*) as len_* min(padding_*) as padding_* | foreach len_* [| eval <<FIELD>>="%".padding_<<MATCHSEG1>>.<<FIELD>>."s" | eval <<MATCHSEG1>>=printf(<<FIELD>>,<<MATCHSEG1>>)] Having got the data into the required format, we now need to merge it with the template, without losing the data. Probably, the simplest way to do this is to use the appendpipe command. | appendpipe [ ``` Find all relevant field names ``` | rex max_match=0 "\<(?<_fields>\w+)\>" ``` Create a tilde-delimited template based on all the events ``` ``` I will leave you to determine what works for your data set ``` ``` Create a pipe-delimited template field with all fieldnames in correct order ``` | eval row="|".mvjoin(split(_sequence,"~"),"|")."|" ] You should now have an additional event with the template. This template should be copied to all the events in the actual data: ``` Copy template to all data rows ``` | eventstats max(row) as row Remove the appended event:: ``` Remove appended row as no longer required ``` | where isnull(_sequence) Now, populate the template with the field values for each event in the data set: ``` For each data field, replace fieldname in template with corresponding formatted data value ``` | foreach row.* [| eval row=if(match(row,"\b<<MATCHSEG1>>\b"),replace(row,"\b<<MATCHSEG1>>\b",'<<FIELD>>'),row)] Field row now has the converted data in fixed-length and justified format: | table row Summary In summary, there were a number of techniques demonstrated in this solution, reverse dereferencing, nested loops, dynamic field formatting, and reprocessing events. For me, the key to solving this puzzle, as with a lot of puzzles, was to break it down into manageable steps, before bringing the parts together for a complete solution. Have questions or thoughts? Comment on this article or in Slack #puzzles channel. Whichever you prefer. ... View more