Append command not showing full results when run with two datamodels.

[renjujacob88]

HI Splunkers,

I'm using append command to combine the results of two datamodels over a period of a time but I'm unable to fetch complete results. When the query is run individually im getting complete results but that same results im not seeing when combined with append command.

Do we have any workaround on this?

Below is the query which im running on splunk

| tstats allow_old_summaries=t count from datamodel=Palo_Url_Filtering where Palo_Url_Filtering.dest_hostname!="*explicit.bing.net" groupby _time Palo_Url_Filtering.client_ip Palo_Url_Filtering.dest_hostname Palo_Url_Filtering.category Palo_Url_Filtering.action |search [| inputlookup topDomainsfinal.csv | rename fqdn as Palo_Url_Filtering.dest_hostname | fields Palo_Url_Filtering.dest_hostname ] |stats count sum(count) as Total_request list(count) as Individual_domain_request_count list(_time) as time values(Palo_Url_Filtering.category) as Palo_Url_Filtering.category values(Palo_Url_Filtering.action) as Palo_Url_Filtering.action by Palo_Url_Filtering.client_ip Palo_Url_Filtering.dest_hostname | convert ctime(time) | rename Palo_Url_Filtering.dest_hostname as fqdn | lookup topDomainsfinal.csv fqdn as fqdn Output category vtratio | where Total_request >=2 | fields Palo_Url_Filtering.client_ip fqdn Total_request Individual_domain_request_count time Palo_Url_Filtering.category Palo_Url_Filtering.action category vtratio | append [| tstats allow_old_summaries=t count from datamodel=DNS_Internal where DNS_Query.domain!="*explicit.bing.net" groupby _time DNS_Query.src_ip DNS_Query.domain | search [| inputlookup topDomainsfinal.csv | rename fqdn as DNS_Query.domain | fields DNS_Query.domain ] |stats count sum(count) as Total_request list(count) as Individual_domain_request_count list(_time) as time values(category) as category by DNS_Query.src_ip DNS_Query.domain | convert ctime(time) | rename DNS_Query.domain as fqdn | lookup topDomainsfinal.csv fqdn as fqdn Output category vtratio | where Total_request >=2 | fields DNS_Query.src_ip fqdn Total_request Individual_domain_request_count time category vtratio] |

[logloganathan]

Could you please use the below query

| tstats allow_old_summaries=t count from datamodel=Palo_Url_Filtering where Palo_Url_Filtering.dest_hostname!="*explicit.bing.net" groupby _time Palo_Url_Filtering.client_ip Palo_Url_Filtering.dest_hostname Palo_Url_Filtering.category Palo_Url_Filtering.action |search [| inputlookup topDomainsfinal.csv | rename fqdn as Palo_Url_Filtering.dest_hostname | fields Palo_Url_Filtering.dest_hostname ] |stats count sum(count) as Total_request list(count) as Individual_domain_request_count list(_time) as time values(Palo_Url_Filtering.category) as Palo_Url_Filtering.category values(Palo_Url_Filtering.action) as Palo_Url_Filtering.action by Palo_Url_Filtering.client_ip Palo_Url_Filtering.dest_hostname | convert ctime(time) | rename Palo_Url_Filtering.dest_hostname as fqdn | lookup topDomainsfinal.csv fqdn as fqdn Output category vtratio | where Total_request >=2 | fields Palo_Url_Filtering.client_ip fqdn Total_request Individual_domain_request_count time Palo_Url_Filtering.category Palo_Url_Filtering.action category vtratio

| Join time
[ search | tstats allow_old_summaries=t count from datamodel=DNS_Internal where DNS_Query.domain!="*explicit.bing.net" groupby _time DNS_Query.src_ip DNS_Query.domain | search [| inputlookup topDomainsfinal.csv | rename fqdn as DNS_Query.domain | fields DNS_Query.domain ] |stats count sum(count) as Total_request list(count) as Individual_domain_request_count list(_time) as time values(category) as category by DNS_Query.src_ip DNS_Query.domain | convert ctime(time) | rename DNS_Query.domain as fqdn | lookup topDomainsfinal.csv fqdn as fqdn Output category vtratio | where Total_request >=2 | fields DNS_Query.src_ip fqdn Total_request Individual_domain_request_count time category vtratio] |

[renjujacob88]

The solution what i was looking for is to append the datamodel results. Based on the query provided , the join command is used to used to combine the subsearch with the result of the main search . The common field is 'time' which is again not a good sign to append the results of the two datamodels. When joining the subsearch and if all results are needed , it always good approach to use type=left.

[p_gurav]

Can you try to edit below parameters in limits.conf:

list_maxsize 
maxresultrows 
maxvalues
maxvaluesize 

Refer below docs:
http://docs.splunk.com/Documentation/Splunk/latest/Admin/Limitsconf?utm_source=answers&utm_medium=in...

[renjujacob88]

@p_gurav
Thanks for the reply. I dont have access to the limits.conf . Apart from that Is there any work around which can be done on the SPlunk enterprise console.

[damien_chillet]

If the subsearch in your append command is returning a lot of results, it may get truncated.
In that case you would see a message in the Job dropdown menu (bottom right of search SPL), can you confirm if that's the case?

[renjujacob88]

@damien_chillet: Thanks for the quick response. Some times i could see the message like "'stats' command: limit for values of field 'xxx' reached. Some values may have been truncated or ignored." . There are times the job button will glow green but wont show me the complete results.

What could be possible workaround on this

[damien_chillet]

You can try play with settings in limits.conf like @p_gurav wrote below.
However best solution is to try re-engineer your search to work with current settings (if that's possible).
I would like to help more but it's difficult without knowing the use case and having data sample available.