cypher-queries.in.sample

// Queries:
//   ========== BloodHound pre-built ==========
//   * Find all Domain Admins
//   * Find Shortest Paths to Domain Admins (time consuming) 
//   * Find Principals with DCSync Rights
//   * Users with Foreign Domain Group Membership
//   * Groups with Foreign Domain Group Membership
//   * Map Domain Trusts
//   * Shortest Paths to Unconstrained Delegation Systems (time consuming)
//   * Shortest Paths to High Value Targets (time consuming)
//   * Find Computers where Domain Users are Local Admin
//   * Find Computers where Domain Users can read LAPS passwords
//   * Shortest Paths from Domain Users to High Value Targets
//   * Find Workstations where Domain Users can RDP
//   * Find Servers where Domain Users can RDP
//   * Find Dangerous Rights for Domain Users Groups
//   * Find Kerberoastable Members of High Value Groups
//   * List all Kerberoastable Accounts
//   * Find Kerberoastable Users with most privileges
//   * Find Domain Admin Logons to non-Domain Controllers
//   * Find Computers with Unsupported Operating Systems - Windows 2000
//   * Find Computers with Unsupported Operating Systems - Windows Server 2003
//   * Find Computers with Unsupported Operating Systems - Windows Server 2008
//   * Find Computers with Unsupported Operating Systems - Windows XP
//   * Find Computers with Unsupported Operating Systems - Windows Vista
//   * Find Computers with Unsupported Operating Systems - Windows 7
//   * Find AS-REP Roastable Users (DontReqPreAuth)
//
//   ========== Hausec.com ========== 
//   * Find All Users with an SPN/Find all Kerberoastable Users with passwords last set > 5 years ago
//   * Find constrained delegation
//   * Principals with control of a “high value” asset where the principal itself does not belong to a “high value” group
//   * Find any computer that is NOT a domain controller and it is trusted to perform unconstrained delegation
//   * Find every user object where the “userpassword” attribute is populated
//   * Find users that have never logged on and account is still active - Website Users
//   * Find users that have never logged on and account is still active - External Users
//   * Find users that have never logged on and account is still active - Telephone
//   * Find users that have never logged on and account is still active - Rest entities



//   ========== BloodHound pre-built ==========

// Find all Domain Admins
CALL apoc.export.json.query(
    "MATCH p=(n:Group)<-[:MemberOf*1..]-(m)
     WHERE n.objectid =~ $name
     RETURN apoc.map.flatten(m) as user
     ",
    "/tmp/bh_cypher_out/domain-admins.json",
    {params:{name:"(?i)S-1-5-.*-512"}}
);

// Find Shortest Paths to Domain Admins
//CALL apoc.export.json.query(
//    "MATCH p=shortestPath((n)-[:MemberOf|HasSession|AdminTo|AllExtendedRights|AddMember|ForceChangePassword|GenericAll|GenericWrite|Owns|WriteDacl|WriteOwner|CanRDP|ExecuteDCOM|AllowedToDelegate|ReadLAPSPassword|Contains|GpLink|AddAllowedToAct|AllowedToAct|SQLAdmin|ReadGMSAPassword|HasSIDHistory|CanPSRemote|AZAddMembers|AZContains|AZContributor|AZGetCertificates|AZGetKeys|AZGetSecrets|AZGlobalAdmin|AZOwns|AZPrivilegedRoleAdmin|AZResetPassword|AZUserAccessAdministrator|AZAppAdmin|AZCloudAppAdmin|AZRunsAs|AZKeyVaultContributor*1..]->(m:Group {name:\"DOMAIN ADMINS@EXAMPLE.COM\"})) 
//     WHERE NOT n=m 
//     RETURN p",
//    "/tmp/bh_cypher_out/sp-to-domain-admins.json",
//    {params:{}}
//);

// Find Principals with DCSync Rights
CALL apoc.export.json.query(
    "MATCH (n1)-[:MemberOf|GetChanges*1..]->(u:Domain {name: $domain_name})
     WITH n1,u MATCH (n1)-[:MemberOf|GetChangesAll*1..]->(u) 
     WITH n1,u MATCH p = (n1)-[:MemberOf|GetChanges|GetChangesAll*1..]->(u)
     RETURN apoc.map.flatten(n1)",
    "/tmp/bh_cypher_out/principals-with-dsync-rights.json",
    {params:{
        domain_name: "EXAMPLE.COM"
    }}
);

// Users with Foreign Domain Group Membership
CALL apoc.export.json.query(
    "MATCH p=(n:User)-[:MemberOf]->(m:Group)
     WHERE n.domain=$domain_name AND m.domain<>n.domain
     RETURN p",
    "/tmp/bh_cypher_out/users-with-foreign-domain-group-membership.json",
    {params:{
        domain_name: "EXAMPLE.COM"
    }}
);

// Groups with Foreign Domain Group Membership
CALL apoc.export.json.query(
    "MATCH p=(n:Group {domain:$domain_name})-[:MemberOf]->(m:Group)
     WHERE m.domain<>n.domain AND n.name<>m.name
     RETURN p",
    "/tmp/bh_cypher_out/groups-with-foreign-domain-group-membership.json",
    {params:{
        domain_name: "EXAMPLE.COM"
    }}
);

// Map Domain Trusts
CALL apoc.export.json.query(
    "MATCH p=(n:Domain)-->(m:Domain)
     RETURN p",
    "/tmp/bh_cypher_out/map-domain-trusts.json",
    {params:{}}
);

// Shortest Paths to Unconstrained Delegation Systems
//CALL apoc.export.json.query(
//    "MATCH (n) 
//     MATCH p=shortestPath((n)-[:MemberOf|HasSession|AdminTo|AllExtendedRights|AddMember|ForceChangePassword|GenericAll|GenericWrite|Owns|WriteDacl|WriteOwner|CanRDP|ExecuteDCOM|AllowedToDelegate|ReadLAPSPassword|Contains|GpLink|AddAllowedToAct|AllowedToAct|SQLAdmin|ReadGMSAPassword|HasSIDHistory|CanPSRemote|AZAddMembers|AZContains|AZContributor|AZGetCertificates|AZGetKeys|AZGetSecrets|AZGlobalAdmin|AZOwns|AZPrivilegedRoleAdmin|AZResetPassword|AZUserAccessAdministrator|AZAppAdmin|AZCloudAppAdmin|AZRunsAs|AZKeyVaultContributor*1..]->(m:Computer {unconstraineddelegation: true})) 
//     WHERE NOT n=m 
//     RETURN p",
//    "/tmp/bh_cypher_out/sp-to-unconstrained-delegation-systems.json",
//   {params:{
//        domain_name: "EXAMPLE.COM"
//    }}
//);

// Shortest Paths to High Value Targets
//CALL apoc.export.json.query(
//    "MATCH p=shortestPath((n)-[*1..]->(m {highvalue:true})) 
//     WHERE m.domain=$domain AND m<>n 
//     RETURN p",
//    "/tmp/bh_cypher_out/sp-to-high-value-targets.json",
//    {params:{
//        domain: "EXAMPLE.COM"
//    }}
//);

// Find Computers where Domain Users are Local Admin
CALL apoc.export.json.query(
    "MATCH p=(m:Group {name:$name})-[:AdminTo]->(n:Computer) 
     RETURN p",
    "/tmp/bh_cypher_out/computers-where-domain-users-are-local-admin.json",
    {params:{
        name: "DOMAIN USERS@EXAMPLE.COM"
    }}
);

// Find Computers where Domain Users can read LAPS passwords
CALL apoc.export.json.query(
    "MATCH p=(Group {name:$name})-[:MemberOf*0..]->(g:Group)-[:AllExtendedRights|ReadLAPSPassword]->(n:Computer)
     RETURN p",
    "/tmp/bh_cypher_out/computers-where-domain-users-car-read-laps-passwords.json",
    {params:{
        name: "DOMAIN USERS@EXAMPLE.COM"
    }}
);

// Shortest Paths from Domain Users to High Value Targets
CALL apoc.export.json.query(
    "MATCH p=shortestPath((g:Group {name:$name})-[*1..]->(n {highvalue:true})) 
     WHERE g.objectid ENDS WITH \"-513\" AND g<>n 
     RETURN p",
    "/tmp/bh_cypher_out/sp-domain-users-to-high-value-targets.json",
    {params:{
        name: "DOMAIN USERS@EXAMPLE.COM"
    }}
);

// Find Workstations where Domain Users can RDP
CALL apoc.export.json.query(
    "MATCH p=(g:Group {name:$name})-[:CanRDP]->(c:Computer) 
     WHERE NOT c.operatingsystem CONTAINS \"Server\" 
     RETURN p",
    "/tmp/bh_cypher_out/workstations-where-users-can-rdp.json",
    {params:{
        name: "DOMAIN USERS@EXAMPLE.COM"
    }}
);

// Find Servers where Domain Users can RDP
CALL apoc.export.json.query(
    "MATCH p=(g:Group {name:$name})-[:CanRDP]->(c:Computer) 
     WHERE c.operatingsystem CONTAINS \"Server\" 
     RETURN p",
    "/tmp/bh_cypher_out/servers-where-users-can-rdp.json",
    {params:{
        name: "DOMAIN USERS@EXAMPLE.COM"
    }}
);

// Find Dangerous Rights for Domain Users Groups
CALL apoc.export.json.query(
    "MATCH p=(m:Group)-[:Owns|WriteDacl|GenericAll|WriteOwner|ExecuteDCOM|GenericWrite|AllowedToDelegate|ForceChangePassword]->(n:Computer) 
     WHERE m.objectid ENDS WITH \"-513\" 
     RETURN p",
    "/tmp/bh_cypher_out/dangerous-rights-for-domain-users.json",
    {params:{}}
);

// Find Kerberoastable Members of High Value Groups
CALL apoc.export.json.query(
    "MATCH p=shortestPath((n:User)-[:MemberOf]->(g:Group)) 
     WHERE g.highvalue=true AND n.hasspn=true 
     RETURN p",
    "/tmp/bh_cypher_out/kerberoastable-members-of-high-value-groups.json",
    {params:{
        name: "DOMAIN USERS@EXAMPLE.COM"
    }}
);

// List all Kerberoastable Accounts
CALL apoc.export.json.query(
    "MATCH (n:User)WHERE n.hasspn=true 
     RETURN apoc.map.flatten(n)",
    "/tmp/bh_cypher_out/kerberoastable-accounts.json",
    {params:{}}
);

// Find Kerberoastable Users with most privileges
CALL apoc.export.json.query(
    "MATCH (u:User {hasspn:true}) 
     OPTIONAL MATCH (u)-[:AdminTo]->(c1:Computer)
     OPTIONAL MATCH (u)-[:MemberOf*1..]->(:Group)-[:AdminTo]->(c2:Computer) 
     WITH u,COLLECT(c1) + COLLECT(c2) AS tempVar UNWIND tempVar AS comps 
     RETURN {name: u.name, computercount: COUNT(DISTINCT(comps))} as user
     ORDER BY user.computercount DESC",
    "/tmp/bh_cypher_out/kerberoastable-accounts-with-most-privileges.json",
    {params:{}}
);

// Find Domain Admin Logons to non-Domain Controllers
CALL apoc.export.json.query(
    "MATCH (dc)-[r:MemberOf*0..]->(g:Group) 
     WHERE g.objectid ENDS WITH '-516' WITH COLLECT(dc) AS exclude 
     MATCH p = (c:Computer)-[n:HasSession]->(u:User)-[r2:MemberOf*1..]->(g:Group) 
     WHERE g.objectid ENDS WITH '-512' AND NOT c IN exclude 
     RETURN RETURN {name: u.name, computer: c.name,path: p} as user",
    "/tmp/bh_cypher_out/domain-admin-logons-to-non-domain-controllers.json",
    {params:{}}
);

// Find Computers with Unsupported Operating Systems - Windows 2000
CALL apoc.export.json.query(
    "MATCH (n:Computer)
     WHERE n.operatingsystem =~ $operating_system
     RETURN apoc.map.flatten(n)",
    "/tmp/bh_cypher_out/computers-with-unsupported-os-windows-2000.json",
    {params:{
        operating_system: "Windows 2000.*"
    }}
);

// Find Computers with Unsupported Operating Systems - Windows Server 2003
CALL apoc.export.json.query(
    "MATCH (n:Computer)
     WHERE n.operatingsystem =~ $operating_system
     RETURN apoc.map.flatten(n)",
    "/tmp/bh_cypher_out/computers-with-unsupported-os-windows-server-2003.json",
    {params:{
        operating_system: "Windows Server 2003.*"
    }}
);

// Find Computers with Unsupported Operating Systems - Windows Server 2008
CALL apoc.export.json.query(
    "MATCH (n:Computer)
     WHERE n.operatingsystem =~ $operating_system
     RETURN apoc.map.flatten(n)",
    "/tmp/bh_cypher_out/computers-with-unsupported-os-windows-server-2008.json",
    {params:{
        operating_system: "Windows Server 2008.*"
    }}
);

// Find Computers with Unsupported Operating Systems - Windows XP
CALL apoc.export.json.query(
    "MATCH (n:Computer)
     WHERE n.operatingsystem =~ $operating_system
     RETURN apoc.map.flatten(n)",
    "/tmp/bh_cypher_out/computers-with-unsupported-os-windows-xp.json",
    {params:{
        operating_system: "Windows XP.*"
    }}
);

// Find Computers with Unsupported Operating Systems - Windows Vista
CALL apoc.export.json.query(
    "MATCH (n:Computer)
     WHERE n.operatingsystem =~ $operating_system
     RETURN apoc.map.flatten(n)",
    "/tmp/bh_cypher_out/computers-with-unsupported-os-windows-vista.json",
    {params:{
        operating_system: "Windows Vista.*"
    }}
);

// Find Computers with Unsupported Operating Systems - Windows 7
CALL apoc.export.json.query(
    "MATCH (n:Computer)
     WHERE n.operatingsystem =~ $operating_system
     RETURN apoc.map.flatten(n)",
    "/tmp/bh_cypher_out/computers-with-unsupported-os-windows-7.json",
    {params:{
        operating_system: "Windows 7.*"
    }}
);

// Find AS-REP Roastable Users (DontReqPreAuth)
CALL apoc.export.json.query(
    "MATCH (u:User {dontreqpreauth: true}) 
     RETURN apoc.map.flatten(u)",
    "/tmp/bh_cypher_out/as-rep-roastable-users.json",
    {params:{}}
);

//   ========== Hausec.com ========== 

// Find All Users with an SPN/Find all Kerberoastable Users with passwords last set > 5 years ago
CALL apoc.export.json.query(
    "MATCH (u:User)
     WHERE u.hasspn=true AND u.pwdlastset < (datetime().epochseconds - (1825 * 86400)) AND NOT u.pwdlastset IN [-1.0, 0.0]
     RETURN {name: u.name, pwdlastset: u.pwdlastset} as user 
     ORDER BY user.pwdlastset",
    "/tmp/bh_cypher_out/kerberoastable-users-with-password-set-more-than-5-years-ago.json",
    {params:{}}
);

// Find constrained delegation
CALL apoc.export.json.query(
    "MATCH (u:User)-[:AllowedToDelegate]->(c:Computer) 
     RETURN {name: u.name, computercount: COUNT(c)} as user
     ORDER BY user.computercount DESC",
    "/tmp/bh_cypher_out/constrained_delegation.json",
    {params:{}}
);

// Principals with control of a “high value” asset where the principal itself does not belong to a “high value” group
CALL apoc.export.json.query(
    "MATCH (n {highvalue:true}) 
     OPTIONAL MATCH (m1)-[{isacl:true}]->(n) 
     WHERE NOT (m1)-[:MemberOf*1..]->(:Group {highvalue:true}) 
     OPTIONAL MATCH (m2)-[:MemberOf*1..]->(:Group)-[{isacl:true}]->(n) 
     WHERE NOT (m2)-[:MemberOf*1..]->(:Group {highvalue:true}) 
     WITH n,COLLECT(distinct(m1.name)) + COLLECT(distinct(m2.name)) AS tempVar 
     UNWIND tempVar AS controllers 
     RETURN {name: n.name,controllers: collect(controllers),controllercount: COUNT(DISTINCT(controllers))} as principal
     ORDER BY principal.controllercount DESC",
    "/tmp/bh_cypher_out/high_val_principals_in_non_high_val_group.json",
    {params:{}}
);

// Find any computer that is NOT a domain controller and it is trusted to perform unconstrained delegation
CALL apoc.export.json.query(
    "MATCH (c1:Computer)-[:MemberOf*1..]->(g:Group) 
     WHERE g.objectid ENDS WITH '-516' 
     WITH COLLECT(c1.name) AS domainControllers 
     MATCH (c2:Computer {unconstraineddelegation:true}) 
     WHERE NOT c2.name IN domainControllers 
     RETURN {name: c2.name, os: c2.operatingsystem} as computer 
     ORDER BY computer.name ASC",
    "/tmp/bh_cypher_out/computers-no-domain-controller-unconstrained-delegation.json",
    {params:{}}
);

// Find every user object where the “userpassword” attribute is populated
CALL apoc.export.json.query(
    "MATCH (u:User) 
     WHERE NOT u.userpassword IS null 
     RETURN {name: u.name, password: u.userpassword} as user",
    "/tmp/bh_cypher_out/users-with-userpassword.json",
    {params:{}}
);

// Find users that have never logged on and account is still active - Website Users 
CALL apoc.export.json.query(
    "MATCH (o:OU)-[:Contains]->(n:User)
     WHERE n.lastlogontimestamp=-1.0 AND n.enabled=TRUE AND o.name=$ou_name
     RETURN {name: n.name, distinguishedname: n.distinguishedname} as user
     ORDER BY user.name",
    "/tmp/bh_cypher_out/active-users-never-logged-in-website.json",
    {params:{ 
        ou_name: "WEBSITE USERS@EXAMPLE.COM"
    }}
);

// Find users that have never logged on and account is still active - External Users 
CALL apoc.export.json.query(
    "MATCH (o:OU)-[:Contains]->(n:User)
     WHERE n.lastlogontimestamp=-1.0 AND n.enabled=TRUE AND o.distinguishedname CONTAINS $ou_distinguised_name
     RETURN {name: n.name, distinguishedname: n.distinguishedname} as user
     ORDER BY user.name",
    "/tmp/bh_cypher_out/active-users-never-logged-in-external-users.json",
    {params:{
        ou_distinguised_name: "OU=Externals"
    }}
);

// Find users that have never logged on and account is still active - Telephone
CALL apoc.export.json.query(
    "MATCH (n:User)
     WHERE n.lastlogontimestamp=-1.0 AND n.enabled=TRUE AND n.name CONTAINS \"TEL\"
     RETURN {name: n.name, distinguishedname: n.distinguishedname} as user
     ORDER BY user.name",
    "/tmp/bh_cypher_out/active-users-never-logged-in-telephone.json",
    {params:{
        ou_distinguised_name: "OU=Externals"
    }}
);

// Find users that have never logged on and account is still active - Rest entities
CALL apoc.export.json.query(
    "MATCH (o:OU)-[:Contains]->(n:User)
     WHERE n.lastlogontimestamp=-1.0 AND n.enabled=TRUE AND NOT n.name  CONTAINS \"TEL\" 
     AND NOT o.distinguishedname CONTAINS $ou_distinguised_name AND NOT o.name=$ou_name
     RETURN {name: n.name, distinguishedname: n.distinguishedname} as user
     ORDER BY user.name",
    "/tmp/bh_cypher_out/active-users-never-logged-in-rest-entities.json",
    {params:{
        ou_name: "WEBSITE USERS@EXAMPLE.COM",
        ou_distinguised_name: "OU=Externals"
    }}
);