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