How to align events returned by two separate searches in a table

[beetlegeuse]

I have a search that references CSV sources which are ingested from a UF; let's call these sources foo.csv and bar.csv. The general idea is to create a table which reflects fields from both CSV sources for a given event.

I'm currently using this search:

index=test_metrics sourcetype=metrics_tsr_csv ts_message!="message" source="*Test-Traits\\bar-*.csv" | append [search index=test_metrics sourcetype=metrics_csv source="*Test-Traits\\foo.csv" reason!="reason" testStepParameters="*Test-Traits*"] | rename testCaseName as URL | table URL, status, ts_message

NOTE: The naming of bar.csv is unique to each event (row) collected from foo.csv; for example, the first row of data in foo.csv is related to a file called bar-1.csv, the second row of data in foo.csv relates to bar-2.csv, etc. This is why I'm using the wildcard in the "source" of the second search. Also, there are no common fields shared between the two CSV sources.

Here's what the ensuing table looks like from the search:

 URL                     status   ts_message    
 https://a.com         FAIL     
 https://b.com         PASS     
 https://c.com         PASS     
 https://d.com         PASS     
 https://e.com         FAIL     
                                    Step 1 [REST Request] FAIL: took 0 ms
                                     Step 1 [REST Request] PASS: took 227 ms     
                                   Step 1 [REST Request] PASS: took 172 ms   
                                   Step 1 [REST Request] PASS: took 173 ms   
                                   Step 1 [Request 1] FAIL: took 238 ms

As you can see, the results from my subsearch (first two columns) are not aligned with the results from my other search (third column).
What do I need to adjust in order to have my five events aligned correctly across all three columns?

I appreciate any insight that can be shared!

EDIT: I've added a couple of screenshots that show the events returned by the search (10 of them), and how the table looks as a result. In the table screenshot, I'm showing that the values in the red rectangle should be situated within the blue rectangle (so that all values in a given row are aligned correctly).

[somesoni2 · Accepted solution]

You generally align them by a common field. If you've a common field, say commonfield, you'd do like this

index=test_metrics sourcetype=metrics_tsr_csv ts_message!="message" source="*Test-Traits\\bar-*.csv" 
| fields testCaseName status commonField 
| append [search index=test_metrics sourcetype=metrics_csv source="*Test-Traits\\foo.csv" reason!="reason" testStepParameters="*Test-Traits*" 
| fields commonField ts_message] 
| stats values(*) as * by commonField
| rename testCaseName as URL | table URL, status, ts_message

If you don't have common field, the only alignment you can do is by row by row (first row of your bar.csv will be aligned with first row from foo.csv). To do that try like this

index=test_metrics sourcetype=metrics_tsr_csv ts_message!="message" source="*Test-Traits\\bar-*.csv" 
| fields testCaseName status 
| appendcols [search index=test_metrics sourcetype=metrics_csv source="*Test-Traits\\foo.csv" reason!="reason" testStepParameters="*Test-Traits*" 
| fields ts_message] 
| rename testCaseName as URL | table URL, status, ts_message

[beetlegeuse]

I tried applying the fields command as you specified in each of the searches (your second suggestion, as I don't have a common field to use between the sources), and I received this error message:

Error in 'appendcols' command: You can only use appendcols after a reporting command (such as stats, chart, or timechart).

Changing "appendcols" to "append" resulted in 10 blank rows being returned in the table. When I swapped the placement of the two "fields" commands in the query, I received a result in the table that was similar to what I originally posted.

[somesoni2]

Replace fields with table command. (with appendcols)

[beetlegeuse]

Thank you! That modification corrected the table issue!