renaming field names from string+number to only the number

[changux]

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!

[somesoni2 · Accepted solution]

How about just this

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

Update#2
Considering your response to my question "In the Field_N, is N the current day? e.g. if today is May 25, will the field name will be Field_1 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 *

[changux]

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 ]

[somesoni2]

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]

[changux]

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!

[somesoni2]

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 ]

[changux]

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?

[changux]

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

[somesoni2]

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

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

[changux]

My fault. You rocks!

[changux]

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

[changux]

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?

[changux]

Yes, exactly!!

[somesoni2]

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

[sundareshr]

Try this

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

[changux]

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

[kristian_kolb]

probably a missing space.

table Name Address ...

/k