Splunk Search

Joining indexes for stats results

the_dude
Engager

I have three indexes I am trying to join that have at least three similar columns each. I want to table the results in order to generate a report and alert. What would be the fastest method to work around using the join command if possible? Because my environment is built to min specs I need to not utilize something that is not resource heavy. Below is my query the "| table" is where I am having issues. Cyber is my elevated account vault AD is my active directory and the unix is for my redhat environment. I am a little lost currently as I have not played with Splunk in a couple of years.


index=cyber  AND index=AD  AND index=unix
| table _eventtime, issuer, requestor, purpose (for cyber)
| table user, issuer, elevID, action (for AD)
| table user, path, cmd (for unix)

Labels (3)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

You are correct for not wanting to use join, for join is perhaps not what you need.  But you need to give us precise prescription about the field or fields you want to join these three indices.  Two of them have an identical field name "user".  Do they have the same values? (Window and Unix usually do not.)  Then, a different pair of indices have an identical field name "issuer".  Then, there is yet another field name in cyber bearing semantic semblance of a user, namely "requestor".  Is this the field you want to "join" with the "user" field in the other two indices?

If you want to join requestor in cyber with user in the other two indices, the following should be your first draf:

index IN (cyber, AD, unix)
| rename requestor AS user
| stats values(_eventtime) as _event_time, values(issuer) as issuer values(purpose) as purpose
values(elevID) as evelID, values(action) as action
 values(path) as path, values(cmd) as cmd by user

Even so, there can be variations depending on other requirements.  Unless you give a prescription, others cannot give you a good answer.

0 Karma

the_dude
Engager

Thank you for the insight. The "| table  * *" were the columns that all match with variances in AD and unix. I have everything broken down specifically per each index in order to have somewhat of a uniform and sanitary environment. I am having to retake a crash course right now in splunk query. Let me try the method you prescribed and we can continue from there. I'll double check my column headers between the three indexes. I will be more precise in my explanation on my next follow up. Thank you.


..update..

index=cyber AND index=AD
| table act, devtype, safe, issuer, username, purpose (for cyber)
| table audit, e_user, evnt_cat, evnt_tsk, proc_name,   (for AD)

index=cyber AND index=unix
| table act, devtype, safe, issuer, username, purpose (for cyber)
| table proc, src, user, msg (for unix)

Double checked my data. AD and unix searches are never done together, always cyber and one or the other.

0 Karma
Get Updates on the Splunk Community!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...