Splunk Search
Highlighted

How to compare two columns from two searches and display the values that only exist in the 2nd column?

Engager

Hi all, I'm fairly new to splunk so I hope you can help me.

I have two searches that retrieve two columns of taskids. I need to compare column A (currently failing tasks) to column B (tasks that failed in the last week) and produce a list of tasks that have just started to fail.

The query below is slightly simplified from what I use. It returns the two columns of task id values:
(TaskID and TaskIDHistoric are retrieved using a REX expression)

 host="A*" SourceName="Task" earliest=@d 
| stats values(TaskID) as TaskID 
| appendcols [search host="A*" SourceName="Task" earliest=-7d@d latest=-1d@d 
| stats values(TaskIDHistoric) as TaskIDHistoric ]

TaskID |TaskIDHistoric

1   | 1
2   | 2
4   | 7
7   | 8

I now have two columns. I want to compare the values from TaskID to the values in TaskIDHistoric and produce a list of the values that only exist in TaskID. (I don't care about values that exist in TaskIDHistoric which no longer appear in TaskID)

The output from the above table that I need would be task 4.

Thanks!

0 Karma
Highlighted

Re: How to compare two columns from two searches and display the values that only exist in the 2nd column?

Engager

I was able to get the required data by making TaskID a multi-value field and then finding the values that do not exist in TaskIDHistoric.

This seems to work ok...

 host="A*" SourceName="Task" earliest=@d 
| stats values(TaskID) as TaskID 
| appendcols [search host="A*" SourceName="Task" earliest=-7d@d latest=-1d@d 
| stats values(TaskIDHistoric) as TaskIDHistoric ]
| makemv TaskID
| mvexpand TaskID
| where not match(TaskIDHistoric, TaskID) 
| table TaskID

If there is a more efficient/better way of doing this, I'd love to know.