Splunk Search
Highlighted

How to rename table field names dynamically in a search?

Path Finder

Sample EventList for my scenario given below:

ID=1 | Name=sankar | Age=20 | Dept=Computer science | Programming=60 | DataStructure=70 | RDBMS=80 | Compiler=75
ID=2 | Name=Akash | Age=25 | Dept=Mechanical | SolidMechanics=80 | ThermoFluidMechanics=65 | Manufacturing=90 
ID=3 | Name=Sathish | Age=18 | Dept=Computer science | Programming=70 | DataStructure=84 | RDBMS=90 | Compiler=85 
ID=4 | Name=Arun | Age=24 | Dept=Mechanical | SolidMechanics=70 | ThermoFluidMechanics=55 | Manufacturing=80 
ID=5 | Name=Kavya | Age=30 | Dept=Electrical | PhysicalOptics=80 | InformationSecurity=75 | Nanophotonics=90 | ImageProcessing=85 | NonlinearSystemTheory=85
ID=6 | Name=Kaveri | Age=24 | Dept=Computer science | Programming=70 | DataStructure=50 | RDBMS=50 | Compiler=85 
ID=7 | Name=Hema | Age=25 | Dept=Electrical | PhysicalOptics=60 | InformationSecurity=85 | Nanophotonics=90 | ImageProcessing=75 | NonlinearSystemTheory=68

ID, Name, Age and Dept fields will be present in all Events without any change in Name, whereas the Courses differs w.r.t Dept.

Resulting Table, i am Excepting as
alt text

Instead of the fields as course name for each dept, it has to be renamed as Course1, Course2 etc...

What is the splunk search I need to use to retrieve the above result?
I have tried with the search below, but didn't get the proper result:

index="college_index" | table ID,Name,Age,Dept,* | fields - source*,splunk_server_group,host,_raw date_*,index,linecount,punct,eventtype,splunk_server,_time,time*pos 

It shows the table with courseNames only.

Any help will be much appreciated.
Thanks in advance

0 Karma
Highlighted

Re: How to rename table field names dynamically in a search?

Communicator

Could you please give some sample data and let me know.

  1. In what format it is and in what format you need?
  2. Its better you provide some raw data.
  • Arun
0 Karma
Highlighted

Re: How to rename table field names dynamically in a search?

Path Finder

Hi @ArunN007
Format of raw data is plain Text.
Sample Indexed Event List given below:

ID=1 | Name=sankar | Age=20 | Dept=Computer science | Programming=60 | DataStructure=70 | RDBMS=80 | Compiler=75
ID=2 | Name=Akash | Age=25 | Dept=Mechanical | SolidMechanics=80 | ThermoFluidMechanics=65 | Manufacturing=90
ID=3 | Name=Sathish | Age=18 | Dept=Computer science | Programming=70 | DataStructure=84 | RDBMS=90 | Compiler=85
ID=4 | Name=Arun | Age=24 | Dept=Mechanical | SolidMechanics=70 | ThermoFluidMechanics=55 | Manufacturing=80
ID=5 | Name=Kavya | Age=30 | Dept=Electrical | PhysicalOptics=80 | InformationSecurity=75 | Nanophotonics=90 | ImageProcessing=85 | NonlinearSystemTheory=85
ID=6 | Name=Kaveri | Age=24 | Dept=Computer science | Programming=70 | DataStructure=50 | RDBMS=50 | Compiler=85
ID=7 | Name=Hema | Age=25 | Dept=Electrical | PhysicalOptics=60 | InformationSecurity=85 | Nanophotonics=90 | ImageProcessing=75 | NonlinearSystemTheory=68

The result has to be in the form of table attached...

0 Karma
Highlighted

Re: How to rename table field names dynamically in a search?

Motivator

Hi jackson1990
Try this search code

index="college_index" | table ID,Name,Age,Dept| rex field=_raw "Dept\=[^\=](?<course1>[^\|])\D(?<course2>\d+)\D(?<course3>\d+)\D(?<course4>\d+)\D(?<course5>\d+)"|fillnull value="NONE"| table ID Name Age Dept course1 course2 course3 course4 course5
Highlighted

Re: How to rename table field names dynamically in a search?

Path Finder

Thanks @chimell for your answer..
This will work if my eventList has a max of 5 courses.
But The Incoming Events can have many courses(not ly 5).Sometimes it can have 10 courses also for a dept.
In such case,i have change the query for 10 courses in a static way.
Is it possible to create the query dynamically,reading each row by row?

0 Karma
Highlighted

Re: How to rename table field names dynamically in a search?

Motivator

jackson1990
if you have not only 5 courses , just add the following to the search code above

.........\D(?&lt;course6&gt;\d+)\D(?&lt;course7&gt;\d+)\D(?&lt;course8&gt;\d+)\D(?&lt;course9&gt;\d+)\D(?&lt;course10&gt;\d+)

and retest
let me know if it work

0 Karma
Highlighted

Re: How to rename table field names dynamically in a search?

Path Finder

Hi @Chimell,
If i include query for remaining courses(from Course 6 to Course10),it will show the fields from Course1 to Course10.
But in case of below scenario:
index=college_index Dept =Computer Science OR Dept =Mechanical

I want courses from Course1 to Course4 to be displayed as extrafields in Table.
But as per your query for the above scenario,It will show course1 to course 10,with NONE for Course5 to Course10
Hope you understand my point.
Is it possible to create the course query dynamically during runtime ,based on the search conditions and results,instead of specifying it in a static way?

0 Karma
Highlighted

Re: How to rename table field names dynamically in a search?

Motivator

see this two link:
http://docs.splunk.com/Documentation/Splunk/6.2.0/Knowledge/Addaregularexpressionattribute

http://docs.splunk.com/Documentation/Splunk/6.2.0/Knowledge/ExtractfieldsinteractivelywithIFX

or
you can use max_match=0 attribut inside your rex command and the mvindex command to do it.
ex:

...you_base_search...| rex field=_raw  max_match=0  ...you_regex_for_one_course...       
| eval course1=mvindex(strings,0) | eval course2=mvindex(strings,1) | eval course3=mvindex(strings,2) |...| eval coursen=mvindex(strings,n-1) |table course1 course2  ... coursen
Highlighted

Re: How to rename table field names dynamically in a search?

Communicator

Here is a fully dynamic solution:

...your base search... | fields ID, Name, Age, Dept, _raw 
| rex "^ID[^|]*\|[^|]*\|[^|]*\|[^|]*\|(?<allcourses>.*)" 
| rex  field=allcourses max_match=100 "(?<coursepair>\w*=\d*)"
| fields - allcourses
| mvexpand coursepair
| rex field=coursepair "^(?<coursename>[^=]*)=(?<coursescore>\d*)"
| eval course_{coursename}=coursescore
| fields - coursename, coursescore, coursepair
| stats values(*) AS * by ID
| eval idx_event=1
| foreach course_* [ eval idx = idx_event | eval course{idx}=if(isnotnull(<<FIELD>>),<<FIELD>>,null()) | eval idx_event=if(isnotnull(<<FIELD>>),idx+1,idx) ]
| fields - course_* idx*

I didn't take time to try to optimize, or reduce steps here. There could be some opportunities for cleanup.

Highlighted

Re: How to rename table field names dynamically in a search?

Path Finder

Hi @jacobwilkins,
Thanks for your reply.
I am getting the output as cource1,course2....course n dynamically.
But i am facing the below errors
--- Marks are not assigned in proper order,as per the course.Order has to be maintained as coming in the events.
--- NULL/EMPTY values has to be replaced by NONE

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.