Using field from query results as new search term

Elastic Stack Elasticsearch
1

Hello

I have logs of Windows events which show users who add themselves temporarily to an admin group. I need a query to find users who have added themselves to an admin group but not removed themselves afterwards.

So, we first search on one event ID, and take the username from the results. We then search the same index for events containing those users and the second event ID. We subtract these results from the first set, so it leaves those users from the first results set who added themseves to a group but did not then remove themselves.

Is this possible?

Thank you

2

You could use aggregations to group a summary of related events for each user on-the-fly e.g. this simplified example would count the number of logins vs logouts for each user:

DELETE test
PUT test
{
	"settings": {
		"number_of_shards": 1,
		"number_of_replicas": 0
	},
	"mappings": {
		"doc": {
			"properties": {
				"user": {
					"type": "keyword"
				},
				"event": {
					"type": "keyword"
				}
			}
		}
	}
}
POST test/doc
{
	"user":"mark",
	"event":"login"
}
POST test/doc
{
	"user":"mark",
	"event":"logout"
}
POST test/doc
{
	"user":"dave",
	"event":"login"
}
GET test/doc/_search
{
	"size":0,
	"aggs":
	{
		"users":{
			"terms":{
				"field":"user"
			},
			"aggs":{
				"logins":{
					"terms":{
						"field":"event"                        
					}
				}             
			}
		}
	}
}

You would then need to post-process the results in your client to spot the behaviours you are interested in. However, this may require a lot of memory if you have a lot of unique users. This sort of behavioural analysis is often best tackled by maintaining an entity centric index alongside your event centric index. See here for discussion and example design/scripts https://www.youtube.com/watch?v=yBf7oeJKH2Y

1 Like
3

Thank you, I will take a look at the videos you suggest