topic How can I create a Dashboard to display only those domain User Accounts for which the contents of a specific AD attribute has changed? in 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 — Fri, 08 Apr 2016 19:29:58 GMT

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

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

sundareshr — Sat, 09 Apr 2016 17:04:06 GMT

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

Re: 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 — Sat, 09 Apr 2016 19:20:57 GMT

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.

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

sundareshr — Mon, 11 Apr 2016 15:08:35 GMT

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

Re: 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 — Tue, 29 Sep 2020 09:23:11 GMT

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.

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

sundareshr — Mon, 11 Apr 2016 18:27:15 GMT

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

Re: 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 — Tue, 29 Sep 2020 09:23:14 GMT

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.

Re: 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 — Mon, 11 Apr 2016 19:07:27 GMT

I don't know how to upload a screen shot, but I did notice this:
The events for permutations 2 and 3 have the identical time stamps. Might this be a problem?

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

sundareshr — Mon, 11 Apr 2016 20:14:39 GMT

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?

Re: 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 — Mon, 11 Apr 2016 20:48:16 GMT

It does not do that.
In fact, if I try only | where changedFrom!="01234567" then I end up with no results instead of the NULL results.
There are no spaces.

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

sundareshr — Tue, 12 Apr 2016 13:39:09 GMT

instead of .. | where changedFrom!="01234567" can you try .. | search changedFrom!="01234567".

Re: 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 — Tue, 12 Apr 2016 15:45:17 GMT

That yields zero results.

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

sundareshr — Tue, 12 Apr 2016 17:26:46 GMT

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

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

somesoni2 — Tue, 12 Apr 2016 18:35:41 GMT

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")

 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"

Re: 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 — Tue, 29 Sep 2020 09:20:36 GMT

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.

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

somesoni2 — Tue, 29 Sep 2020 09:20:38 GMT

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")

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"

Re: 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 — Tue, 29 Sep 2020 09:20:43 GMT

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!

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

somesoni2 — Tue, 12 Apr 2016 21:00:06 GMT

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")

Re: 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 — Tue, 12 Apr 2016 21:08:17 GMT

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

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

somesoni2 — Tue, 29 Sep 2020 09:20:51 GMT

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