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

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

asdusert — Sun, 14 Oct 2018 02:58:39 GMT

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 ?

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

renjith_nair — Sun, 14 Oct 2018 04:39:47 GMT

@asdusert,

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

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

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

asdusert — Sun, 14 Oct 2018 09:40:13 GMT

@renjith.nair Thanks so much . it works.

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

renjith_nair — Sun, 14 Oct 2018 11:40:40 GMT

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