topic Remove duplicate rows based on condition in Splunk Search

Remove duplicate rows based on condition

nesa246 — Sat, 13 Jun 2020 23:43:15 GMT

testName values 'VerifyBtagsTest' and 'Test_AcceptTAndCModal' values occurred 2 times wanted to take the latest executed row how do I do this

base search..... | table testName Status resultMessage
| where (resultMessage=="null" AND resultMessage != "Test already passed in test plan/run*") OR (Status=="Fail")

testName Status executed resultMessage

VerifyBtagsTest	Fail	2020-06-13T18:17:17.701	System.NullReferenceException
BonusBalanceTrackingTest	Fail	2020-06-13T18:10:36.249	System.NullReferenceException
Test_AcceptTAndCModal	Fail	2020-06-13T18:10:36.249	OpenQA.Selenium.NoSuchElementException
VerifyBtagsTest	Fail	2020-06-13T18:10:36.249	OpenQA.Selenium.NoSuchElementException
BonusBalanceTrackingTest	Pass	2020-06-13T18:17:17.702	null
Test_AcceptTAndCModal	Pass	2020-06-13T18:17:17.702	null
MarketBannerWithOutcomesFunctionalityTest	Pass	2020-06-13T18:15:50.825	null
BasicBannerPromotionTest	Pass	2020-06-13T18:15:30.316	null
BelgiumLoadBankingDesktopTest	Pass	2020-06-13T18:15:20.831	null
MaltaLoadBankingDesktopTest	Pass	2020-06-13T18:15:13.02	null

Re: Remove duplicate rows based on condition

richgalloway — Mon, 15 Jun 2020 11:15:02 GMT

Use the dedup command to remove duplicate events. It selects the most recent events for each unique value of the given field.

base search..... | where (resultMessage=="null") OR (Status=="Fail") | dedup testName | table testName Status resultMessage

Re: Remove duplicate rows based on condition

bowesmana — Mon, 15 Jun 2020 04:19:07 GMT

Assuming you still have the _time field in the base search, then, on the assumption that the where clause test applies to the data prior to evaluating latest, then

| where (resultMessage=="null") OR (Status=="Fail") | table _time testName Status executed resultMessage | stats latest(*) as * by testName

will pick the latest one for each testName. If you do not have _time, then you can create time from the 'executed' field with

| eval _time=strptime(executed, "%F %T.%Q")

before the table statement.

Note that your original resultMessage test was wrong as it is checking for =="null" AND NOT - so it will always be true, so you will need to correct according to your requirements. I have removed the redundant part above.