How to read an array of fields from a single event and make into different records ?

[JP]

Hi,

In Splunk, I have Test Automation results logs which has details like Test case name, Test Status, Error, Duration, Date etc in multiple events. Each event has nearly 25 - 20 test cases details in an array. I need to fetch each test case as a single record. 

When I use spath, it is fetching the fields, but all the test case of a single event is written as a single record.

index=jenkins OR source=\test OR job_name:"Dev/TestAutomation/Regression"| spath

I am very new to Splunk. Is there any way by which I can write each testcase details as a single record. With these details, my requirement is to create an Regression Test Automation dashboard.

Thanks

[ITWhisperer]

Try something like this (you may have to adjust because it is difficult to read images so I may have got the structure wrong):

| spath path=testsuite.testcase{} output=testcase
| mvexpand testcase

[JP]

@ITWhisperer  Even after running this query, when I view the search result in the Tabular view, I still see multiple testcasenames, status etc clubbed together in one event. Below is a screenshot of a single event which has the testcase and status together. For my requirement to create a dashboard, I would like to have each of these as a single event.

[ITWhisperer]

Did you do this instead of your spath? Try this to also extract the elements from testcase:

| spath path=testsuite.testcase{} output=testcase
| mvexpand testcase
| spath input=testcase

[JP]

@ITWhisperer Yes I did the command that you gave me. Do you mean to say that I have to do a  separate spath for each field that I need ? 

[ITWhisperer]

If you want separate event from an array, you need to extract the array to a multivalue field, then expand the multivalue field to separate events, then you can extract elements of the array.

[JP]

@ITWhisperer - Thanks I understand and it worked to change the grouped events to single events. But the issue now is the events are very repetitive. I tried to use dedup command, but still there are N number of duplicates and the records are not coming in the right mapping. The data is so jumbled up. Following is the command that I used. 

index=jenkins* host=opsdev-app-jenkins.route53.com source="/test"
| search job_name=Dev/TestAutomation/Cert1_Regression
| spath path=testsuite.testcase{}.classname output=tclassname
| spath path=testsuite.testcase{}.testname output=testname
| spath path=testsuite.testcase{}.status output=status
| mvexpand tclassname
| mvexpand testname
| mvexpand status
| rename metadata.Nexis.Auto.Environment AS Environment
| table job_started_at job_duration job_name Environment tclassname testname status

I have been trying to find something that will help me to map data rightly and avoid duplicates, but I unable to find the solution. Is there anything I could do to avoid duplicates and jumbling up of data ?

[ITWhisperer · Accepted solution]

No, as I showed, you need to extract the array as a multivalue field, mvexpand that, then extract the sub-elements of those

index=jenkins* host=opsdev-app-jenkins.route53.com source="/test"
| search job_name=Dev/TestAutomation/Cert1_Regression
| spath path=testsuite.testcase{} output=testcase
| mvexpand testcase
| spath input=testcase path=classname output=tclassname
| spath input=testcase path=testname output=testname
| spath input=testcase path=status output=status
| rename metadata.Nexis.Auto.Environment AS Environment
| table job_started_at job_duration job_name Environment tclassname testname status

[JP]

@ITWhisperer I understood the concept and it works perfectly. Thank you!

[JP]

@efika The results are still remaining the same. One event has multiple values. Tried exactly the same. 

[ eval new_field=mvappend(new_field,'<<FIELD>>')]

In the <<FIELD>>, I gave the multivalue field. I hope that is right?

[efika]

@JP ,

You should keep the '<<FIELD>>', this is the variable for the foreach loop (see the docs https://docs.splunk.com/Documentation/Splunk/8.2.1/SearchReference/Foreach)

[JP]

Thanks @efika for replying.

[efika]

My pleasure. I'm glad I could do something to help someone today.

[efika]

Hi @JP ,

I hope I understood correctly what you are trying to do, if so maybe this will enable you to progress toward a solution (Working on the Win Events for demo sake) :

index=main sourcetype=XmlWinEventLog 
| fields _raw 
| spath 
| foreach Event.System.* 
    [ eval new_field=mvappend(new_field,'<<FIELD>>')] 
| table new_field 
| mvexpand new_field