Compare two different rows and count values from search B

[nesa246]

index=spb_uip_qa_automation splunkAutomationTag="Client_ReleaseChecklist_Staging_Platform_Desktop_Chrome_9.15.0.1568_360"
| table guid startTime endTime

guid startTime endTime

73ad9706-53e3-4dbb-b8f4-89c7e2d773de

2020-06-14T11:48:34.13

2020-06-14T11:49:47.183

index=spb_uip_qa_automation guid IN ("73ad9706-53e3-4dbb-b8f4-89c7e2d773de", "be736d15-d463-4e28-b75c-09e44f7e2177")
| eval Status=case(testResult == 0, "Pass", testResult ==1, "Fail", testResult == 2, "Inconclusive")
| table testName Status
| where isnotnull(testName) AND isnotnull(Status)

guid testName Status

73ad9706-53e3-4dbb-b8f4-89c7e2d773de	UkLoadAndCompleteRegistrationDesktopTest	Inconclusive
73ad9706-53e3-4dbb-b8f4-89c7e2d773de	UkResponsibleGamingTest	Fail
73ad9706-53e3-4dbb-b8f4-89c7e2d773de	MaltaRegistrationDesktopTest	Inconclusive
73ad9706-53e3-4dbb-b8f4-89c7e2d773de	BelgiumRegistrationDesktopTest	Inconclusive
73ad9706-53e3-4dbb-b8f4-89c7e2d773de	MexicoResponsibleGamingDesktopTest	Fail
73ad9706-53e3-4dbb-b8f4-89c7e2d773de	MexicoLoadBankingDesktopTest	Inconclusive
73ad9706-53e3-4dbb-b8f4-89c7e2d773de	MexicoRegistrationDesktopTest	Fail
73ad9706-53e3-4dbb-b8f4-89c7e2d773de	SpainRegistrationDesktopTest	Fail
73ad9706-53e3-4dbb-b8f4-89c7e2d773de	NoindexAttributeTest	Pass
73ad9706-53e3-4dbb-b8f4-89c7e2d773de	BelgiumSportsToCasinoDesktopTest	Inconclusive
73ad9706-53e3-4dbb-b8f4-89c7e2d773de	UkLoadBankingTest	Inconclusive

I want final result to be like below: 

guid startTime endTime totalTestPassed totalTestFailed totalTestInconclusive

73ad9706-53e3-4dbb-b8f4-89c7e2d773de

2020-06-14T11:48:34.13

2020-06-14T11:49:47.183

1

4

6

As I am beginner to Splunk and I am really struggling a lot to get this table right to create charts for Pass & Fail % and Runtime graphs, please kindly help me .

[javiergn · Accepted solution]

Hi @nesa246 ,

Try using stats the following way:

| stats 
    count(eval(testResult=0)) as totalTestPassed, 
    count(eval(testResult=1)) as totalTestFailed, 
    count(eval(testResult=2)) as totalTestInconclusive 
    by 
        guid, 
        startTime, 
        endTime

Hope that helps.

Regards,

J

[nesa246]

@javiergn  Thanks for the very quick reply and it works !!

I have another query, below table gives me 2 rows I have to pick 

Where

| guid matches

| startTime is oldest

| endTime is latest

| Add values of two rows in  totalTestPassed totalTestFailed totalTestInconclusive

| table splunkAutomationTag startTime endTime totalTestPassed totalTestFailed totalTestInconclusive

should return only one row 

 index=spb_uip_qa_automation guid | join guid [search guid splunkAutomationTag="Client_ReleaseChecklist_Platform_Desktop_Chrome_9.16.0.1569_376" scrumTeamID=10 ]
| stats
count(eval(testResult=0)) as totalTestPassed,
count(eval(testResult=1)) as totalTestFailed,
count(eval(testResult=2)) as totalTestInconclusive
by splunkAutomationTag,
guid,
startTime,
endTime

splunkAutomationTag guid startTime endTime totalTestPassed totalTestFailed totalTestInconclusive

Client_ReleaseChecklist_Platform_Desktop_Chrome_9.16.0.1569_376	2e5c05a0-d6ce-45ff-ab52-e056b666a02f	2020-06-15T14:01:25.17	2020-06-15T14:02:31.603	2	1	26
Client_ReleaseChecklist_Platform_Desktop_Chrome_9.16.0.1569_376	5bced7fb-d153-40a3-85a2-6dfb8cb81166	2020-06-15T13:52:26.81	2020-06-15T14:01:18.16	26	3	0

[javiergn]

Hi @nesa246, sorry but I don't fully understand your last question.

Could you try to reformat your code and tables and perhaps provide an example of what you are expecting VS what you are seeing?

Thanks,

J

[nesa246]

@javiergn  Sorry about the query, formatting was bad

I have another query from the below table,  I have two rows and have to display in one row with the below conditions where 

1. Guid have to match

2. Starttime must be the oldest

3. Endtime must be latest

expected table to be 

splunkAutomationTag guid startTime endTime totalTestPassed totalTestFailed totalTestInconclusive TestsPassed

Client_ReleaseChecklist_Staging_Platform_Desktop_Chrome_9.15.0.1568_359

17796259-7592-4b13-8c30-a2b548d422f1

2020-06-13T19:10:30.783

2020-06-13T19:13:38.087

7

4

0

0