Cisco ISE: Compare field between events for change

[elliottrl]

I've been working on a query for Cisco ISE to compare what authorization devices are getting and to count when their authorization changes. Only the two most recent authentication attempts are of interest. This query works, but I'm hoping someone can help get me to something more efficient as this takes quite a bit of time to run against a 24 hour period.

sourcetype=cisco:ise:syslog log_type=Pass*
| eventstats list(SelectedAuthorizationProfiles) as authz by Calling_Station_ID
| eval authz1=mvindex(authz, 1),
authz2=mvindex(authz, 2)
| eventstats values(authz1) as authz1 values(authz2) as authz2 by Calling_Station_ID
| where authz1!=authz2
| timechart span=1h limit=0 dc(Calling_Station_ID) as "# of Changes"

[evania]

Hi @elliottrl ,

Did you have a chance to check out an answer? If it worked, please resolve this post by approving it! If your problem is still not solved, keep us updated so that someone else can help you.

Thanks for posting!

[dmarling]

You can get the last result using streamstats, but you will have to reverse the order that the events are streamed by using either the reverse command or a sort. I choose sort as I find it tends to work a bit faster:

sourcetype=cisco:ise:syslog log_type=Pass*
| sort 0 + _time
| streamstats current=false window=1 global=f values(authz) as previousAuth by Calling_Station_ID
| where authz!=previousAuth
| timechart span=1h limit=0 dc(Calling_Station_ID) as "# of Changes"

[elliottrl]

I made some slight modifications to correct, but no dice. Managed to get zero returns.

sourcetype=cisco:ise:syslog log_type=Pass*
 | sort 0 + _time
 | streamstats current=false window=1 values(SelectedAuthorizationProfiles) as previousAuth by Calling_Station_ID
 | where SelectedAuthorizationProfiles!=previousAuth
 | timechart span=1h dc(Calling_Station_ID) as "# of Changes"

[dmarling]

@elliottrl , I wanted to let you know that I figured out why the query I gave you didn't work after playing around with a similar use case I ran into. I did not added a necessary command on the streamstats for this to function properly:

 sourcetype=cisco:ise:syslog log_type=Pass*
  | sort 0 + _time
  | streamstats current=false window=1 global=f values(SelectedAuthorizationProfiles) as previousAuth by Calling_Station_ID
  | where SelectedAuthorizationProfiles!=previousAuth
  | timechart span=1h dc(Calling_Station_ID) as "# of Changes"

The global=f flag was critical for this to function due to the command defaulting to true per the documentation of streamstats:

global Syntax: global=
Description: Used only when the window
argument is set. Defines whether to
use a single window, global=true, or
to use separate windows based on the
by clause. If global=false and window
is set to a non-zero value, a separate
window is used for each group of
values of the field specified in the
by clause. Default: true

https://docs.splunk.com/Documentation/Splunk/7.2.6/SearchReference/Streamstats

I have updated the original answer as well with this correction. I can confirm that making that one modification made a difference in my similar use case.

[elliottrl]

So, the query does work, but the results differ from what I originally posted in that I'm getting different counts per hour. I'll do some more digging from my end to see if I can figure the delta out. Appreciate the help!

[dmarling]

I took a look at your original query and I can see why it's returning different results. Your orignal evals to generate auth1 and auth2 are using mvindex with auth1 being 1 and auth2 being 2 in your mvindex command. When using mvindex, 0 is the first result of the multi-valued field and 1 is the second result. Your auth1 and auth2 were the second and third results of the multi-valued field authz. This means your where clause was saying you don't want the second and third results to match, which ultimately means you where using the most current time stamp to track auth information from the prior two authorizations. I am not surprised that they are no longer lining up with the method I provided you as with my query you are comparing the auth event on the current event with the auth event on the prior event. If you really want it to be like the original query, I can modify the query, but I'm pretty sure that was not your goal when you originally wrote it.

[elliottrl]

I didn't realize that mvindex started at 0. That'll definitely make a difference in the delta between your wuery and mine. You are correct, going for the second and third entries was not my intent. I'll validate things when I have a chance and Mark the answer appropriately

[dmarling]

If you have time, do you mind going through an example where you would expect results and it did not return the expected results? If you could copy/paste a small example base with it being fuzzed a bit for any secure information I could write a run anywhere example using that data to see why it didn't work as expected.