Home

Office365/AzureAd last logon

%3CLINGO-SUB%20id%3D%22lingo-sub-331913%22%20slang%3D%22en-US%22%3EOffice365%2FAzureAd%20last%20logon%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-331913%22%20slang%3D%22en-US%22%3E%3CP%3EI%20ingest%20the%20Office365%20unified%20audit%20log%20and%20AAD%20Signin%20Logs%20into%20Azure%20log%20analytics%2C%20and%20now%20use%20log%20analytics%20for%20almost%20all%20of%20my%20search%20requirements%20across%20Office365%2C%20Security%2C%20Audit%20etc..%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGiven%20the%20difficulty%20for%20IT%20Admins%20to%20audit%20user%20inactivity%20these%20days%2C%20(seen%20here%20%3CA%20href%3D%22https%3A%2F%2Ffeedback.azure.com%2Fforums%2F169401-azure-active-directory%2Fsuggestions%2F15150216-include-users-last-logon-time%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ffeedback.azure.com%2Fforums%2F169401-azure-active-directory%2Fsuggestions%2F15150216-include-users-last-logon-time%3C%2FA%3E)%20as%20the%20%22Last%20Logon%22%20date%20in%20Exchange%20Online%20is%20reliable%2C%20and%20it%20doesn't%20account%20for%20the%20other%20Office365%20products.%26nbsp%3B%26nbsp%3BI%20know%20the%20Management%20API%20has%20some%20helpful%20attributes%20but%20the%20lastlogon%20date%20only%20allows%20you%20to%20go%20back%2030%20days..%20which%20is%20not%20enough.%26nbsp%3B%20Yes%20i%20understand%2C%20i%20can%20pull%20down%20data%20over%20a%203%20month%20period%2C%20then%20crunch%20that%20together%20and%20roll%20my%20own%20last%20logon%20date%20from%20that%2C%20but%20i'd%20prefer%20not%20to.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Azure%20AD%20Sign%20in%20logs%20do%20provide%20all%2C%20...%20nearly%20all%20the%20necessary%20data%20to%20detect%20user%20inactivity%2C%20but%20i%20need%20some%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20the%20below%20query%20against%20the%20SigninLogs%20table%2C%20but%20i%20cannot%20work%20out%20of%20find%20the%20query%20syntax%20for%26nbsp%3B%3CSTRONG%3E%22is%20NOT%20greater%20than%2090%20days%20ago%22.%26nbsp%3B%26nbsp%3B%3C%2FSTRONG%3EThere%20does%20not%20seem%20to%20be%20an%20operator%20for%20%22!%26gt%3B%22%20or%20%22not%20%26gt%3B%22.%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CPRE%3ESigninLogs%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26lt%3B%20ago(90d)%26nbsp%3B%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%22is%20NOT%20greater%20than%2090%20days%20ago%22%3CBR%20%2F%3E%7C%20where%20UserPrincipalName%20endswith%20%22co.uk%22%3CBR%20%2F%3E%7C%20where%20ResultType%20%3D%3D%200%3CBR%20%2F%3E%7C%20project%26nbsp%3BUserPrincipalName%3C%2FPRE%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EAny%20help%20to%20make%20this%20query%20work%20would%20be%20amazing!%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-331913%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-370124%22%20slang%3D%22en-US%22%3ERe%3A%20Office365%2FAzureAd%20last%20logon%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-370124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9172%22%20target%3D%22_blank%22%3E%40Stanislav%20Zhelyazkov%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20they%20are%20asking%20about%20correlating%20the%20office365%20audit%20logs%20to%20azure%20ad%20auditg%20logs%2C%20more%20specifically%20sign%20in%20attributes.%26nbsp%3BThe%20problem%20is%20the%20office%20365%20logs%20do%20not%20expose%20any%20true%20sign%20in%20event%20to%20correlate%20the%20two%20data%20sources%20from%20what%20I%20can%20tell.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332785%22%20slang%3D%22en-US%22%3ERe%3A%20Office365%2FAzureAd%20last%20logon%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332785%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EI%20am%20not%20sure%20if%20I%20can%20understand%20the%20request%20exactly%20but%20I%20will%20try%20to%20answer.%20I%20would%20like%20to%20first%20make%20a%20note%20that%20TimeGenerated%20is%20the%20time%20the%20log%20was%20generated.%20Keep%20in%20mind%20that%20there%20might%20be%20a%20different%20date%2Ftime%20column%20that%20shows%20the%20date%20of%20the%20login%20activity.%20With%20the%20above%20query%20you%20have%20pasted%20and%20specifically%3C%2FP%3E%0A%3CPRE%3E%7C%20where%20TimeGenerated%20%26lt%3B%20ago(90d)%20%3C%2FPRE%3E%0A%3CP%3Eyou%20are%20actually%20taking%20all%20records%20that%20have%20happened%20before%2090%20days.%20So%20I%20think%20this%20is%20what%20you%20are%20actually%20requested.%3C%2FP%3E%0A%3CP%3EYou%20can%20do%20also%20additional%20things%20like%3C%2FP%3E%0A%3CPRE%3E%7C%20where%20TimeGenerated%20%26lt%3B%20ago(90d)%20and%20TimeGenerated%20%26gt%3B%20ago(120d)%3C%2FPRE%3E%0A%3CP%3ETo%20do%20exact%20time%20frame.%3C%2FP%3E%0A%3CP%3EAlso%20to%20note%20that%20by%20default%20Log%20Analytics%20stores%20data%20for%2031%20days.%20If%20you%20want%20to%20go%20beyond%20you%20will%20need%20to%20extend%20the%20retention%20period%20of%20your%20Log%20Analytics%20workspace.%3C%2FP%3E%0A%3CP%3ELet%20me%20know%20if%20this%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

I ingest the Office365 unified audit log and AAD Signin Logs into Azure log analytics, and now use log analytics for almost all of my search requirements across Office365, Security, Audit etc..  

 

Given the difficulty for IT Admins to audit user inactivity these days, (seen here https://feedback.azure.com/forums/169401-azure-active-directory/suggestions/15150216-include-users-l...) as the "Last Logon" date in Exchange Online is reliable, and it doesn't account for the other Office365 products.  I know the Management API has some helpful attributes but the lastlogon date only allows you to go back 30 days.. which is not enough.  Yes i understand, i can pull down data over a 3 month period, then crunch that together and roll my own last logon date from that, but i'd prefer not to.

 

The Azure AD Sign in logs do provide all, ... nearly all the necessary data to detect user inactivity, but i need some help.

 

I am using the below query against the SigninLogs table, but i cannot work out of find the query syntax for "is NOT greater than 90 days ago".  There does not seem to be an operator for "!>" or "not >".

 
SigninLogs
| where TimeGenerated < ago(90d) 
| where TimeGenerated "is NOT greater than 90 days ago"
| where UserPrincipalName endswith "co.uk"
| where ResultType == 0
| project UserPrincipalName
 
Any help to make this query work would be amazing!
2 Replies

Hi,

I am not sure if I can understand the request exactly but I will try to answer. I would like to first make a note that TimeGenerated is the time the log was generated. Keep in mind that there might be a different date/time column that shows the date of the login activity. With the above query you have pasted and specifically

| where TimeGenerated < ago(90d) 

you are actually taking all records that have happened before 90 days. So I think this is what you are actually requested.

You can do also additional things like

| where TimeGenerated < ago(90d) and TimeGenerated > ago(120d)

To do exact time frame.

Also to note that by default Log Analytics stores data for 31 days. If you want to go beyond you will need to extend the retention period of your Log Analytics workspace.

Let me know if this helps.

@Stanislav Zhelyazkov 

 

I believe they are asking about correlating the office365 audit logs to azure ad auditg logs, more specifically sign in attributes. The problem is the office 365 logs do not expose any true sign in event to correlate the two data sources from what I can tell.