Question: how do I join the two SQL\SQWL script into one to create a report that pulls last month user login with their assigned active directory security group/windows group?

Question

I'm trying to join two working scripts into one but I'm a beginner at this scripting. 
 Script #1: I have a SWQL script to pull last month user login. 
 SELECT tolocal(TimeLoggedUtc) as [Login] , AccountID , AuditEventMessage FROM Orion.AuditingEvents ae WHERE daydiff9timeloggedutc, getutcdate())<31 AND ae.AuditingActionType.ActionType = 'Orion.UserLogin' Order By TimeLoggedUtc 
 Script #2: I have a SQL to pull the active directory security group/windows group the user is assigned to in the database. 
 SELECT AccountID , GroupInfo FROM [Orion].[dbo].[Accounts] WHERE AccountID like '%ABC\%' 
 Question: how do I join the two script into one to create a report that pulls last month user login with their assigned active directory security group/windows group?

sum_giais · Answer

Could you try this swql query? Might be what you're looking for... there's navigational properties for the Account via SWQL. 
 SELECT
 TOLOCAL(ae.TimeLoggedUtc) AS [TimeStamp]
 ,ae.AccountID AS [Account]
 ,ae.AuditEventMessage AS [Event]
 ,ae.Account.GroupInfo AS [Group Details]
FROM Orion.AuditingEvents AS ae
WHERE DAYDIFF(ae.TimeLoggedUtc, GETUTCDATE()) < 31 
AND ae.AuditingActionType.ActionType = 'Orion.UserLogin'
AND ae.AccountID LIKE '%ABC\%'
ORDER BY ae.TimeLoggedUtc

Question: how do I join the two SQL\SQWL script into one to create a report that pulls last month user login with their assigned active directory security group/windows group?

Top Replies