How to merge search from two different hosts?

[kimsplunk]

Hello I have a list of host pairs e.g. hostA1 and hostA2, hostB1 and hostB2, etc.

I'm currently trying to search for event A to happen for each host pair and show the results count by each pair of hosts, rather than individual hosts?

E.g. the results would show

Pair 1: Host A1 or Host A2         2

Pair 2: Host B1 or Host B2         93

Pair 3: Host C1 or Host C2         42

Is it also possible to do this using a lookup file?

[yeahnah]

Hi @kimsplunk 

Sure you can.  Here's a run anywhere example...

| makeresults | eval _raw="Host,Pair
hostA1,1
hostA2,1
hostB1,2
hostB2,2
hostC1,3
hostC2,3"
| multikv forceheader=1 | table Host Pair | outputlookup host_pair.csv | where isnull(Host)  ``` create temp lookup ```
| append [ | makeresults count=6 | streamstats count | eval count=((count%2) + 1), Host="hostA".count.",hostB".count.",hostC".count, Host=split(Host, ",") | mvexpand Host ]
 ``` the above is just creating example lookup and events ```
 ``` here's the kind of foo to use in your search... ```
| lookup host_pair.csv Host OUTPUT Pair
| stats count values(Host) AS Host_Pairs BY Pair
| eval Pair="Pair".Pair.": ".mvjoin(Host_Pairs, " & ")
| table Pair count

You'll obviously need to adjust it to fit with your query result set.

If the Host pair names have a naming regular naming convention then you could potentially just use eval commands to define the Pair values and therefore not need the lookup.

Hope this helps

[kimsplunk]

Hello so I'm using the following section- however I'm not sure how to actually search for the results that I want- I'd like to add this phrase to the search: [| search index=host_index AND "phrase 1" AND "phrase 2"
| stats count by Host_Pairs]. I've tried adding it to the end but that doesn't seem to work.

| makeresults | eval _raw="Host,Pair
hostA1,1
hostA2,1
hostB1,2
hostB2,2
hostC1,3
hostC2,3"
| multikv forceheader=1 | table Host Pair
| stats count values(Host) AS Host_Pairs BY Pair
| eval Pair="Pair".Pair.": ".mvjoin(Host_Pairs, " & ")
| table Pair count

[yeahnah]

Hi @kimsplunk 

The run anywhere example I gave was just a demonstration of how to do it.

In your case, the base search is run at the beginning,  returning all the events with the Host field in them.  The lookup command (I assume you've created your lookup file?) then enriches the event with the Pair field, before it's summarised into a table with the stats command.   The last lines just reformat the output.

Based on the limited information provided, it would look something like this...

index=host_index AND "phrase 1" AND "phrase 2" Host=*
| lookup host_pair.csv Host OUTPUT Pair  ``` assuming you created your lookup file ```
| stats count values(Host) AS Host_Pairs BY Pair
| eval Pair="Pair".Pair.": ".mvjoin(Host_Pairs, " & ")
| table Pair count

If you need further help then I suggest you provide some example output of the raw events, i.e. what is returned when you run the base search query (index=host_index AND "phrase 1" AND "phrase 2") - just a few lines (remove anything sensitive).

Hope that helps