Splunk Search

Quotes in CSV-formatted events

zenmoto
Path Finder

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?

0 Karma
1 Solution

zenmoto
Path Finder

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);
        }

    }
}

View solution in original post

0 Karma

zenmoto
Path Finder

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);
        }

    }
}
0 Karma

gkanapathy
Splunk Employee
Splunk Employee

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.

0 Karma

zenmoto
Path Finder

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).

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...