topic Re: Why is stats count producing incorrect results on joined data? in Splunk Search

Why is stats count producing incorrect results on joined data?

ArsenyKapralov — Mon, 28 Sep 2020 17:40:33 GMT

I'm trying to join data from two sourcetypes and make some simple statistics based on joined data. But when I'm using stats I have some incorrect results. Let me explain in details:

In sourcetype "ATM" I have field DEVICE_ID and in sourcetype "Zabbix" I have fields JNAME and AVAILABLE. Fields DEVICE_ID and JNAME contains the same sort of data from two different applications and fields AVAILABLE contain information for which I need some statistics. In sourcetype "ATM" I have events for more DEVICE_ID's than in sourcetype "Zabbix"

Basically my task is to count for how much DEVICE_ID's I don't have information in "Zabbix" every day.
I used following query to get the result:

sourcetype=ATM | eval date=strftime(_time,"%d-%m-%Y") | dedup DEVICE_ID, date
| join type=left DEVICE_ID, data [search sourcetype=Zabbix | eval date=strftime(_time,"%d-%m-%Y") | dedup JNAME, date | rename JNAME AS DEVICE_ID]
| fillnull value="No data" AVAILABLE
| search AVAILABLE="No data"
| stats count(DEVICE_ID) by date

When I'm executing this search for 7 days, I get correct information for latest 5 and fully incorrect for two first. When I executed this search for last 30 days I got correct result for latest 11 days and incorrect for all other. Like this:

date | count
11-09-2014 | 2994
12-09-2014 | 2989
13-09-2014 | 347
14-09-2014 | 328
15-09-2014 | 341
16-09-2014 | 349

Please help me to make this search working properly. Thank you in advance.

P.S. I'm using Splunk Free if it matter

Re: Why is stats count producing incorrect results on joined data?

jrodman — Mon, 28 Sep 2020 17:40:50 GMT

I don't know if this is your problem, but please note that count(DEVICE_ID) does not count the number of results which have the field DEVICE_ID. Instead it counts the number of occurrences (or values, if you prefer) of the field DEVICE_ID, which can disagree in the case of multivalue fields. You may wish to check if you get the same number for |where DEVICE_ID="*" | stats count by date

Re: Why is stats count producing incorrect results on joined data?

HiroshiSatoh — Mon, 28 Sep 2020 17:40:52 GMT

Clerical error?
join type=left DEVICE_ID, data
-> join type=left DEVICE_ID, date

Re: Why is stats count producing incorrect results on joined data?

ArsenyKapralov — Wed, 24 Sep 2014 11:59:41 GMT

jrodman
Thank you for your comment. But |where DEVICE_ID="*" produce no results. Also I'm sure that numbers provided by my search are correct for some dates, so I think this doesn't affect my case

HiroshiSatoh
Yes, just and error. It doesn't affect my case

I also found that subsearch in join was limited by time. When I tried to change limits (in limits.conf) it starts to produce correct results for more dates. So, I think this is the case and I need to understand how I can speedup search and avoid incorrect results in general.

Any ideas?

Re: Why is stats count producing incorrect results on joined data?

wpreston — Mon, 28 Sep 2020 17:40:59 GMT

A couple of things:

Keep in mind that subsearches return a maximum of 10,500 results, so some of your results could be truncated.
You might be able to refactor the search such that a join is not needed, which could greatly increase the efficiency of the search. Would a search like this accomplish what you need?

sourcetype=ATM OR sourcetype=Zabbix
| eval date=strftime(_time,"%d-%m-%Y")
| rename JNAME AS DEVICE_ID
| fillnull value="No data" AVAILABLE
| fields DEVICE_ID sourcetype date
| search AVAILABLE="No data"
| stats count(Device_ID) as DeviceCount dc(sourcetype) as SourceTypeCount by date
| where SourceTypeCount>1

Re: Why is stats count producing incorrect results on joined data?

ArsenyKapralov — Wed, 24 Sep 2014 13:38:10 GMT

Thanks for this data. As I understand when I'm using "join" by default Splunk returns 50k results and I can manage this in limits.conf
I tested your query but I get only zeros in DeviceCount if I'm trying to search without "where" and get "No results found" error if I'm searching with "where" clause. Any ideas how to fix this?

Re: Why is stats count producing incorrect results on joined data?

wpreston — Wed, 24 Sep 2014 14:38:38 GMT

Ah, that's because I didn't include it in my stats command, sorry about that. Here's the edited search:

sourcetype=ATM  OR sourcetype=Zabbix  
| eval date=strftime(_time,"%d-%m-%Y")  
| rename JNAME AS DEVICE_ID  
| fillnull value="No data" AVAILABLE  
| fields DEVICE_ID sourcetype date 
| search AVAILABLE="No data" 
| stats count(Device_ID) as DeviceCount dc(sourcetype) as SourceTypeCount by date DEVICE_ID
| where SourceTypeCount>1

Re: Why is stats count producing incorrect results on joined data?

ArsenyKapralov — Mon, 28 Sep 2020 17:41:54 GMT

Thanks for your help, but it's still not working. I tried to analyze search results without stats and where and found following:
1) after | search AVAILABLE="No data" I have events only of one sourcetype - ATM
2) I see DEVICE_ID field in fields list but when I'm trying to do simple stats | stats count(Device_ID) by date search provides zero results each day

Any ideas why this can happen and what to do?