Quotes in CSV-formatted events

[zenmoto]

I am attempting to add CSV-formatted events to my index through the REST API. I've got it working mostly correctly, but I'm having an issue with the quotes.

The events I'm adding are in standard CSV format, so they look like

"HEADER1","HEADER2"
"row1 value1","row1 value2"
"row2 value1","row2 value2"

The problem I'm having is that as I go to split the values with multikv I can't get rid of the quotes- I'm getting keys of HEADER1_ and values that include the surrounding quotes.

Is there a good way to do a better CSV extraction? Am I barking up the wrong tree with multikv?

[zenmoto · Accepted solution]

Perhaps I just worded this question badly, but I think I found the answer to my dilemma in a different question here. I'm trying to do something very similar in being able to import arbitrary reports from databases to use them in Splunk. Formatting my results in this tabular format is somewhat more onerous, but it should work for me.

Here's an example Java class I put together for outputting the correct format in case it helps someone else.

import java.io.IOException;
import java.io.Writer;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.ArrayList;


public class DisplayTable {
    protected List<ColumnInfo> columndata= new ArrayList<ColumnInfo>();
    protected List<List<String>> data = new ArrayList<List<String>>();

    private static int[] numericTypes = {Types.BIGINT, Types.DECIMAL, Types.DOUBLE, Types.FLOAT, 
        Types.INTEGER, Types.NUMERIC, Types.REAL, Types.SMALLINT, Types.TINYINT};


    public DisplayTable() {

    }

    public DisplayTable(ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        for (int i=1; i<=meta.getColumnCount(); i++) {
            addColumn(meta.getColumnLabel(i), isNumericSqlType(meta.getColumnType(i)));
        }
        while (rs.next()) {
            List<String> row = new ArrayList<String>();
            for (int i=1; i<=meta.getColumnCount(); i++) {
                row.add(rs.getObject(i).toString());
            }
            addRow(row);
        }
    }

    public void addColumn(String name, boolean numeric) {
        columndata.add(new ColumnInfo(name, numeric));
    }

    public void addColumn(String name) {
        this.addColumn(name, true);
    }

    public void addRow(List<String> row) {
        for (int i=0; i<columndata.size(); i++) {
            columndata.get(i).updateMetrics(row.get(i));
        }
        data.add(row);
    }

    public void print(Writer out) throws IOException {
        for (int i=0; i<columndata.size(); i++) {
            out.append(columndata.get(i).formattedHeader() + " ");
        }
        out.append("\n");
        for (List<String> row : data) {
            for (int i=0; i<columndata.size(); i++) {
                out.append(columndata.get(i).formatValue(row.get(i)) + " ");
            }
            out.append("\n");
        }
    }

    private boolean isNumericSqlType(int type) {
        for (int i=0; i<numericTypes.length; i++) {
            if (type == numericTypes[i]) return true;
        }
        return false;
    }


    private class ColumnInfo {
        int width;
        boolean numeric;
        String title;

        public ColumnInfo (String name, boolean numeric) {
            this.numeric = numeric;
            this.title = name;
            this.width = name.length();
        }

        public void updateMetrics(String value) {
            if (value.length() > width) {
                width = value.length();
            }
        }

        private String format(String value, boolean rightjust) {
            int padding = width - value.length();
            StringBuilder str = new StringBuilder(width);
            if (rightjust) {
                for (int i=0; i<=padding; i++) {
                    str.append(" ");
                }
                str.append(value);
            } else {
                str.append(value);
                for (int i=0; i<=padding; i++) {
                    str.append(" ");
                }
            }
            return str.toString();
        }

        public String formatValue(String value) {
            return format(value, numeric);
        }

        public String formattedHeader() {
            return format(title, true);
        }

    }
}

[gkanapathy]

You should not be using multikv. And, if you're using the REST API, you should just be sending each row as a single event/record. You can use standard CSV, in which case you should put the headers/field names into props.conf/transforms.conf, or you can send it as a kv-pair formatted record.

[zenmoto]

I think that perhaps I wasn't quite specific enough- I don't actually know the headers to begin with, and I want to keep all of the rows together (much like a result from top or ps).