topic Re: How to split a row by 2 field values in Splunk Search

How to split a row by 2 field values

Trishant — Sat, 18 Nov 2017 16:20:34 GMT

I have a sample data which I am trying to split over 2 fields.

For Example:

In above image we have a test case ID which has some values in Different time spans, It contains combined values form 2 different vendors let say A and B.

What I need is to split this row into 2 parts for 2 vendors one having data for A and another having data for B.???

And please tell me how to sort this span buckets. 0-3, 12-15, 15-18, 18-21, 3-6......???

Re: How to split a row by 2 field values

niketn — Sun, 19 Nov 2017 02:21:32 GMT

@Trishant, you would need to add more details. What is the field to identify VendorA and VendorB. What is your current SPL? Also can you sample some event data (after mocking/anonymizing any sensitive information)?

Re: How to split a row by 2 field values

kamlesh_vaghela — Sun, 19 Nov 2017 02:30:58 GMT

HI @Trishant,

How you want to split this event between 2 vendors?? I mean if we say for span column "0-3" then how we can split value "96.00"?

And you asked about sorting of 0-3, 12-15, 15-18, 18-21, 3-6, do you want to sort sequence of these span columns??

Thanks

Re: How to split a row by 2 field values

Trishant — Tue, 29 Sep 2020 16:50:45 GMT

Hi,

I have used below search to get this view

sort +iteration | eval testId = testId + ": " + testcase |
rename testId as Test_CaseID, build as Build, duration as Time_Taken | where (Build= "Vendor A" OR Build= "Vendor B") |
chart count(Test_CaseID) as Total_Runs over Test_CaseID by Time_Taken bins=100|
untable Test_CaseID, Time_Taken, count |
eventstats sum(count) as Total by Test_CaseID|
eval perc=round(count*100/Total,2) | fields - count(Total) |
xyseries Test_CaseID, Time_Taken, perc|

so this 96.00 is coming from Vendor A + Vendor B
what I want is 2 rows with same Test_CaseID(1 for A and another for B)

Yes, I want to sort sequence of these span columns like 0-3, 3-6, 6-9, 9-12.....

Re: How to split a row by 2 field values

Trishant — Tue, 29 Sep 2020 16:50:47 GMT

Hi,

I have used below search to get this view

so this 96.00 is coming from Vendor A + Vendor B
what I want is 2 rows with same Test_CaseID(1 for A and another for B)

Yes, I want to sort sequence of these span columns like 0-3, 3-6, 6-9, 9-12.....

Hope this might help you in some extent...

Re: How to split a row by 2 field values

Trishant — Tue, 29 Sep 2020 16:51:10 GMT

Hi,

I have used below search to get this view

so this 96.00 is coming from Vendor A + Vendor B
what I want is 2 rows with same Test_CaseID(1 for A and another for B)

Yes, I want to sort sequence of these span columns like 0-3, 3-6, 6-9, 9-12.....

Re: How to split a row by 2 field values

kamlesh_vaghela — Mon, 20 Nov 2017 16:50:00 GMT

HI @Trishant,
Got it.

Can you share some sample event & expected output ?? So I can try to design search for you.

Thanks

Re: How to split a row by 2 field values

somesoni2 — Mon, 20 Nov 2017 20:18:53 GMT

Try like this

...base search

| where build="Vendor A" OR build="Vendor B"
| sort +iteration 
| eval Test_CaseID = testId + ": " + testcase + "#" + build
| chart count(Test_CaseID) as Total_Runs over Test_CaseID by duration bins=100
| untable Test_CaseID, Time_Taken, count 
| eventstats sum(count) as Total by Test_CaseID
| eval perc=round(count*100/Total,2) 
| fields - count(Total) 
| xyseries Test_CaseID, Time_Taken, perc
| rex field=Test_CaseID "(?<Test_CaseID>[^#]+)#(?<Build>.+)"

There is no easy way to sort those dynamic columns for bins of Time Taken as they're treated as string when converted to columns.