Aruba Controller authmgr syslog. How to search each device's successful authentication count by username?

[hpchiu]

Hi,

I have an Aruba Controller SYSLOG example:

Apr  1 11:41:32 192.168.1.254 Apr  1 11:41:28 2015 Aruba3600 authmgr[1753]: <522008>    User Authentication Successful: username=Alberta MAC=9c:f3:87:11:4f:a1 IP=192.168.0.18 role=authenticated VLAN=16 AP=HQ.12.11 SSID=TestSSID AAA profile=Guest-aaa_prof auth method=802.1x auth server=192.168.2.18

Apr  1 11:41:58 192.168.1.254 Apr  1 11:41:53 2015 Aruba3600 authmgr[1753]: <522008>    User Authentication Successful: username=William MAC=a0:a8:cd:de:cf:3e IP=192.168.0.21 role=authenticated VLAN=16 AP=HQ.12.14 SSID=TestSSID AAA profile=Guest-aaa_prof auth method=802.1x auth server=192.168.2.17

Apr  1 11:42:12 192.168.1.254 Apr  1 11:42:08 2015 Aruba3600 authmgr[1753]: <522008>    User Authentication Successful: username=Alberta MAC=54:9f:13:05:a3:4c IP=192.168.0.19 role=authenticated VLAN=16 AP=HQ.12.13 SSID=TestSSID AAA profile=Guest-aaa_prof auth method=802.1x auth server=192.168.2.11

Apr  1 11:42:14 192.168.1.254 Apr  1 11:42:11 2015 Aruba3600 authmgr[1753]: <522008>    User Authentication Successful: username=Sabrina MAC=f0:dc:e2:79:5a:b7 IP=192.168.0.27 role=authenticated VLAN=16 AP=HQ.12.12 SSID=TestSSID AAA profile=Guest-aaa_prof auth method=802.1x auth server=192.168.2.37

Apr  1 11:42:24 192.168.1.254 Apr  1 11:42:21 2015 Aruba3600 authmgr[1753]: <522008>    User Authentication Successful: username=Alberta MAC=9c:f3:87:11:4f:a1 IP=192.168.0.19 role=authenticated VLAN=16 AP=HQ.12.13 SSID=TestSSID AAA profile=Guest-aaa_prof auth method=802.1x auth server=192.168.2.18

---

my search syntax1

source="192.168.1.254" "User Authentication Successful" | stats count by username| sort limit=15 -num(count)

got result :
username　Count

---

William　　 1
Alberta　　 3
Sabrina 　　1

my search syntax2

source="192.168.1.254" "User Authentication Successful" | stats  list(MAC) as "MAC_Adddress" by username

got result :
username　MAC_Address

---

William　　 a0:a8:cd:de:cf:3e
Alberta　　 54:9f:13:05:a3:4c
　　　 　　 9c:f3:87:11:4f:a1
Sabrina 　　f0:dc:e2:79:5a:b7

---

How can I get a result like this:
username　MAC_Address　Count
------------　-------------------　--------
William　　a0:a8:cd:de:cf:3e　1
Alberta　　 54:9f:13:05:a3:4c　1
　　　 　　 9c:f3:87:11:4f:a1　2
Sabrina 　　f0:dc:e2:79:5a:b7　1

I want to know each device's successful authentication count by username. I am trying stuff, but somehow i cant find a way to search in one search two different count values..

[Runals · Accepted solution]

source="192.168.1.254" "User Authentication Successful" | stats values(MAC) as "MAC_Adddress" count by username

Give that a try as a base search. You can do more than one stats function in a stats command. I've also used values here vs list as values will give you a deduplicated list.

I think what you are really after is something like this though. Note this will look good in Splunk but not as much if you export it to PDF (vertical alignment is at the bottom of the cell vs top sadly). If you export it the multi-values are space delimited.

source="192.168.1.254" "User Authentication Successful" | stats count by username MAC | stats sum(count) as total_auth list(MAC) as MAC_Address list(count) as count by username | sort -total_auth

I added the sum bit and sorting to help highlight what I've done. The better way to see this though is to run the search up through just the first stats command, add the second stats command, and then add any sorting. This works because you are listing each field in the order it shows up. If you substitute list for values in the second stats bit each column will be alpha sorted which might throw off which count aligns to which MAC. If you want to introduce a level of sorting in the results you would need to do that between the stats command like this

source="192.168.1.254" "User Authentication Successful" | stats count by username MAC | sort -count | stats sum(count) as total_auth list(MAC) as MAC_Address list(count) as count by username | sort -total_auth

[hpchiu]

Thank you for your prompt reply and solve my problem.

^^
have a nice day.