SOLVED

query where VM is not login

%3CLINGO-SUB%20id%3D%22lingo-sub-1502034%22%20slang%3D%22en-US%22%3Equery%20where%20VM%20is%20not%20login%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502034%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20any%20one%20help%20me%20query%20where%20VM%20is%20not%20login%20for%20past%2060%20days%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1502034%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1502269%22%20slang%3D%22en-US%22%3ERe%3A%20query%20where%20VM%20is%20not%20login%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502269%22%20slang%3D%22en-US%22%3EDo%20you%20have%2060days%20of%20data%20in%20your%20workspace%3F%20Typically%20you'll%20need%20SecurityEvent%20table%20and%20eventid%204624%20for%20login...do%20you%20have%20this%3F%3C%2FLINGO-BODY%3E
Occasional Contributor

Can any one help me query where VM is not login for past 60 days

4 Replies
Do you have 60days of data in your workspace? Typically you'll need SecurityEvent table and eventid 4624 for login...do you have this?

@Srini1987

@Clive Watson is raising good points - you can only check which accounts actually sent login events in the past but did not send them again over the last 60 days. That means you should have a long retention of those logs. For Windows, you should have something like that:

SecurityEvent
| where TimeGenerated > ago(90d)    // or however long your retention is
| where EventID == 4624             // this is the login event ID
| summarize arg_max(TimeGenerated, *) by TargetAccount// gets the latest login per account
| where TimeGenerated < ago(60d)    // filtering logins events by their last login date

 

Similarly, for Linux it should be (not verified)

LinuxAuditLog
| where TimeGenerated > ago(90d)
| where RecordType == 'user_login'  and res == 'success'
| summarize arg_max(acct, *)
| where TimeGenerated < ago(60d)

@Clive Watson

 

Thanks solution ,i got the point.. 

best response confirmed by Srini1987 (Occasional Contributor)
Solution

@Noa Kuperberg 

 

Perfect solution which i was expected..

Thanks for your time to help on the case.