So I have two similar searches that use two different indexes. The output of both searches are tables and what I want is to compare 1st column from table 1 (result of Search 1) and 1st column from table 2 (result of Search 2).
Result should be diff of those two columns. Both columns contain a list of users and what I need are all users from column 1 (table1) that are not contained in column 2 (table2). Both columns in both tables are named "user" and both contain same usernames but 1st table has all usernames and 2nd table have just few usernames.
Search 1:
index="A" host="XYZ" source="ASDF" user="*" | table user host _time
Search 2:
index="B" host="XYZ" source="FDSA" user="*" | table user host _time
I thought that It can be solved with:
| set diff [Search1][Search2]
but for some reason it doesn't give me result that I need.
I would appreciate any help with this problem.
Try something like this
( index="A" source="ASDF") OR (index="B" source="FDSA") host="XYZ" source="ASDF" user="*" | table user host _time source | eventstats values(source) as source
Now to get the users which are not present in both the index/source, add this to above search
...| where mvcount(source) =1
For users only in index A,
..| where mvcount(source)=1 AND source="ASDF"
In a hurry I responded this as Answer earlier so I ask moderator to ignore it if possible.
Back to the question. Somesoni2 your answer got me some results and I thank you on that but still it's not quite what I need.
New search looks like this:
[search index="A" source="/var/log/splunkusers" host="XYZ" user="*"] OR [search index="B" source="/var/log/secure" host="XYZ" user="*" | dedup user] | table user host _time source
and as a result I get:
userA XYZ 2015-09-16 16:11:16 /var/log/secure
userB XYZ 2015-09-23 15:24:38 /var/log/secure
userC XYZ 2015-10-12 14:00:54 /var/log/secure
userA XYZ 2015-10-14 07:42:29 /var/log/splunkusers
userB XYZ 2015-10-14 07:42:29 /var/log/splunkusers
userC XYZ 2015-10-14 07:42:29 /var/log/splunkusers
userD XYZ 2015-10-14 07:42:29 /var/log/splunkusers
userF XYZ 2015-10-14 07:42:29 /var/log/splunkusers
and result what I need is:
userD XYZ 2015-10-14 07:42:29 /var/log/splunkusers
userF XYZ 2015-10-14 07:42:29 /var/log/splunkusers
So basically I need only users from source /var/log/splunkusers that are not in /var/log/secure
you suggested use of:
| eventstats values(source) as source
but it just groups me sources and there is no row with only one source so that I could use:
...| where mvcount(source) =1
as you suggested. I get what you wanted to achieve and it would be ok. Can you please check my new search and example of results I get and results I need? It might give you better idea for possible solution.