Splunk Search

Correlating results from different searches

JWBailey
Communicator

I am trying to generate a report that returns a number of different account activities, specifically when new accounts are created, existing accounts are enabled, and existing accounts are disabled. I would like to generate a nice table, with the User account being modified, what the action was, and who did it. All of this information is available in various events, the issue is combining all the various events to get this all into one report.

Disabling accounts is simple, I have a direct event that gives me all the necessary information, no confusion.

The issue is between new accounts, and enabling existing accounts. When creating a new account, technically a disabled account is created, and then it is enabled. So, I see two events. Identifying the new accounts is easy, I just search for the “account created” event.

Identifying the enabled accounts is my problem, when I perform my search for them, it also returns the newly created accounts. How can I combine the search to show me all enabled accounts but not when this specific other event exists (the new account event)? Obviously both events will share some fields, but each event also has relevant information that does not intersect.

Then how can I combine all three of these (seemingly) individual searches into one results set, so I can build the table and pull the appropriate fields.

Here are some simple logs as examples:

When creating a new account, you get two events as follow:

    EventID: 100
    UserName Katie
    Action: Created Account
    Technician: Sue

    EventID: 200
    UserName: Katie
    Action Enable Account
    Technician: Sue

When enabling an account, you get a single event as follow:

EventID: 200
UserName: Mike
Action: Enable Account
Technician: Sue

When disabling an account, you get a single event as follow:

    EventID: 300
    UserName: Bob
    Action: Disable Account
    Technician: Sue

I want my report to say:

Katie – New Account – By Sue

Mike – Enabled Account – By Sue

Bob – Disabled Account – By Sue

I don’t know that it would be pretty, or efficient, but I think I can do it with sub-searches and join commands if I could figure out how to eliminate the “false positive” results from the Enable account search.

Thank you.

0 Karma
1 Solution

lguinn2
Legend

I don't know that you need a bunch of subsearches - would this work?

yoursearchhere
| sort UserName _time EventID
| streamstats current=false window=1 last(Action) as prevAction by UserName
| where NOT ( prevAction="Created Account" and Action="Enable Account" )
| rename _time as Time
| fieldformat Time = strftime(Time,"%c")
| table UserName Action Technician Time

View solution in original post

0 Karma

lguinn2
Legend

I don't know that you need a bunch of subsearches - would this work?

yoursearchhere
| sort UserName _time EventID
| streamstats current=false window=1 last(Action) as prevAction by UserName
| where NOT ( prevAction="Created Account" and Action="Enable Account" )
| rename _time as Time
| fieldformat Time = strftime(Time,"%c")
| table UserName Action Technician Time
0 Karma

lguinn2
Legend

streamstats is not very efficient; it may not perform very well over large data sets. But it probably performs just as well as multiple sub-searches. There might be a way to do this with the stats command, but I couldn't figure it out.

BTW, "large data sets" to me means petabytes across more than 3 Splunk indexers. More or less... It also depends on how long you are willing to wait for the report to generate!

0 Karma

JWBailey
Communicator

Thank you, I came up with the same where once I thought about simplifying mine. It is easier to negate the 1 set of results I don't want, than including the 3 types I do.

How does the streamstats command effect the search time / efficiency? Is this a relatively demanding command?

0 Karma

lguinn2
Legend

You could certainly use the more complex where command. But it would be even more efficient to put that in your initial search

yoursearchhere (Action="Disable Account" OR Action="Create Account" OR Action="Enable Account")

That would eliminate any other kinds of events before Splunk goes to the trouble of sorting them, etc. I hadn't thought about the initial search...

I also updated my where command, because it wasn't right... it needed the parentheses or else the test would be wrong - which was probably your point!

0 Karma

JWBailey
Communicator

So I think I follow this. The streamstats basically looks back one event and stores the action in the prevAction field. The where command then filters the results, only keeping events we want.

Why wouldn't I use a more complex where command:
| where Action="Disable Account" OR Action="Create Account" OR (Action="Enable Account" AND NOT prevAction="Create Account")

It seems like the where command you used will only keep the correct Enable Account events, am I miss interpreting this, or was that the intentions of your where command?

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 ...