Extract count failed login with only an authentication method

[marco_massari11]

Hi,

I have some syslog cisco. In this log I have login success and failed (field1), the authentication method(field2) and some mac address (field3). I need to create a dashboard that contains only the mac address that have only login failed with authentication method equal to ethernet, so they can't have failed login (or success) with authentication method wireless. Then in another dashboard I need to put the mac address that have login failed with authentication method equal to ethernet and at least one attempt (success or failed) with wireless. I don't know if the problem is clear. I have a query like this:

index=.....................
| stats values(field1) as status by field3 
| where mvcount(status)=1 and status="failed"
| dedup field3
| stats count 

This query should take only mac address which have only failed login, I don't know if could help.

Thanks in advance!!

[to4kawa]

sample:

| makeresults count=1000
| eval mac_address=(random() % 3).(random() % 3).(random() % 3)
| eval status=mvindex(split("success,failed",","),random() % 2)
| eval method=mvindex(split("eth,wifi",","),random() % 2)

| stats count by mac_address status method
| eval method_status=method."_".status
| xyseries mac_address method_status count

index=.....................
| stats  count by field3 field2 field1
| eval field2_field1=field2."_".field1
| xyseries field3 field2_field1 count




and please extract the necessary information.

[marco_massari11]

@to4kawa  sorry but I do not understand what I should wirte in my query first.

index=......

| eval mac_address=(random() % 3).(random() % 3).(random() % 3)
| eval status=mvindex(split("success,failed",","),random() % 2)
| eval method=mvindex(split("eth,wifi",","),random() % 2)

| stats count by mac_address status method
| eval method_status=method."_".status
| xyseries mac_address method_status count

like this?

[to4kawa]

no, 
Sample query can only run itself. Do not append with your query.

https://docs.splunk.com/Documentation/Splunk/8.0.1/SearchReference/Makeresults

[marco_massari11]

@to4kawa  I think your solution is what I need, I really appreciate your help. So If I want to show  the mac address that have only failed attempt whit ethernet I can filter like:

index="cisco" hostname=* mac=* (status=success OR status=failed) (method=eth OR method=wifi)
| eval site=substr(NetworkDeviceName,1,7) + substr(NetworkDeviceName, -4)
| stats count by mac  status method
| eval method_status=method."_".status 
| xyseries  mac_status_method count
| fillnull value=NULL "eth_failed" "eth_success" "wifi__failed" "wifi_success"
| search "eth_failed">=1 AND "eth_success"="NULL" AND "wifi__failed"="NULL" AND "wifi_success"="NULL"

Have you any suggestion to show the count of mac by site (second line of the query) in a column chart ?

[marco_massari11]

@to4kawa  Sorry I wrote the query wrong, now it's working. In the results I have something like this:

Mac        Failed/eth               Failed/wifi           Passed/eth               Passed/wifi 

and for each mac I have the count for each column, the count is the number of attempts?

[marco_massari11]

@to4kawa I tried this one 

index=.....................
| stats  count by field3 field2 field1
| eval field2_field1=field2."_".field1
| xyseries field3 field2_field1 count

but I have not results

[to4kawa]

yes, this is only failed attempts. 
I don't know your log. so I can't make the query.

[marco_massari11]

@to4kawa  Ok so in another dashboard I need to put the mac address that have login failed with authentication method equal to ethernet and at least one attempt (success or failed) with wireless. I don't know if the problem is clear but In this second dashboard I should not have the mac address in the first dashboard. Have you some ideas?

[to4kawa]

index=.....................
| stats dc(field1) as status_check values(field1) as status by field3 field2
| where status_check=1 AND  status="failed"

what's this result?

[marco_massari11]

@to4kawa  With your query I have the list of mac address with authentication method equal to ethernet, status check=1 and status=failed.

Is this te list of mac address that have only failed attempts with Ethernet?

I have also different sites, how can I group the mac address? Something like stats count by site? but where in the query?

Thank you so much for your help!