Splunk Search

renaming field names from string+number to only the number

Builder

Hi all.
I have a dataset with fields like:

Field_1
Field_2
Field_3
Field_4
Field_5
Name
Address

I need to remove the string part of the field name "Field_" and after, show in a table the value of the max field value, in this case, something like:

... | table Name, Address, 5

My problem, my dataset changes every day and i need only the value of the current day (the number part in the field_{number} field name).

Any help?

Thanks!

0 Karma
1 Solution

SplunkTrust
SplunkTrust

How about just this

your base search | rename Field_* as * | table Name, Address, 5

Update#2
Considering your response to my question "In the FieldN, is N the current day? e.g. if today is May 25, will the field name will be Field1 to Field_25?", try this. The subsearch in the end will return the current date (like today it will return 26)

your base search | rename Field_* as * | table Name Address [| gentimes start=-1 | eval search=strftime(now(),"%d") | table search ]

Update#2.1
There could be formatting issue. so give these a try

your base search | rename Field_* as * | table Name Address [| gentimes start=-1 | eval today=tonumber(strftime(now(),"%d")) | return $today ]

OR

your base search | rename Field_* as * | table Name Address [| gentimes start=-1 | eval search=tonumber(strftime(now(),"%d")) | table search ]

Update#1

Give this a try

 your base search | eval temp=Name."#".Address | table temp , Field_* | untable temp Field Value
| eval Field=mvindex(split(Field,"_"),1) | eventstats max(Field) as max by temp | where Field=max 
| rex field=temp "(?<Name>.+)#(?<Address>.+)" | eval {Field}=Value 
| fields - temp Value Field | table Name Address *

View solution in original post

SplunkTrust
SplunkTrust

How about just this

your base search | rename Field_* as * | table Name, Address, 5

Update#2
Considering your response to my question "In the FieldN, is N the current day? e.g. if today is May 25, will the field name will be Field1 to Field_25?", try this. The subsearch in the end will return the current date (like today it will return 26)

your base search | rename Field_* as * | table Name Address [| gentimes start=-1 | eval search=strftime(now(),"%d") | table search ]

Update#2.1
There could be formatting issue. so give these a try

your base search | rename Field_* as * | table Name Address [| gentimes start=-1 | eval today=tonumber(strftime(now(),"%d")) | return $today ]

OR

your base search | rename Field_* as * | table Name Address [| gentimes start=-1 | eval search=tonumber(strftime(now(),"%d")) | table search ]

Update#1

Give this a try

 your base search | eval temp=Name."#".Address | table temp , Field_* | untable temp Field Value
| eval Field=mvindex(split(Field,"_"),1) | eventstats max(Field) as max by temp | where Field=max 
| rex field=temp "(?<Name>.+)#(?<Address>.+)" | eval {Field}=Value 
| fields - temp Value Field | table Name Address *

View solution in original post

Builder

Hi. Some way to show the data from yesterday? I mean, something like:

| rename ACTIVO_DIA_* as * | table NOMBRE [| gentimes start=-2 | eval search=strftime(now(),"%d") | table search ]
0 Karma

SplunkTrust
SplunkTrust

Try like this. Change the value of noofdays in the subsearch to adjust how many day's data to be show. 1 for today, 2 for today and yesterday and so on.

 | rename ACTIVO_DIA_* as * | table NOMBRE [| gentimes start=-1 | eval noofdays=2 | eval t=mvrange(0,noofdays) | mvexpand t | eval search=strftime(relative_time(now(),"-".t."d@d"),"%d") | stats list(search) as search delim="," | nomv search]
0 Karma

Builder

Thanks! Works great. I can see the number of columns that i set in noofdays var, but, if i want to show only the desired column (noofdays=2 to show yesterday data), how i can adjust your query?

Thanks!

0 Karma

SplunkTrust
SplunkTrust

Use this. The relative_time will get date of yesterday.

 | rename ACTIVO_DIA_* as * | table NOMBRE [| gentimes start=-2 | eval search=strftime(relative_time(now(),"-1d@d"),"%d") | table search ]
0 Karma

Builder

Great!! How i can discover the max number in the field after renaming? I mean, how to know that 5 is the name of the max field?

0 Karma

Builder

Thanks again. The column 26 is not showing anything and i already have data.

An image with the last query:

https://dl.dropboxusercontent.com/u/813317/splunk-no.png

An image showing that 26 has data:

https://dl.dropboxusercontent.com/u/813317/splunk-si.png

0 Karma

SplunkTrust
SplunkTrust

Your field names have prefix as "ACTIVODIA" and my ansers (based on your initial question) assume it to be "Field". In my answer, replace all instance of Field with "ACTIVODIA".

 your base search | rename ACTIVO_DIA_* as * | table Name Address [| gentimes start=-1 | eval search=strftime(now(),"%d") | table search ]

Builder

My fault. You rocks!

0 Karma

Builder

With update 2.1 of your answer, field 26 continues empty.

0 Karma

Builder

I ran this without success:

| eval temp=NOMBRE."#".SERVICIO | table temp , ACTIVO_DIA_* | untable temp Field Value
 | eval Field=mvindex(split(Field,"_"),1) | eventstats max(Field) as max by temp | where Field=max 
 | rex field=temp "(?<NOMBRE>.+)#(?<SERVICIO>.+)" | eval {Field}=Value 
 | fields - temp Value Field | table NOMBRE SERVICIO *

What do you think that is wrong?

0 Karma

Builder

Yes, exactly!!

0 Karma

SplunkTrust
SplunkTrust

In the FieldN, is N the current day? e.g. if today is May 25, will the field name will be Field1 to Field_25?

0 Karma

Legend

Try this

.... | tableName Address Field_* |  foreach Field_* [rename <<FIELD>> AS <<MATCHSTR>>]

Builder

Thanks! Sorry but i can't understand how use this instruction. What means tableName?

0 Karma

Ultra Champion

probably a missing space.

table Name Address ...

/k

0 Karma