Combine the count from 1 search to the result of another search

[agolkar]

Hello everyone,

I have one search that is showing me a list of IP addresses of addresses. Lets call the field of IP addresses "Name":

  index="IP_list" sourcetype="sourcetype_IP_list" | table Name Location

I get the output of:

 Name                                 Location
---------------                 --------------------
192.168.1.3                       basement
192.168.1.5                       attic
192.168.1.8                       garage

I have another search that is showing me something about the servers if that server has more than 1 of that thing. In this search, the server IP is called "IP"
Here is an example:

index="server_info" thing>0 | stats count by IP

I get the output of:

IP                      count
--------------          -------------
192.168.1.3                 4
192.168.1.5                 8
192.168.1.8                 2

How could I combine these 2 searches to I get a table that shows the IP (Name), location, and the count?

[Vijeta]

Try this -

index="IP_list" sourcetype="sourcetype_IP_list" | stats values(Location)  as Location by Name |join Name[search index="server_info" thing>0 | stats count by IP| rename IP as Name]

[chrisyounger]

Try this

index="IP_list" sourcetype="sourcetype_IP_list" 
| eval IP = Name
| fields IP Location 
| append [ search index="server_info" thing > 0 | stats count by IP]
| stats latest(count) as count values(Location) by IP

Hope this helps!

[agolkar]

I feel like its so close! Its getting me the right columns but the count field is empty for every IP.....

[chrisyounger]

Does this search work correctly search index="server_info" thing > 0 | stats count by IP ?

Secondly, what happens if you try changing the last line to this: | stats values(count) as count values(Location) by IP

[agolkar]

search index="server_info" thing > 0 | stats count by IP

Does work.

Changing the last line worked! I now see the correct counts! However I noticed one weird thing. If I add the location to the first line of the search ex:
index="IP_list" sourcetype="sourcetype_IP_list" Location="basement"

It still shows all results/counts but leaves the location of other places empty and just shows the location for servers located in "basement" any ideas on how to fix this? Thank you for the help.