Splunk Search

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

shayhibah
Path Finder

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

Tags (1)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

View solution in original post

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

0 Karma

shayhibah
Path Finder

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

shayhibah
Path Finder

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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
0 Karma

shayhibah
Path Finder

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

ooh Great.

Happy Splunking

0 Karma
Get Updates on the Splunk Community!

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...

Splunk and Fraud

Watch Now!Watch an insightful webinar where we delve into the innovative approaches to solving fraud using the ...