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
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
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.
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
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
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
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
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?
jackson1990
if you have not only 5 courses , just add the following to the search code above
.........\D(?<course6>\d+)\D(?<course7>\d+)\D(?<course8>\d+)\D(?<course9>\d+)\D(?<course10>\d+)
and retest
let me know if it work
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?
Could you please give some sample data and let me know.
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...