I found one that is probably the most missed feature in SEPM by every admin.
When you need to get all logs for a client or for a group currently you may need to waste like >5 mins to get all your logs related to System, Security, Behavior and Traffic Log
Here the lifechanging SQL that will deliver all logs right away... Just put your part of the groupname or hostname into the declaration and execute the statement.
DECLARE @Hostname nvarchar(512)
SELECT @Hostname = '%%'
DECLARE @Groupname nvarchar(2000)
SELECT @Groupname = '%%'
SELECT
DATEADD(HOUR,+2,DATEADD(SECOND,ASL.EVENT_TIME / 1000, '1970/01/01 00:00')) AS "EVENT-TIME",
ASL.HOST_NAME AS "COMPUTER",
S.NAME AS "SEPM",
G.NAME AS "GROUP",
ASL.EVENT_SOURCE AS "EVENT-SOURCE",
ASL.EVENT_DESC AS "DESCRIPTION",
ASL.EVENT_DATA AS "RULENAME",
'N/A' AS "LOCAL-IP",
'N/A' AS "REMOTE-IP",
'N/A' AS "APPLICATION",
'N/A' AS "LOCATION",
'N/A' AS "INTRUSION URL",
NULL AS "LOCAL-PORT",
NULL AS "REMOTE-PORT"
FROM dbo.V_AGENT_SYSTEM_LOG ASL
JOIN dbo.V_GROUPS G
ON ASL.GROUP_ID = G.ID
JOIN dbo.V_SERVERS S
ON ASL.SERVER_ID = S.ID
WHERE G.NAME like @Groupname
AND ASL.HOST_NAME like @Hostname
UNION ALL
SELECT
DATEADD(HOUR,+2,DATEADD(SECOND,ASL.EVENT_TIME / 1000, '1970/01/01 00:00')) AS "EVENT-TIME",
ASL.HOST_NAME AS "COMPUTER",
S.NAME AS "SEPM",
G.NAME AS "GROUP",
'N/A' AS "EVENT-SOURCE",
ASL.EVENT_DESC AS "DESCRIPTION",
'N/A' AS "RULENAME",
ASL.LOCAL_HOST_IP_TEXT AS "LOCAL-IP",
ASL.REMOTE_HOST_IP_TEXT AS "REMOTE-IP",
ASL.APP_NAME AS "APPLICATION",
ASL.LOCATION_NAME AS "LOCATION",
(ASL.INTRUSION_URL + ' _Payload URL/' + ASL.INTRUSION_PAYLOAD_URL) AS "INTRUSION URL",
NULL AS "LOCAL-PORT",
NULL AS "REMOTE-PORT"
FROM dbo.V_AGENT_SECURITY_LOG ASL
JOIN dbo.V_GROUPS G
ON ASL.GROUP_ID = G.ID
JOIN dbo.V_SERVERS S
ON ASL.SERVER_ID = S.ID
WHERE G.NAME like @Groupname
AND ASL.HOST_NAME like @Hostname
UNION ALL
SELECT
DATEADD(HOUR,+2,DATEADD(SECOND,ABL.EVENT_TIME / 1000, '1970/01/01 00:00')) AS "EVENT-TIME",
ABL.HOST_NAME AS "COMPUTER",
S.NAME AS "SEPM",
G.NAME AS "GROUP",
CASE
WHEN ABL.VAPI_NAME is NULL THEN 'Tamper Protection'
ELSE ABL.VAPI_NAME
END AS "EVENT-SOURCE",
ABL.DESCRIPTION AS "DESCRIPTION",
ABL.RULE_NAME AS "RULENAME",
'N/A' AS "LOCAL-IP",
'N/A' AS "REMOTE-IP",
ABL.CALLER_PROCESS_NAME AS "APPLICATION",
'N/A' AS "LOCATION",
'N/A' AS "INTRUSION URL",
NULL AS "LOCAL-PORT",
NULL AS "REMOTE-PORT"
FROM dbo.V_AGENT_BEHAVIOR_LOG ABL
JOIN dbo.V_GROUPS G
ON ABL.GROUP_ID = G.ID
JOIN dbo.V_SERVERS S
ON ABL.SERVER_ID = S.ID
WHERE G.NAME like @Groupname
AND ABL.HOST_NAME like @Hostname
UNION ALL
SELECT
DATEADD(HOUR,+2,DATEADD(SECOND,ATL.EVENT_TIME / 1000, '1970/01/01 00:00')) AS "EVENT-TIME",
ATL.HOST_NAME AS "COMPUTER",
S.NAME AS "SEPM",
G.NAME AS "GROUP",
'N/A' AS "EVENT-SOURCE",
('Action: '+ CASE
WHEN ATL.BLOCKED = 1 THEN 'BLOCKED'
WHEN ATL.BLOCKED = 0 THEN 'ALLOWED'
END
+' - Protcol: '+
CASE
WHEN ATL.NETWORK_PROTOCOL = 1 THEN 'IP, Ethernet'
WHEN ATL.NETWORK_PROTOCOL = 2 THEN 'TCP'
WHEN ATL.NETWORK_PROTOCOL = 3 THEN 'UDP'
WHEN ATL.NETWORK_PROTOCOL = 4 THEN 'ICMP'
END
+' - Direction: '+
CASE
WHEN ATL.TRAFFIC_DIRECTION = 1 THEN 'IN'
WHEN ATL.TRAFFIC_DIRECTION = 2 THEN 'OUT'
WHEN ATL.TRAFFIC_DIRECTION = 0 THEN 'Unknown'
END )AS "DESCRIPTION",
ATL.RULE_NAME AS "RULENAME",
ATL.LOCAL_HOST_IP_TEXT AS "LOCAL-IP",
ATL.REMOTE_HOST_IP_TEXT AS "REMOTE-IP",
ATL.APP_NAME AS "APPLICATION",
ATL.LOCATION_NAME AS "LOCATION",
'N/A' AS "INTRUSION URL",
ATL.LOCAL_PORT AS "LOCAL-PORT",
ATL.REMOTE_PORT AS "REMOTE-PORT"
FROM dbo.V_AGENT_TRAFFIC_LOG ATL
JOIN dbo.V_GROUPS G
ON ATL.GROUP_ID = G.ID
JOIN dbo.V_SERVERS S
ON ATL.SERVER_ID = S.ID
WHERE G.NAME like @Groupname
AND ATL.HOST_NAME like @Hostname
ORDER BY "EVENT-TIME" DESC, "COMPUTER"
;