Splunk Search

How to compare values on the same columns and on the same date

Path Finder

I have this table, and i just want to remove the rows that has the same cost on that date if the B1 of that row is on the B2 of another row. So basically, I just want to remove rows 4 and 5. Is there any way to do this? Thanks.
alt text

0 Karma
1 Solution

Builder

Hi iancorrea,

I have a crazy search that works how you want it to, but you have to have the Line number field in there.

... [your search] ...
| sort Line
| streamstats current=f window=1 last(B2) as lastb2 last(B1) as lastb1 last(Cost) as lastcost last(Line) as lastline
| eval match = if(Cost == lastcost AND B1 == lastb2, 1, 0)
| eval removeline = case(match == 1, split(lastline . "," . Line, ","))
| eventstats values(removeline) as removeline
| where Line!=removeline
| table Line Date Log B1 B2 Cost Reference

Basically, it uses streamstats to compare to the previous event's values and then uses eventstats to provide a list of line numbers to remove using the where statement.

View solution in original post

Builder

Hi iancorrea,

I have a crazy search that works how you want it to, but you have to have the Line number field in there.

... [your search] ...
| sort Line
| streamstats current=f window=1 last(B2) as lastb2 last(B1) as lastb1 last(Cost) as lastcost last(Line) as lastline
| eval match = if(Cost == lastcost AND B1 == lastb2, 1, 0)
| eval removeline = case(match == 1, split(lastline . "," . Line, ","))
| eventstats values(removeline) as removeline
| where Line!=removeline
| table Line Date Log B1 B2 Cost Reference

Basically, it uses streamstats to compare to the previous event's values and then uses eventstats to provide a list of line numbers to remove using the where statement.

View solution in original post

Path Finder

Thanks for researching for a solution sir, I appreciate your effort 🙂 But i came up to another solution which I think the same concept as your answer. I made another field named "Comparison" and used eventstats to compare. This is what I've done,

1. ... [my search] ...
2. | eval Comparison=if(Log=="In", Date+"|"+B1+"|"+B2+"|"+Cost,Date+"|"+B2+"|"+B1+"|"+Cost)
3. | eventstats count as Status by Comparison
4. | eval Status=if(Status=="1","Failed","Passed")
5. | where Status="Failed"
0 Karma

Contributor

In that case, why is row 6 the one that stays? Is it because it's the latest one? How would we know that other than the ID?

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!