Adding 'host' field to a set diff command

[leonienicks]

My set diff query compares the values of one field from two different hosts and outputs a list of the field values that are unique to one host or the other. However, I can't seem to find a way to also display the host name alongside the output. I am trying to get a list of values that are unique to one host and know which host they have come from.

The query follows this format:

| set diff [index=example sourcetype=example host=host1 | table FIELD] [index=example sourcetype=example host=host2 | table FIELD]

It will then output a list of values for that field that are unique to one host or the other, but I have no way of knowing which host they are unique to. If I include host in the table part of the subsearches it will return all entries, since the host is different in all cases.

I'm looking for something like this

Field host
1234 host1
5678 host2
9101 host1
2345 host1

Any suggestions?

Thanks!

[woodcock · Accepted solution]

Do this:

index=example sourcetype=example host=host1 OR host=host2
| stats values(host) AS hosts BY FIELD
| where mvcount(hosts)=1

[leonienicks]

Thanks so much - this is great! The only thing is that it won't display any duplicate extra results to one host so for example, if host1 has event A once and host2 has event A twice, since it's the same value it won't show in this. Perhaps there is a separate query that would fetch this?

[woodcock]

OK, try this:

index=example sourcetype=example host=host1 OR host=host2
| stats count BY host FIELD
| eventstats dc(host) AS hosts BY FIELD
| where Your Logic Here

[leonienicks]

Thanks! This inspired me to do something else I was working on, which was that for the same unique ID field, fetching the results that had differences elsewhere in the events:
index=example sourcetype=example host=host1 OR host=host2
| eventstats dc(host) AS uniqueID BY FIELD
| where uniqueID = 2
| eval raw=toString(FIELD)+"|"+toString(FIELD2)+"|"+toString(FIELD3)
| stats values(host) AS hosts BY raw
| where mvcount(hosts) = 1