topic Re: multi field in different rows in Splunk Search

multi field in different rows

gcusello — Tue, 29 Sep 2020 14:13:05 GMT

Hi at all,

I'm ingesting many csv where there are a variable number of columns.

some of this columns have name "service_0_name", "service_1_name", ..., "service_45_name".
Not all these fields are full, but if there's a value in e.g. "service_3_name", there are values also in "service_0_name", "service_1_name" and "service_2_name".
I need a field with all service_x_name values to manage with mvexpand.
In other word something like coalesce(field1, field2, ...) that takes all values and not only the first.

Anyone has an idea how to solve this problem?

Thank you in advance.

Giuseppe

Re: multi field in different rows

woodcock — Mon, 29 May 2017 14:05:45 GMT

Assuming there is one key field and all other fields/columns are service_* (if there isn't, just add | eval host="LabelHere" before the rest of the solution), then this will work:

... | untable host services count | stats values(services)  AS service_values list(services) AS service_list BY host

Note, this will only work for 1000 values because that is the limit for values and list.

Re: multi field in different rows

gcusello — Tue, 29 Sep 2020 14:13:12 GMT

Hi Woodcoock,
sorry but I don't understand the untable command:

I have in each row:
_time uid other_fields service_0_name service_1_name service_2_name ...
and values of service_x_name are strings (as tcp, telnet, ftp, ....)

I need to have all these strings fo each row.

Bye.
Giuseppe

Re: multi field in different rows

woodcock — Mon, 29 May 2017 19:44:07 GMT

Let's assume that you have exactly these fields:

_time uid other fields

Along with your huge list of fields like this:

service_0_name service_1_name service_2_name ...

In order to use untable, you to have exactly 1 "other" field so you need to combine and reduce your "non-service" fields into a single "other" field, do the "service" stuff, then extract the "other" fields back out like this:

| eval time_uid_other_fields = _time . "::" . uid . "::" . other . "::" . fields
| table time_uid_other_fields service_*
| untable time_uid_other_fields services count
| stats values(services)  AS service_values list(services) AS service_list BY time_uid_other_fields
| rex field=time_uid_other_fields "^(?<_time>.*?)::(?<uid>.*?)::(?<other>.*?)::(?<fields>.*?)$"
| fields - time_uid_other_fields

Note, this will only work for 1000 values because that is the limit for values and list.

Re: multi field in different rows

woodcock — Mon, 29 May 2017 19:44:26 GMT

See my other answer.