How do I look up members in an AD group and display which users have not generated a "Success/Fail" VPN event from Cisco ISE ?

[jkujawa]

I am lost on this one. I want to look up members in an AD group and output users who have not generated a success or failure action from Cisco ISE within xx days. Here are two searches I built. Not sure if they can be combined for what I am trying to accomplish.

Queries Active Directory and displays users in the group.

|ldapsearch domain=MYDOMAIN search="(&(objectClass=user)(memberOf=CN=MyGroup,OU=Groups,DC=Mydomain,DC=com))" | table sAMAccountName | rename sAMAccountName as Username | Sort Username

Queries Cisco ISE and displays employees who used the VPN.

sourcetype="cisco:ise:syslog" NetworkDeviceName="MYVPN" action=failure OR action=success PIX7x_Tunnel_Group_Name=MYVPNGroup

Thanks!

[maciep]

If there is a user field in the cisco ise data that would match the user coming from ldap, then I think one way could be to append these searches together with a common field name and use some stats to filter down to the users you want to see.

Not tested at all, but maybe something like this

sourcetype="cisco:ise:syslog" NetworkDeviceName="MYVPN" action=failure OR action=success PIX7x_Tunnel_Group_Name=MYVPNGroup
    | rename user_field as Username
    | eval type = "ise"
    | append [ 
        | ldapsearch domain=MYDOMAIN search="(&(objectClass=user)(memberOf=CN=MyGroup,OU=Groups,DC=Mydomain,DC=com))" 
        | rename sAMAccountName as Username 
        | eval type = "ldap" ]
    | stats dc(type) as count values(type) as types by Username
    | where count=1 AND types="ldap"

[jkujawa]

The relevant Cisco ISE fields are:

• user
• AD_User_Resolved_DNs
• User_Name
• UserName

The one that matched the LDAP query best is "AD_User_Resolved_DNs" as it matches the case format (upper or lower).

So far, I've tried the search string you provided and it is not pulling in the correct data. I see the usernames, but it includes people who logged in to the VPN within the specified time frame from the time picker.

sourcetype="cisco:ise:syslog" NetworkDeviceName="MYVPN" action=failure OR action=success PIX7x_Tunnel_Group_Name=MYVPNGroup  | rename AD_User_Resolved_DNs as Username  | eval type = "ise"          | append [  | ldapsearch domain=MYDOMAIN search="(&(objectClass=user) memberOf=CN=MyGroup,OU=Groups,DC=Mydomain,DC=com))"  | rename sAMAccountName as Username 
 | eval type = "ldap" ] | stats dc(type) as count values(type) as types by Username | where count=1 AND types="ldap"

[maciep]

are the usernames exactly the same? No domains or anything to strip out?

if you remove the where command at the end and maybe sort by username do you see any you would consider duplicates? Or in general do you see data you'd expect - a username, a count and field for types, probably containing one or two entries?