topic Re: How do I compare fields between two events? in Splunk Search

How do I compare fields between two events?

hacktastic — Wed, 05 May 2010 23:04:24 GMT

Hi,

I'm Splunking some report data that is in CSV format, which may or may not matter in the context of this question. I've got Splunk set up to index the CSV data line-by-line and I've set props.conf and transforms.conf to properly assign fields to the CSV data, so that's all done. I need to do a comparison of the dates between two events that are coming from two different hosts but share common fields. For example:

Log1 from HostA: "field1","field2","field3","dateA"

Log2 from HostB: "field1","field2","field3","dateB"

In plain English: "Match up the lines from HostA and HostB where field1, field2 and field3 are identical, then compare the dates. If the dates do not match, report this back."

I'm drawing a blank on how to do this. Your help is appreciated!

Re: How do I compare fields between two events?

gkanapathy — Wed, 05 May 2010 23:51:18 GMT

Not entirely sure I understand, but how about:

... | stats distinct_count(datefield) as diff_dates by field1,field2,field3 | where diff_dates > 1

Re: How do I compare fields between two events?

Lowell — Wed, 05 May 2010 23:55:48 GMT

Here are two approaches, not sure which will work best for you:

Search 1:

your search | transaction fields="field1,field2,field3" | search duration>0

Search 2:

your search | stats dc(_time) as times, values(_time) as time_values, by field1, field2, field3 | search times>1 | convert ctime(time_values)

It's not clear from your question if dateA and dateB represent two different dates or two different field names. In the searches above, I'm assuming that your actually talking about the timestamp of your event, which is accessible in the _time field.

If you do have two field names, then perhaps this could work for you:

your search | transaction fields="field1,field2,field3" | where dateA!=dateB

Note: You have to use where when you are comparing the value of two different fields. You can use search if you are comparing against a constant value.

Re: How do I compare fields between two events?

hacktastic — Thu, 06 May 2010 00:03:18 GMT

DateA and DateB are part of the results, not time stamps. I need to know if these dates differ.

Re: How do I compare fields between two events?

hacktastic — Thu, 06 May 2010 00:03:59 GMT

That does it, but I'd like to know the difference in time between the two dates as well in, say, hours.

Re: How do I compare fields between two events?

gkanapathy — Thu, 06 May 2010 03:02:00 GMT

... | stats max(datefield) as d1, min(datefield) as d2 by field1,field2,field3 | eval ddiff = d1-d2 | where ddiff <> 0

Re: How do I compare fields between two events?

hacktastic — Thu, 06 May 2010 04:14:50 GMT

Awesome. Thanks again!