Splunk Search

Build a table with information from various sourcetype

dineshp
Explorer

I have two different logsource,
ProxyLogs: Contains "ipaddress" and "username"
WebLogs: Conatains "IP_address" and whole other stuff like UserAgent, Time, Branch,HostName,loginname

I have a query that is giving me following things which i want

TIme, IP, hostname, UA, loginname, Branch

But my requirement is to Lookup the individual "ipaddress" from ProxyLogs in the Weblogs and find the matching username (i.e who that IP belongs to)

Time, IP, hostname, UA, loginname, Branch, username

I have this so far:
sourcetype=WebLogs (other- logic)| stats count by Time,IP_address,HostName,UserAgent,loginname,Branch| rename IP_address as IP | rename UserAgent as UA | join IP type=outer [search sourcetype=WebLogs |fields username ipaddress]

But this result in random user every i run the search.

Any help will be really appreciated.

0 Karma

Richfez
SplunkTrust
SplunkTrust

There are a variety of ways to do this, ranging from simple to complex and from slow to quick. I'm going to throw out one that should be fast, if it'll work for you.

There's some things I can't quite tell from the information you supplied so some of this is a guess. But, it sounds like your WebLogs have all the information except the external IP address (which in the below I'm going to pretend is named "external_ip" to keep it straight), which comes from the ProxyLogs.

sourcetype=WebLogs OR sourcetype=ProxyLogs 
| stats count AS Total, last(external_ip) AS Outside_Address By Time, IP_Address, hostname, UserAgent, loginname

Again, I'm not positive it'll work for you (and I'm sure it'll need more fields and some alterations to make it work), but maybe this will give you a different idea on how to accomplish it.

What it does is just take ALL those events together, then use the stats command to group them. The way the WebLogs and ProxyLogs connect together isn't perfectly clear in your question, and it's not clear in the above answer either - so the explanation is at this point that I'm assuming "loginname" is common to both. I don't think it is, I think you have one have "username" and the other "loginname", which I'm hoping is how they connect. In that case, you'll need to make them the same field so you'll have a match, but you can't just rename it because the field exists already. So here's a technique for that...

sourcetype=WebLogs OR sourcetype=ProxyLogs 
| eval MyConnectingUser=coalesce(username,loginname) 
| stats count AS Total, latest(external_ip) AS Outside_Address By Time, IP_Address, hostname, UserAgent, MyConnectingUser

So, for each event it'll have either a username or a loginname, right? (Because of my example, that is). The eval will coalesce those two into a new field, if username exists it'll copy username into MyConnectingUser and ignore loginname. If username doesn't exist, it'll take loginname and copy THAT into MyConnectingUser. Then the stats uses MyConnectingUser to "do things."

Also note my "latest(external_ip)". It's a way to grab the latest external_ip from a group of them - stats will "group" the events so you HAVE to use some sort of summary field. earliest(), first(), last(), latest(), values() and list() are all ways to "summarize" a field without summarizing it, if you get my drift there.

maciep
Champion

I didn't check the logic, but I believe the join command needs matching fields to join on, so maybe something like this:

sourcetype=WebLogs (other- logic)| stats count by Time,IP_address,HostName,UserAgent,loginname,Branch| rename IP_address as IP | rename UserAgent as UA | join IP type=outer [search sourcetype=WebLogs |fields username ipaddress | rename ipaddress as IP]
0 Karma

dineshp
Explorer

Tried it, didnt work.

0 Karma

maciep
Champion

also, noticed that both your main search and join search are looking at the same sourcetype...thought they were different sourcetypes? Should the join search be looking at ProxyLogs?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...