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

[shayhibah]

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

[kamlesh_vaghela · Accepted solution]

@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

[shayhibah]

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

[kamlesh_vaghela]

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

[shayhibah]

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

[kamlesh_vaghela]

@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

[shayhibah]

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

[kamlesh_vaghela]

ooh Great.

Happy Splunking