Splunk Search

How to rename table field names dynamically in a search?

jackson1990
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

jacobwilkins
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.

jackson1990
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

jackson1990
Path Finder

Hi @jacobwilkins ,
Still the order is not maintained.Any other solution to maintain the order for the above query.
The reason behind is,after assigning the coursescore to course_{coursename} in
eval course_{coursename}=coursescore,Its displaying in ascending order,so the order gets changes.
Help me out,if there is any possibility

0 Karma

fdi01
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

chimell
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

jackson1990
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

chimell
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

jackson1990
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

Arun_N_007
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

jackson1990
Path Finder

Hi @Arun_N_007
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
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 ...