topic Re: extract max count value in Dashboards & Visualizations

extract max count value

marco_massari11 — Mon, 23 Nov 2020 13:47:48 GMT

Hi,

I have thousands of csv file on my splunk by monitoring a local share. Each day the folder is replicated by new csv files (current day).The csv file are about switches and are all differents and contain for each one some informations about their interfaces (ip, interface name...). In my query I filter for some fileds to include only interfaces that I need. Ip and Interface name should be unique, so I think I should dedup like | dedup IP, Interface. Then I want to extract for each month the max count of those filtered Interfaces. My query is like this but it's incomplete:

index=appliance sourcetype=new field1=........ field2=........ field3=........

| dedup Ip, Interface (I don't know if is correct and if is what I need)

| ...?

Re: extract max count value

richgalloway — Mon, 23 Nov 2020 14:15:01 GMT

You say the query is incomplete, but don't say what is missing.

The dedup command takes the first event for each unique set of Ip and Interface values. It may be ignoring the events that contain the max value you seek. If you will be using the stats command to find a maximum value then using dedup is not only unneeded, but counter-productive.

Re: extract max count value

marco_massari11 — Mon, 23 Nov 2020 14:55:43 GMT

The query is incomplete beacuse I don't know how to proceed. Yes with dedup I lose old values, So how can I fix this?

Re: extract max count value

richgalloway — Mon, 23 Nov 2020 18:21:05 GMT

What are the desired results? You say you want a max count so have you looked at the stats command?

Re: extract max count value

marco_massari11 — Tue, 24 Nov 2020 09:29:12 GMT

I want the max count of distinct Interfaces for each month. I tried sometthing like this:

index=network_appliance Hostname=** (sourcetype="old" OR sourcetype="new")
| timechart max(Interface) as Total_max span=1mon

But I have not results

Re: extract max count value

richgalloway — Tue, 24 Nov 2020 14:28:05 GMT

Is this what you need?

index=network_appliance Hostname=** (sourcetype="old" OR sourcetype="new") | bin span=1mon _time | stats dc(Interface) as if_count by _time | timechart span=1mon max(if_count) as Total_max

Re: extract max count value

marco_massari11 — Tue, 24 Nov 2020 14:43:05 GMT

@richgalloway yes but the counts are so small. I should have around 28k interfaces but the result is 6k

Re: extract max count value

richgalloway — Tue, 24 Nov 2020 14:50:32 GMT

If you have 28,000 distinct interfaces, but Splunk finds only 6,000 then perhaps something is wrong with the data. How are you arriving at the 28k figure? Are all 28k reporting to Splunk consistently?

Re: extract max count value

marco_massari11 — Tue, 24 Nov 2020 14:55:11 GMT

Yes beacuse I have a total count query which returns number of interfaces. Each Interface have an IP, so the couple Interface-IP is unique.

index=network_appliance Hostname=* (sourcetype="old" OR sourcetype="new")
| dedup Interface IP
| stats count(Interface)