Getting Data In

How can I create a Dashboard to display only those domain User Accounts for which the contents of a specific AD attribute has changed?

untieshoe
Path Finder

Specifically, if an AD user account attribute "employeeType" changes from "NULL" to "Contractor", how can I detect/filter for that? Ideally I'd make a Dashboard to display only the User Accounts for which that attribute changed from NULL to Contractor (or something other than NULL) over the previous week. I have Active Directory feeds already set up, so I already receive historical data (snapshots of AD) that contain the data I need (i.e. the 'before' value and 'after' value of the attribute).

0 Karma

sundareshr
Legend

streamstats is what you need to use. Try this with appropriate field names

... | reverse | streamstats window=1 current=f latest(employeeType) as changedFrom | where employeeType="Contractor" AND changedFrom!="Contractor"

http://docs.splunk.com/Documentation/Splunk/6.0.4/SearchReference/Streamstats

0 Karma

sundareshr
Legend

This baffles me. wonder if @somesoni2 or @woodcock have ideas/thoughts

0 Karma

somesoni2
Revered Legend

Give these a try

index=msad sAMAccountName=jsmith | reverse | streamstats window=1 current=f latest(securecomputingCom2000_SafeWord_UserID) as changedFrom | where securecomputingCom2000_SafeWord_UserID="01234567" AND (isnull(changedFrom) OR changedFrom="NULL")

OR

 index=msad sAMAccountName=jsmith | fillnull value="NULL" securecomputingCom2000_SafeWord_UserID | stats list(securecomputingCom2000_SafeWord_UserID) as securecomputingCom2000_SafeWord_UserID by sAMAccountName | where mvindex(securecomputingCom2000_SafeWord_UserID,0)="NULL" AND mvindex(securecomputingCom2000_SafeWord_UserID,-1)="01234567"
0 Karma

untieshoe
Path Finder

Thank you for contacting others, sundareshr.
And thank you for taking a look at this, somesoni2.
Your first suggestion does result in locating the one event that is the indicator that the attribute has just changed from null to the value 01234567, so that is encouraging.
The second suggestion results in all events before, during and after the change in attribute.

The only problem with the first suggestion is that it is tailored to work for a particular value of securecomputingCom2000_SafeWord_UserID (i.e. 01234567). In real life this value will always be a unique alphanumeric, representing the serial number of the SafeWord token issued to the user account. If I try to use a wildcard ...| where securecomputingCom2000_SafeWord_UserID="*" AND (isnull(changedFrom) OR changedFrom="NULL") then the search finds no results.

0 Karma

somesoni2
Revered Legend

Anything missing from query 1 result that you were expecting?

The query 2 is just checking and displaying the records for the accounts where securecomputingCom2000_SafeWord_UserID was changed. If your goal is just to display the sAMAccountName and it's latest status, you can add following after end of query 2

.... |  eval securecomputingCom2000_SafeWord_UserID =mvindex(securecomputingCom2000_SafeWord_UserID,-1)="01234567" | table sAMAccountName securecomputingCom2000_SafeWord_UserID

Instead of hardcoded "01234567", if you want to use a dynamic serial number, then try like this

index=msad sAMAccountName=jsmith | reverse | streamstats window=1 current=f latest(securecomputingCom2000_SafeWord_UserID) as changedFrom | where (isnotnull(securecomputingCom2000_SafeWord_UserID) OR securecomputingCom2000_SafeWord_UserID!="NULL") AND (isnull(changedFrom) OR changedFrom="NULL")

OR

index=msad sAMAccountName=jsmith | fillnull value="NULL" securecomputingCom2000_SafeWord_UserID | stats list(securecomputingCom2000_SafeWord_UserID) as securecomputingCom2000_SafeWord_UserID by sAMAccountName | where mvindex(securecomputingCom2000_SafeWord_UserID,0)="NULL" AND mvindex(securecomputingCom2000_SafeWord_UserID,-1)!="NULL"

0 Karma

untieshoe
Path Finder

Nothing was missing from query 1.
I'm not interested in the current status of all the accounts; I'm only interested in the status of user accounts where the attribute has just changed (from NULL to something).

The dynamic serial number example worked only until I removed jsmith from the search criteria. When I remove jsmith from the search, I end up with data on all users who have some value in securecomputingCom2000_SafeWord_UserID.

The bottom (3rd) search code produced something quite amazing and unexpectedly useful, and is a keeper!! When I remove the specific user (jsmith) from the search (and I also filter out the AD machine accounts '*$'), what results is a list of user account where the securecomputingCom2000_SafeWord_UserID attribute has changed over time and ended up as NULL. So the output represents staff who no longer have tokens assigned to them. This is useful, and is sort of the exact opposite of my original quest: to find all users where the securecomputingCom2000_SafeWord_UserID value starts as NULL and changes into something else! Thank you for your persistence!

0 Karma

somesoni2
Revered Legend

Try this variant of query1 with dynamic otion

index=msad sAMAccountName=*| reverse | streamstats window=1 current=f latest(securecomputingCom2000_SafeWord_UserID) as changedFrom by sAMAccountName | where (isnotnull(securecomputingCom2000_SafeWord_UserID) OR securecomputingCom2000_SafeWord_UserID!="NULL") AND (isnull(changedFrom) OR changedFrom="NULL")
0 Karma

untieshoe
Path Finder

That one produces hundreds of non-relevant results: All users with their token values.

0 Karma

somesoni2
Revered Legend

In field securecomputingCom2000_SafeWord_UserID, does it contains a string value "NULL" OR it's just blank OR field not available in the event?

0 Karma

untieshoe
Path Finder

Oh, that's a good point. It appears that in AD the attribute is blank (not set) when a token has not been assigned to a user. (I may have been misusing the term "NULL"; I apologize).
When Splunk pulls AD data for each user account it seems to leave out the blank attributes, including securecomputingCom2000_SafeWord_UserID when that attribute is not set.

Then after the attribute is assigned a value for a user account, the attribute is included in the pulled attribute set for that account.

So I think I incorrectly stated my initial requirement. Instead of seeking the change from blank to something for the securecomputingCom2000_SafeWord_UserID attribute, what I should have requested was "how to detect the addition of an attribute (and its value) that was previously not present".

0 Karma

somesoni2
Revered Legend

Great... Let's try again

index=msad sAMAccountName=* | fillnull value="NULL" securecomputingCom2000_SafeWord_UserID | reverse | streamstats window=1 current=f latest(securecomputingCom2000_SafeWord_UserID) as changedFrom by sAMAccountName | where isnotnull(changedFrom) AND (securecomputingCom2000_SafeWord_UserID!="NULL" AND changedFrom="NULL")
0 Karma

untieshoe
Path Finder

After realizing I'd mis-stated the problem, found my own solution that was adequate. Needed to use fillnull to populate an AD attribute (securecomputingCom2000_SafeWord_UserID) that was missing from most user accounts. Then used dedup to locate user accounts that have more than one value for securecomputingCom2000_SafeWord_UserID. Here is the result which works:

index=msad sourcetype=ActiveDirectory | fillnull securecomputingCom2000_SafeWord_UserID | dedup displayName, securecomputingCom2000_SafeWord_UserID | stats count by displayName | where count > 1 | sort displayName | table displayName, count
0 Karma

untieshoe
Path Finder

That search results in only multiple copies of the AD schema, with all attributes in alphabetical order. For the specific case of the securecomputingCom2000_SafeWord_UserID attribute, the value shown is: securecomputingCom2000-SafeWord-UserID=OptionalProperties

0 Karma

untieshoe
Path Finder

You are very patient with me, and I appreciate that...
This last search only delivers several copies of the entire AD schema. In the results, the only references to securecomputingCom2000_SafeWord_UserID relate to the fact that securecomputingCom2000_SafeWord_UserID has "OptionalProperties".

0 Karma

untieshoe
Path Finder

Thank you for your suggestion, sundareshr. I tried this search with various AD attributes and tried narrowing the search time interval to the moment the attribute value changes, but all searches so far have come up zero. This feels like the right approach, but there may be a detail that is not being considered. For instance, should this search work with historical data (this is what I want), or is it intended for real-time searching? Thank you.

0 Karma

sundareshr
Legend

This should work with whatever data that is returned by the criteria you in in the ... segment. Can you share you search? What do you get if you replace the where at the end with | table employeeType changedFrom

0 Karma

untieshoe
Path Finder

At its most basic, and simplified to successfully locate a single AD account known to be relevant (jsmith), my search is simply this:

index=msad sAMAccountName=jsmith | reverse | streamstats window=1 current=f latest(securecomputingCom2000_SafeWord_UserID) as changedFrom | where securecomputingCom2000_SafeWord_UserID="01234567" AND changedFrom!="01234567"

The results of the initial search, when run over a suitable time window, and piped to a table command, successfully yields the jsmith account both without and later with the value "01234567" in the "securecomputingCom2000_SafeWord_UserID" attribute for the jsmith user account. (This attribute is not native to AD, but a schema addition for smart tokens). I've also tried a similar syntax with the employeeType attribute over a time window when it was first NULL, and was then given a value "Contractor". The results are the same.

If I replace the Where clause with "| table securecomputingCom2000_SafeWord_UserID changeFrom" then I again get to see the two different values for the securecomputingCom2000_SafeWord_UserID attribute, and multiple additional events (before and after the value changes) that show these values, but this doesn't help me zero in on the point in time when the attribute changed. My objective is to find when the attribute changes amongst all the data that shows me that the value has remained constant (before and after the value changes).

Thanks again for taking on this problem, sundareshr.

0 Karma

sundareshr
Legend

In the table view, do you not see events where securecomputingCom2000_SafeWord_UserID="01234567" AND changedFrom!="01234567" ? Do these events need to be grouped by something, like say userid? If yes, you could add a by clause to the streamstats command, like so streamstats window=1 current=f latest(employeeType) as changedFrom by userid

If this doesn't include a screenshot of what you see when you do the .. | | table _time securecomputingCom2000_SafeWord_UserID changeFrom

0 Karma

untieshoe
Path Finder

I do see events where securecomputingCom2000_SafeWord_UserID="01234567" AND changedFrom!="01234567". I think that would be permutation #2 below.

The search ending with a table delivers three permutation:
1. securecomputingCom2000_SafeWord_UserID is NULL, and changedFrom is NULL
2. securecomputingCom2000_SafeWord_UserID is 01234567, and changedFrom is NULL
3. securecomputingCom2000_SafeWord_UserID is 01234567, and changedFrom is 01234567

In my test search, I'm already 'grouping' by the fact that I've singled out an individual user account known to have had the attribute change from NULL to 01234567. I.E. a group of one. But I think I understand what you're getting at. In my case I would include "...as changedFrom by sAMAccountName.

I'll see if I can throw in a screen shot.

0 Karma

sundareshr
Legend

In the above example, with 3 permutations, the where should filter #2. Does it not do that? Are there any spaces in either of the fields?

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!