Splunk Search

Best approach for comparing multiple search results with same fields

turbocharger
Explorer

I'm trying to find an elegant solution to compare the results of multiple searches - all of which have  identical field names. I need to compare all values in the same fields to each other and if a condition is met, display the fields in the event. I have seen a lot of examples that show how to do this using different field names. However, I don't see a clear way to loop through the index of the same fields in all events. 

The only way I have been able to find that can work (and it is highly undesirable) is to rename the fields of each search and then comparing them. For instance:

say search1 returns: 'day_of_week', 'color', 'shape', 'distance'

one would need to rename the fields in search2 to :'day_of_week2', 'color2', 'shape2', 'distance2' to compare them. If you have a lot of fields (which I do) this gets complex and error prone fast.

Is there a way to assign a reference to a search assign a reference and be able to compare like:

foreach search1.day_of_week != search2.day_of_week

Or somehow leverage the field's index to loop through the field  values in all events and compare. 

My goal is to compare all instances of each field (and combinations of field values) to each other. Based on equality or inequality, I want to step through the data and make a final comparison if the prior conditions are satisfied.

For example, I'd like to compare a particular event field 'distance' to 'distance' in all other events if the values in 'day_of_week' AND 'color' AND 'shape' are equal (or not equal). Is there any straightforward way of doing this without a lot of complex logic or explicitly defining a value to compare to? I don't want to define the values I am looking for in advance and would like to use the values returned in the search field to set the value to be compared. This feels like a simple task but I am not finding an elegant solution. I'd be truly grateful for some suggestions.

 

 

Labels (2)

turbocharger
Explorer

Thanks for the response! To explain, here is 1 row of data for 5 searches. Each search returns several rows of data like this. I just listed 1 row of data for each search for simplicity. Actually, to simplify further I could just show two searches.

search1: day_of_week', 'color', 'shape', 'distance' : Monday, black, square, 12

search2: day_of_week', 'color', 'shape', 'distance' : Friday, blue, triangle, 22

search3: day_of_week', 'color', 'shape', 'distance' : Tuesday, red, octagon, 22

search4: day_of_week', 'color', 'shape', 'distance' : Saturday, green, triangle, 12

search5: day_of_week', 'color', 'shape', 'distance' : Saturday, green, triangle, 12

I would like to apply the following logic to compare all instances of returned fields:

| eval flagIt=If (day_of_week!=day_of_week AND color!=color AND shape=shape AND distance=distance), flagIt, "True")

The problem is this logic doesn't work because the condition is always true with 'field=field.' I am coming from a programmer's perspective so this tells me you need to loop, iterate, increment to do these comparisons. This feels like it would be a common case - comparing events by values returned in fields - like day_of_week - and processing that data to determine the presence or absence of conditions based on logic. I suspect this requires | foreach and it isn't clear if I need to rename the fields for the different searches to compare them or not. The documentation for this function appears to be quite hard to follow.

ITWhisperer
Ultra Champion

It looks like you want to find events where day_of_week, color, shape and distance are unique across your whole data set?

| eventstats count by day_of_week color shape distance
| where count=1
0 Karma

turbocharger
Explorer

I have a solution that works - but I am not crazy about the method used. I would prefer to not have to rename the fields because this adds a lot of steps as the number of searches increase.

 

| search1 | appendcols [ |search2  |  rename day_of_week as day_of_week2 ....etc..]  | where day_of_week!=day_of_week2 ...etc

I wanted to post this for someone else that gets stuck on the same problem. I hope it helps someone and if there is a better way I would love to know what it is.

0 Karma

ITWhisperer
Ultra Champion

You can use 

| stats list(distance) as distance by day_of_week color shape

to group distances where day_of_week, color and shape are the same, but it is not clear whether this is useful without knowing what sort of comparison you are looking for. (Your base search for this could be all your searches combined e.g. index=abc OR index=xyz ...)

Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!