How do I compare fields between two events?

[hacktastic]

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!

[Lowell]

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.

[hacktastic]

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

[gkanapathy · Accepted solution]

Not entirely sure I understand, but how about:

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

[hacktastic]

Awesome. Thanks again!

[gkanapathy]

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

[hacktastic]

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