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..
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
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
Thank you for your prompt reply and solve my problem.
^^
have a nice day.