topic Re: Add daily stats to Search in Splunk Enterprise

Add daily stats to Search

Madmax — Fri, 03 Nov 2023 14:09:03 GMT

I was able to find this search that gives me the number of users(IONS) who disconnected 10 or more times however it gives me the total based on time. I would like to display a daily number for 30 days in a line chart. For example Monday there were 10 users who disconnected over 10 time and so on for the rest of week. I can't seem to get the timechart to work with this:

index=gbts-vconnection sourcetype=VMWareVDM_debug "onEvent: DISCONNECTED" (host=Host1 OR host=host2) | rex field=_raw "(?ms)^(?:[^:\\n]*:){5}(?P<IONS>[^;]+)(?:[^:\\n]*:){8}(?P<Device>[^;]+)(?:[^;\\n]*;){4}\\w+:(?P<VDI>\\w+)" offset_field=_extracted_fields_bounds | stats count by Device IONS | where count >= 10 | appendpipe [|stats count as IONS | eval Device="Total"]

Re: Add daily stats to Search

FelixLeh — Fri, 03 Nov 2023 14:47:14 GMT

If I understand you correctly the query should work like this:

index=gbts-vconnection sourcetype=VMWareVDM_debug "onEvent: DISCONNECTED" (host=Host1 OR host=host2) | rex field=_raw "(?ms)^(?:[^:\\n]*:){5}(?P<IONS>[^;]+)(?:[^:\\n]*:){8}(?P<Device>[^;]+)(?:[^;\\n]*;){4}\\w+:(?P<VDI>\\w+)" offset_field=_extracted_fields_bounds | eventstats count as failed_count by Device IONS | where failed_count>=10 | timechart dc(IONS) as IONS span=1d

This will show you the amount of user with more than 10 failed logons on each day.

Re: Add daily stats to Search

Madmax — Fri, 03 Nov 2023 17:41:00 GMT

That's exactly what I needed!!! Thank you very much.

Re: Add daily stats to Search

FelixLeh — Mon, 06 Nov 2023 08:28:12 GMT

I'm glad I was able to help!

Re: Add daily stats to Search

Madmax — Mon, 06 Nov 2023 16:29:23 GMT

I spoke too soon. It appears that the numbers are not accurate. It shows the proper number if I set the time picker to last 24 hours but once I select last 30 days the number for yesterday increase by hundreds.

Re: Add daily stats to Search

FelixLeh — Tue, 07 Nov 2023 08:08:56 GMT

My bad. I forgot to add a time variable to the eventstats. By disregarding time the query checks whether a user has more than 10 failed logins over the entire search span. If your data already has a date stamp then you can use that. Alternatively you could adjust the query like this:

index=gbts-vconnection sourcetype=VMWareVDM_debug "onEvent: DISCONNECTED" (host=Host1 OR host=host2) | rex field=_raw "(?ms)^(?:[^:\\n]*:){5}(?P<IONS>[^;]+)(?:[^:\\n]*:){8}(?P<Device>[^;]+)(?:[^;\\n]*;){4}\\w+:(?P<VDI>\\w+)" offset_field=_extracted_fields_bounds | eval temp_date = strftime(_time, "%Y-%m-%d") | eventstats count as failed_count by IONS,temp_date | where failed_count>=10 | timechart dc(IONS) as IONS span=1d

Re: Add daily stats to Search

Madmax — Tue, 07 Nov 2023 14:18:02 GMT

That worked like a charm!! Thanks again!