Skip to main content
Help Center Logpoint Docs Idea Portal Logpoint.com

Hello, 

I am trying to automate getting some statistics I have to report to the executive relating to the number of resolved and unresolved alerts we are dealing with as a security team.
I’m starting with the the alerts out of Office 365 as I thought that might be easier. The end goal being to aggregate the alerts from a number of different sources and either provide a regular report or dashboard for the executive. However I am starting small.

Getting the number of resolved alerts is fairly straightforward 

norm_id="Office365" label="Alert" status="Resolved" host="SecurityComplianceCenter" alert_name=* | chart count()

however getting the unresolved alerts is not quite as easy. My initial test involved negating the status field in the query above. Whist this gave a result, it was not very accurate. 

The problem appears to be that the status can be in one of 3 states, “Active”, “Investigating” or “Resolved”, so my first attempt was counting multiple log entries for the same alert. 

After some more experimentation I have come up with 

wnorm_id="Office365" label="Alert" -status="Resolved" host="SecurityComplianceCenter" alert_name=*] as search1  
left join
norm_id="Office365" label="Alert" status="Resolved" host="SecurityComplianceCenter" alert_name=* ] as search2 
on search1.alert_id = search2.alert_id | count()

This sort of works and the result is more accurate, but still very different to what Office 365 shows. 

I think I need to check  that an entry with an Active or an Investigating state only counts once for the same alert_id before it is checked against whether there is a corresponding resolved entry for that alert_id

I am not sure how to achieve this, whether it would need a nested join, or whether nested joins are even possible.

 

Any hints, or better ways of achieving this would be greatly appreciated.

 

Thanks

Jon

Unfortunately, there can only be one Join - but just as food for thought, one often overlooked bit of functionality that has saved my bacon in scenarios like this is to use lists and tables, and create multiple queries that populate them. Or perhaps you could use the distinct_list command to create a list of all the alert IDs, but only once.

Not sure that’s very helpful in this context, perhaps other people have better ideas...

That is a help, I had overlooked the tables and lists options so I will give it a go and see what I come up with.

 

Reply


Terms & ConditionsCookie settings