Splunk Search

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

Path Finder

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 allowoldsummaries=t count from datamodel=PaloUrlFiltering where PaloUrlFiltering.desthostname!="*explicit.bing.net" groupby _time PaloUrlFiltering.clientip PaloUrlFiltering.desthostname PaloUrlFiltering.category PaloUrlFiltering.action |search [| inputlookup topDomainsfinal.csv | rename fqdn as PaloUrlFiltering.desthostname | fields PaloUrlFiltering.desthostname ] |stats count sum(count) as Totalrequest list(count) as Individualdomainrequestcount list(time) as time values(PaloUrlFiltering.category) as PaloUrlFiltering.category values(PaloUrlFiltering.action) as PaloUrlFiltering.action by PaloUrlFiltering.clientip PaloUrlFiltering.desthostname | convert ctime(time) | rename PaloUrlFiltering.desthostname as fqdn | lookup topDomainsfinal.csv fqdn as fqdn Output category vtratio | where Totalrequest >=2 | fields PaloUrlFiltering.clientip fqdn Totalrequest Individualdomainrequestcount time PaloUrlFiltering.category PaloUrlFiltering.action category vtratio | append [| tstats allowoldsummaries=t count from datamodel=DNSInternal where DNSQuery.domain!="*explicit.bing.net" groupby _time DNSQuery.srcip DNSQuery.domain | search [| inputlookup topDomainsfinal.csv | rename fqdn as DNSQuery.domain | fields DNSQuery.domain ] |stats count sum(count) as Totalrequest list(count) as Individualdomainrequestcount list(time) as time values(category) as category by DNSQuery.srcip DNSQuery.domain | convert ctime(time) | rename DNSQuery.domain as fqdn | lookup topDomainsfinal.csv fqdn as fqdn Output category vtratio | where Totalrequest >=2 | fields DNSQuery.srcip fqdn Totalrequest Individualdomainrequestcount time category vtratio] |

0 Karma

Motivator

Could you please use the below query

| tstats allowoldsummaries=t count from datamodel=PaloUrlFiltering where PaloUrlFiltering.desthostname!="*explicit.bing.net" groupby _time PaloUrlFiltering.clientip PaloUrlFiltering.desthostname PaloUrlFiltering.category PaloUrlFiltering.action |search [| inputlookup topDomainsfinal.csv | rename fqdn as PaloUrlFiltering.desthostname | fields PaloUrlFiltering.desthostname ] |stats count sum(count) as Totalrequest list(count) as Individualdomainrequestcount list(time) as time values(PaloUrlFiltering.category) as PaloUrlFiltering.category values(PaloUrlFiltering.action) as PaloUrlFiltering.action by PaloUrlFiltering.clientip PaloUrlFiltering.desthostname | convert ctime(time) | rename PaloUrlFiltering.desthostname as fqdn | lookup topDomainsfinal.csv fqdn as fqdn Output category vtratio | where Totalrequest >=2 | fields PaloUrlFiltering.clientip fqdn Totalrequest Individualdomainrequestcount time PaloUrlFiltering.category PaloUrl_Filtering.action category vtratio

| Join time
[ search | tstats allowoldsummaries=t count from datamodel=DNSInternal where DNSQuery.domain!="*explicit.bing.net" groupby time DNSQuery.srcip DNSQuery.domain | search [| inputlookup topDomainsfinal.csv | rename fqdn as DNSQuery.domain | fields DNSQuery.domain ] |stats count sum(count) as Totalrequest list(count) as Individualdomainrequestcount list(time) as time values(category) as category by DNSQuery.srcip DNSQuery.domain | convert ctime(time) | rename DNSQuery.domain as fqdn | lookup topDomainsfinal.csv fqdn as fqdn Output category vtratio | where Totalrequest >=2 | fields DNSQuery.srcip fqdn Totalrequest Individualdomainrequestcount time category vtratio] |

0 Karma

Path Finder

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.

0 Karma

Champion

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

0 Karma

Path Finder

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

0 Karma

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?

0 Karma

Path Finder

@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

0 Karma

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.

0 Karma