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!

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...