topic Re: How to merge two different queries with the same columns together? in Splunk Search

How to merge two different queries with the same columns together?

shayhibah — Thu, 05 Dec 2019 10:48:24 GMT

Hi,

I have different queries:

Query 1:
|inputlookup myLokkup | eval count=0 | table myField, count
For Example:
myField count
A 0
B 0
C 0

Query 2:
sourcetype="my_log" | stats count by myField
For Example:
myField count
A 4
C 2

How can I combine these 2 queries to return the following:
myField count
A 4
B 0
C 2

Thanks

Re: How to merge two different queries with the same columns together?

kamlesh_vaghela — Thu, 05 Dec 2019 10:55:36 GMT

@shayhibah

Can you please try this?

sourcetype="my_log" 
| stats count by myField
| append [ |inputlookup myLokkup | eval count=0 | table myField, count ] 
| stats sum(count) as count by myField

Thanks

Re: How to merge two different queries with the same columns together?

shayhibah — Thu, 05 Dec 2019 11:43:31 GMT

hi @kamlesh_vaghela

The output is incorrect:

A0
A 4
B 0
C0
C 2

It didn't remove fields that exist in both searches

Re: How to merge two different queries with the same columns together?

kamlesh_vaghela — Thu, 05 Dec 2019 11:53:41 GMT

@shayhibah

I think it should work. Can you please confirm that count doesn't have any extra hidden character.

Please check below sample search with same logic.

| makeresults | eval myField="A,C",myField=split(myField,","),count=20 | mvexpand myField | table myField count | append [| makeresults | eval myField="A,B,C",myField=split(myField,","),count=0 | mvexpand myField | table myField count] | stats sum(count) as count by myField

Is it possible to share your search with sample values?

Re: How to merge two different queries with the same columns together?

shayhibah — Thu, 05 Dec 2019 12:01:24 GMT

@kamlesh_vaghela
Sure,

This is my query:

sourcetype="my_log" | stats count by my_field
| append [|inputlookup my_lookup | rename field AS my_field | eval count=0 | table my_field, count]
| stats sum(count) as count by my_field

For some reason your query above works fine but mine does not.

Re: How to merge two different queries with the same columns together?

shayhibah — Thu, 05 Dec 2019 12:04:54 GMT

I know what the problem is - typo 😕
But your response was correct - thank you

Re: How to merge two different queries with the same columns together?

kamlesh_vaghela — Thu, 05 Dec 2019 12:05:37 GMT

@shayhibah

I think your lookup field has extra spaces.
Try this.

sourcetype="my_log" | stats count by my_field
 | append [|inputlookup my_lookup | rename field AS my_field | eval count=0 | eval myField=trim(myField) | table my_field, count]
 | stats sum(count) as count by my_field

Re: How to merge two different queries with the same columns together?

kamlesh_vaghela — Thu, 05 Dec 2019 12:06:37 GMT

ooh Great.

Happy Splunking