topic Re: Create new field based off combination of 2 in Splunk Search

Create new field based off combination of 2

jachockey012 — Wed, 28 Oct 2020 15:23:51 GMT

so I have some data that comes in via a TCP input. I want to quickly run a specific search but it requires me to have the data formated a bit different. I think the tables below will help describe what I am looking to do because I am unable to describe it very well.

Event 1

Name Value

metric_name	Value
A	1
B	0
C	0
D	0
I	274

Event 2

Name Value

mertic_name	Value
A	2
B	2
C	2
D	2
I	344

What I want to have is a new field for each of the Names and then every new Value is "appended" to that event.

A	B	C	D	I
1	0	0	0	274
2	2	2	2	344

Re: Create new field based off combination of 2

ITWhisperer — Wed, 28 Oct 2020 09:05:28 GMT

Can you provide an example of your raw events and any existing field extractions? The layout of the raw event will determine the solution e.g. a solution for a JSON event will look different to a solution to a key=value event.

Re: Create new field based off combination of 2

jachockey012 — Wed, 28 Oct 2020 15:08:37 GMT

So the data is sent in single line event looks like this.

2020-09-25 06:38:56.080 +0000 Tag="CTL.CTL 5580.I" Value="279" Quality="good" ControlLogix 5580

I am using the Kepware TA from the https://splunkbase.splunk.com/app/3963/

----- Transforms.conf -----

[metric_value]
FORMAT = _value::$1
REGEX = Value="(\S+?)"
WRITE_META = 1

[kepware_path]
FORMAT = kepware_path::"$1"
REGEX = Tag="(.+?)"
WRITE_META = 1

[quality]
FORMAT = quality::"$1"
REGEX = Quality="(.+?)"
WRITE_META = 1

[metric_name]
FORMAT = metric_name::"$1"
REGEX = [A-z0-9_()]+\.([A-z0-9_()\s]*?)\"\sValue
WRITE_META = 1

[asset]
FORMAT = asset::"$1"
REGEX = Tag\=\"([A-z0-9.\s*()_]*)\.
WRITE_META = 1

[metadata]
FORMAT = metadata::“$1”
REGEX = Quality=".*"\s*(.*)
WRITE_META = 1

Re: Create new field based off combination of 2

ITWhisperer — Wed, 28 Oct 2020 15:22:11 GMT

Try

| extract | fields - _time _raw | table *

Re: Create new field based off combination of 2

jachockey012 — Wed, 28 Oct 2020 15:33:17 GMT

That didnt end up working. As I said I am having a hard time explaining I think this would be an example in python sudocode...

for i in metric_name if (i = A) A.extend (A) if (i = B) if (i = C) ...

I want to take each "metric_name" and make it a column with the "Value" and then later "_time" from that event as a row/element.

Re: Create new field based off combination of 2

ITWhisperer — Wed, 28 Oct 2020 16:26:54 GMT

When you do the extract, from your sample data, you will get a field called Tag with value "CTL.CTL 5580.I", a field called Value with value "279", and a field called Quality with a value "good". Assuming your other events have similar fields, all the Tag values will be in Tag fields, etc. The table command just displays them as you showed in your original post. The only thing I can think of that is different from what I think you are asking for is that they are not in multi-value fields. Assuming a table is not useful to you for some reason, perhaps you need to expand on what it is you are trying to achieve beyond getting all the values listed under their key names.

Re: Create new field based off combination of 2

jachockey012 — Thu, 29 Oct 2020 00:05:06 GMT

Correct I have Tag with value "CTL.CTL 5580.I" and a field Value with value "279"

I also have another event with the Tag "CTL.CTL 5580.A" and a field Value with value "1"

What I want is basically a field

CTL.CTL 5580.I	CTL.CTL 5580.A
279	1

I am trying to do some trendline on the value over time that CTL.CTL 5580.I as well a use it s a field to fit a model for categorical predictions on CTL.CTL 5580.A

Re: Create new field based off combination of 2

jachockey012 — Thu, 29 Oct 2020 00:33:29 GMT

| bucket span=1s _time | chart values(Value) over _time by metric_name

This was able to get me what I needed! Not the most optimal but defiantly good enough for me. This works because I would have all my events within the same second