Splunk Search

multi field in different rows

gcusello
SplunkTrust
SplunkTrust

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

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

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.

View solution in original post

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

woodcock
Esteemed Legend

See my other answer.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...