topic Re: Extract count failed login with only an authentication method in Dashboards & Visualizations

Extract count failed login with only an authentication method

marco_massari11 — Tue, 24 Nov 2020 09:48:47 GMT

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!!

Re: Extract count failed login with only an authentication method

to4kawa — Tue, 24 Nov 2020 09:56:27 GMT

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?

Re: Extract count failed login with only an authentication method

marco_massari11 — Tue, 24 Nov 2020 10:07:18 GMT

@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!

Re: Extract count failed login with only an authentication method

to4kawa — Tue, 24 Nov 2020 10:30:44 GMT

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

Re: Extract count failed login with only an authentication method

marco_massari11 — Tue, 24 Nov 2020 10:38:35 GMT

@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?

Re: Extract count failed login with only an authentication method

to4kawa — Tue, 24 Nov 2020 10:57:43 GMT

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.

Re: Extract count failed login with only an authentication method

marco_massari11 — Tue, 24 Nov 2020 11:13:44 GMT

@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?

Re: Extract count failed login with only an authentication method

to4kawa — Tue, 24 Nov 2020 11:18:49 GMT

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

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

Re: Extract count failed login with only an authentication method

marco_massari11 — Tue, 24 Nov 2020 11:29:26 GMT

@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

Re: Extract count failed login with only an authentication method

marco_massari11 — Tue, 24 Nov 2020 12:11:46 GMT

@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?

Re: Extract count failed login with only an authentication method

marco_massari11 — Wed, 25 Nov 2020 15:13:56 GMT

@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 ?