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).
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
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
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.
Replace fields
with table
command. (with appendcols)
Thank you! That modification corrected the table issue!