Why can't results can be shown when calculating the difference between two columns from two searches?

[asdusert]

Here is my query :

index="basicdataapi" source="/data/api-process/logs/equitydata-rawdata-producer/application.log" 
service_name="equitydata-rawdata-producer"
 host="daasynprdbd6012" CountFlag="true" PackageDataType |stats count by PackageDataType | append[search  
 index=datasvc source="us-east-1:/aws/lambda/ged-equitydata-rawdata-consumer*" 
 service_name="equitydata-rawdata-consumer" CountFlag="true" 
 PackageDataType |stats count by PackageDataType ]|**table DataPackageType num |streamstats max(num) as max min(num) as min by PackageDataType | eval diff=max-min | dedup DataPackageType | table DataPackageType max min diff.

When I run the above query which have been marked as "Strong" font, I can get a result like this:
PackageDataType num
CO 319
SO 420
CO 319
SO 420

But I want to count the difference between same "PackageDataType", like below format:
PackageDataType max min diff
CO 319 319 0
SO 420 420 0

So I added "streamstats.." to count it . But after I added it , no result can be shown. Is there anything wrong ?

[renjith_nair · Accepted solution]

@asdusert,

From your search, there are possibly two things you might need to change to get the desired result

1. In your original search you are using field name PackageDataType but in the table command next to it, you are using DataPackageType which are different field names for splunk and that's the reason you are not getting the result.

2. streamstats gives you streaming max or min. ie.when the streamstats look at the first event, it only knows CO 319 and hence it would result 319 as max and min for CO even though the second CO has a different count say 219. You may test it by running

   index=_*|stats count by sourcetype ,index|fields - sourcetype|streamstats max(count) as max,min(count) as min by index

So try stats or eventstats for the final calculation.

index="basicdataapi" source="/data/api-process/logs/equitydata-rawdata-producer/application.log" 
service_name="equitydata-rawdata-producer"
host="daasynprdbd6012" CountFlag="true" PackageDataType |stats count by PackageDataType 
| append[search index=datasvc source="us-east-1:/aws/lambda/ged-equitydata-rawdata-consumer*" 
service_name="equitydata-rawdata-consumer" CountFlag="true" 
|stats count by PackageDataType ]
|stats max(num) as max min(num) as min by PackageDataType | eval diff=max-min

To your original search, just add

 |stats max(num) as max min(num) as min by PackageDataType | eval diff=max-min

[asdusert]

@renjith.nair Thanks so much . it works.

[renjith_nair]

@asdusert, glad that it worked. Kindly accept it as answer to close this thread. Thanks