Improvement to the large query

[AdrianVollmer]

Very interesting approach. I made some improvements.

• replace all ' with " to be consistent, so we can easily put the query inside quotes.
• make use of well-known SIDs to identify the group of domain controllers. The name of the group depends on the AD's language. The name of the OU in the DN however, does not, so I left it in, even though there is probably a better way to handle this.
• replace the domain with a parameter, so I can execute this query like so:

$ cypher-shell -u neo4j -p XXXXX --encryption false -P "domain => 'mydomain.local'" "$(cat general-query)"

Result:

MATCH (u:User)                                        WHERE toLower(u.name) ENDS WITH $domain RETURN "Users in total" AS what, count(u) AS number UNION ALL
MATCH (u:User {enabled: false})                       WHERE toLower(u.name) ENDS WITH $domain RETURN "Disabled Users" AS what, count(u) AS number UNION ALL
MATCH (u:User {enabled: true, allowedtodelegate: true}) WHERE toLower(u.name) ENDS WITH $domain RETURN "Enabled Users with Allowed to Delegate" AS what, count(u) AS number UNION ALL
MATCH (u:User {enabled: true, unconstraineddelegation: true}) WHERE toLower(u.name) ENDS WITH $domain RETURN "Enabled Users with Unconstrained Delegation" AS what, count(u) AS number UNION ALL
MATCH (u:User {enabled: true, admincount: true})      WHERE toLower(u.name) ENDS WITH $domain RETURN "Enabled Users with Admin Count = 1" AS what, count(u) AS number UNION ALL
MATCH (u:User {enabled: true,  hasspn: True})         WHERE toLower(u.name) ENDS WITH $domain AND NOT u.name STARTS WITH "KRBTGT" RETURN "Kerberoastable & Enabled Users" AS what, count(u) AS number UNION ALL
MATCH (u:User {enabled: false, hasspn: True})         WHERE toLower(u.name) ENDS WITH $domain AND NOT u.name STARTS WITH "KRBTGT" RETURN "Kerberoastable & Disabled Users" AS what, count(u) AS number UNION ALL
MATCH (u:User {enabled: true, passwordnotreqd: true}) WHERE toLower(u.name) ENDS WITH $domain RETURN "Enabled Users with Password Not Required" AS what, count(u) AS number UNION ALL
MATCH (u:User {enabled: true, pwdneverexpires: true}) WHERE toLower(u.name) ENDS WITH $domain RETURN "Enabled Users with Password Never Expires" AS what, count(u) AS number UNION ALL
MATCH (u:User {enabled: true, dontreqpreauth: true})  WHERE toLower(u.name) ENDS WITH $domain RETURN "Enabled Users with Dont Require Pre-Authentication (ASREP roastable)" AS what, count(u) AS number UNION ALL
MATCH (u:User {enabled: true})                        WHERE toLower(u.name) ENDS WITH $domain AND u.pwdlastset > 0 AND u.lastlogon > 0 WITH u.name AS name, u.description AS description, u.enabled AS enabled, datetime({ epochSeconds:toInteger(u.pwdlastset) }) AS pwdlastset, duration.inDays(datetime({ epochSeconds:toInteger(u.pwdlastset) }), date()).days AS days_since_pwdlastset, datetime({ epochSeconds:toInteger(u.lastlogon) }) AS lastlogon, duration.inDays(datetime({ epochSeconds:toInteger(u.lastlogon) }), date()).days AS days_since_lastlogon WHERE days_since_pwdlastset > 90 AND days_since_lastlogon < 7 RETURN "Enabled Users pwdlastset > 90 days and lastlogon < 7 days" AS what, count(name) AS number UNION ALL
MATCH (u:User {enabled: true})                        WHERE toLower(u.name) ENDS WITH $domain AND u.pwdlastset > 0 AND u.lastlogon > 0 WITH u.name AS name, u.description AS description, u.enabled AS enabled, datetime({ epochSeconds:toInteger(u.pwdlastset) }) AS pwdlastset, duration.inDays(datetime({ epochSeconds:toInteger(u.pwdlastset) }), date()).days AS days_since_pwdlastset WHERE days_since_pwdlastset > 90 RETURN "Enabled Users pwdlastset > 90 days" AS what, count(name) AS number UNION ALL
MATCH (u:User {enabled: true})                        WHERE toLower(u.name) ENDS WITH $domain AND u.pwdlastset > 0 AND u.lastlogon > 0 WITH u.name AS name, u.description AS description, u.enabled AS enabled, datetime({ epochSeconds:toInteger(u.lastlogon) }) AS lastlogon, duration.inDays(datetime({ epochSeconds:toInteger(u.lastlogon) }), date()).days AS days_since_lastlogon WHERE days_since_lastlogon > 90 RETURN "Enabled Users lastlogon > 180 days" AS what, count(name) AS number UNION ALL
MATCH (u:User {enabled: false})                       WHERE toLower(u.name) ENDS WITH $domain AND u.pwdlastset > 0 AND u.lastlogon > 0 WITH u.name AS name, u.description AS description, u.enabled AS enabled, datetime({ epochSeconds:toInteger(u.pwdlastset) }) AS pwdlastset, duration.inDays(datetime({ epochSeconds:toInteger(u.pwdlastset) }), date()).days AS days_since_pwdlastset, datetime({ epochSeconds:toInteger(u.lastlogon) }) AS lastlogon, duration.inDays(datetime({ epochSeconds:toInteger(u.lastlogon) }), date()).days AS days_since_lastlogon WHERE days_since_pwdlastset > 90 AND days_since_lastlogon < 7 RETURN "Disabled Users pwdlastset > 90 days and lastlogon < 7 days" AS what, count(name) AS number UNION ALL
MATCH (u:User {enabled: false})                       WHERE toLower(u.name) ENDS WITH $domain AND u.pwdlastset > 0 AND u.lastlogon > 0 WITH u.name AS name, u.description AS description, u.enabled AS enabled, datetime({ epochSeconds:toInteger(u.pwdlastset) }) AS pwdlastset, duration.inDays(datetime({ epochSeconds:toInteger(u.pwdlastset) }), date()).days AS days_since_pwdlastset WHERE days_since_pwdlastset > 90 RETURN "Disabled Users pwdlastset > 90 days" AS what, count(name) AS number UNION ALL
MATCH (u:User {enabled: false})                       WHERE toLower(u.name) ENDS WITH $domain AND u.pwdlastset > 0 AND u.lastlogon > 0 WITH u.name AS name, u.description AS description, u.enabled AS enabled, datetime({ epochSeconds:toInteger(u.lastlogon) }) AS lastlogon, duration.inDays(datetime({ epochSeconds:toInteger(u.lastlogon) }), date()).days AS days_since_lastlogon WHERE days_since_lastlogon > 90 RETURN "Disabled Users lastlogon > 180 days" AS what, count(name) AS number UNION ALL
MATCH (u:Computer)                                    WHERE toLower(u.name) ENDS WITH $domain RETURN "Computers in total" AS what, count(u) AS number UNION ALL
MATCH (u:Group)                                       WHERE toLower(u.name) ENDS WITH $domain RETURN "Groups in total" AS what, count(u) AS number UNION ALL
MATCH (u:Domain)                                      WHERE toLower(u.name) ENDS WITH $domain RETURN "Domains in total" AS what, count(u) AS number UNION ALL
MATCH (u:OU)                                          WHERE toLower(u.name) ENDS WITH $domain RETURN "OUs in total" AS what, count(u) AS number UNION ALL
MATCH (u:GPO)                                         WHERE toLower(u.name) ENDS WITH $domain RETURN "GPOs in total" AS what, count(u) AS number UNION ALL
MATCH (u {admincount: True})                          WHERE toLower(u.name) ENDS WITH $domain RETURN "adminCount=1" AS what, count(u) AS number UNION ALL
MATCH (u)                                             WHERE toLower(u.name) ENDS WITH $domain AND u.userpassword =~ ".+" RETURN "userPassword Not Empty" AS what, count(u) AS number UNION ALL
MATCH (u:Computer {unconstraineddelegation: True})-[:MemberOf]->(g:Group) WHERE toLower(u.name) ENDS WITH $domain AND (NOT g.objectid ENDS WITH "-516") AND (NOT u.distinguishedname CONTAINS "Domain Controllers") RETURN "Unconstrained Delegation Computers" AS what, count(u) AS number UNION ALL
MATCH (u {owned: true})                               WHERE toLower(u.name) ENDS WITH $domain RETURN "Owned Principals" AS what, count(u) AS number UNION ALL
MATCH (u {highvalue: true})                           WHERE toLower(u.name) ENDS WITH $domain RETURN "High Value" AS what, count(u) AS number

[mgeeky]

Awesome contribution @AdrianVollmer - thank you! :-)

I considered the use of Cypher variables before but the drawback I saw was that, the query utilising them might be unusable from Neo4j Web UI. That's why I opted for a tradeoff involving Find&Replace annoyance.

With that said, what do you think about putting two separately designed queries next to each other, giving auditor's choice depending on his use case?

Regards,
M.

[AdrianVollmer]

In the web UI you can set parameters with this command before executing the query:

:param domain => 'mydomain.local'

See https://neo4j.com/docs/cypher-manual/current/syntax/parameters/

I mean, you need to find and replace the domain name anyway. Not many domains are named contoso.local ;) might as well replace $domain instead of contoso.local.

I leave it up to you, maybe try it out a few times.

[mgeeky]

Didn't know it's possible to define params in web UI, that might in fact be far easier approach!

Looks cool to me. I'm gonna test it out in a spare minute and add it to the collection.

Thank you @AdrianVollmer - really appreciate it :-)