seajaysec
commented
4 years ago Fixed! I've had that error with a few other queries, but it hadn't come up with that one in my testing. That'll come up when the query tries to convert the Epoch time to something more human readable and encounters a NULL value. I added in a check that converts NULL epoch time values to epoch time 1, which is then represented as 1970-01-01T00:00:00Z once converted to human readable format.
Here's what the query looks like broken out:
MATCH (u:User) MATCH (g:Group {name:'DOMAIN ADMINS@TESTLAB.LOCAL'})
SET u.llInt = coalesce(u.lastlogon,'1')
SET u.lldInt = coalesce(u.lldate,'1')
SET u.lltsInt = coalesce(u.lastlogontimestamp,'1')
SET u.pwdlsInt = coalesce(u.pwdlastset,'1')
RETURN u.name AS UserName,
u.displayname AS DisplayName,
u.domain AS Domain,
u.enabled AS Enabled,
u.highvalue AS HighValue,
u.objectsid AS SID,
u.description AS Description,
u.title AS Title,
u.email as Email,
datetime({epochSeconds:toInteger(u.llInt)}) AS LastLogon,
datetime({epochSeconds:toInteger(u.lldInt)}) AS LLDate,
datetime({epochSeconds:toInteger(u.lltsInt)}) AS LLTimeStamp,
datetime({epochSeconds:toInteger(u.pwdlsInt)}) AS PasswordLastSet,
u.owned AS Owned, u.sensitive AS Sensitive,
u.admincount AS AdminCount,
u.hasspn AS HasSPN,
u.unconstraineddelegation AS UnconstrainedDelegation,
u.dontreqpreauth AS DontReqPreAuth,
u.passwordnotreqd AS PasswordNotRequired,
u.homedirectory AS HomeDirectory,
u.serviceprincipalnames AS ServicePrincipalNamesThis is testing well for me on a sample dataset I found that was producing the same error. Let me know if the latest version is working for you.
mubix
The 'All Domain Admins' query uses Last Logon with a datetime function but the entire query fails if any of the Domain Admins have "never" signed in. I'm sorry I don't know Cypher enough to know how to fix it.