topic Re: Using single query with two sourcetypes? in Splunk Search

Using single query with two sourcetypes?

brc55 — Tue, 28 Dec 2021 14:21:46 GMT

I have 2 sourcetypes, vpn & winevents, how do you write a single query to get winevents of the top 5 busiest machines of IP X (1 IP is used by many users). The vpn sourcetype contains both hostname & IP, while the winevents only contains the hostname.

I'm assuming I'd utilize the append command and a sub search

sourcetype=winevents | append [search sourcetype=vpn] | top limit=5

Any help is appreciated, thanks

Re: Using single query with two sourcetypes?

tscroggins — Mon, 03 Jan 2022 04:03:14 GMT

@brc55

You can return events from multiple source types in one search:

(sourcetype=winevents OR sourcetype=vpn)

or:

sourcetype IN (winevents vpn)

What do you mean by "busiest" users of an IP address? Are you trying to 1) correlate winevents to the vpn IP address by hostname and 2) get the top 5 hostnames by IP address? This isn't difficult, but we'll need to know more about the fields available in the winevents and vpn source types.

Re: Using single query with two sourcetypes?

SinghK — Mon, 03 Jan 2022 05:48:39 GMT

(sourcetype=winevents OR sourcetype=vpn)| top limit=5

Re: Using single query with two sourcetypes?

isoutamo — Mon, 03 Jan 2022 08:42:00 GMT

usually the most efficient way is use stats on this kind of cases. Based on what you are meaning with "most busiest" you should use something like this:

index IN (vpn, winevents) sourcetype IN (<your sourcetypes here, separated with ,>) | stats count(ip) as nIPs values(*) as * by hostname | sort 0 - nIPs | head 5

r. Ismo