Splunk Search

How to merge two fields and remove values that are identical in both?

karthik4455
Explorer

Escalated_Tickets Resolved_Tickets
4334
3453
5545
8438
7565
8948
8877
4675
9868
4334
3453
5568
5545
8438
6932
7565
8948
8877
1258
6589
4675
9868
What I need is the below output.
Tickets
5568
6932
1258
6589

Single output which will remove entries that are identical in Escalated_Tickets & Resolved_Tickets

Tags (2)
0 Karma

chris
Motivator

--- Updated (thanks aweitzman) ---

I am assuming that every line in your question is an event. You cant try this:

basesarch | eval ticket_id=coalesce(Escalated_Tickets,Resolved_Tickets) |  eval marker=case(isnull(Escalated_Tickets), "Resolved",isnull(Resolved_Ticket), "Escalated") | stats count dc(marker) as markers values(marker) by ticket_id  | where markers<2
  • The first eval will make sure that you have the same field name for your ticket ids
  • The marker eval will make sure, that if the same ticket is escalated or resolved multiple times it will not disappear

This might give you an idea for a solution:
http://blogs.splunk.com/2011/01/11/maintaining-state-of-the-union/

Chris

chris
Motivator

I updated the answer

0 Karma

chris
Motivator

Ouch I didn't think about that you're right

0 Karma

aweitzman
Motivator

I don't think this is quite good enough, since it won't handle the case where there is more than one anomalous Resolved_Tickets number of the same value. In other words, if "6932" was instead "6589" these searches would eliminate them both.

I think the solution might involve writing subsearches to retrieve each column separately, and then using set diff to come up with the difference between the two. Not ideal from a performance perspective, but it should be more accurate.

Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...