Identifying compliant logins - including last login time.

[DLevine_]

Working on supplementing a search we are using to implement conditional access policies. The search identifies successful logins and produces a percentage of compliant logins over a period. What I am trying to add, is the last login time which is identified by the "createdDateTime" in the logs. 

Here is the current search: 
index="audit" sourcetype="signin" userPrincipalName="*domain.com" status.errorCode=0
| eval DeviceCompliance='deviceDetail.isCompliant'
| chart count by userPrincipalName DeviceCompliance
| eval total=true + false
| rename true as compliant
| eval percent=((compliant/total)*100)
| table userPrincipalName compliant total percent

I have tried adding / modifying pipes like "stats latest(createdDateTime) by userPrincilaName compliant total percent" but this is inserting the time into the true / false fields. I feel that I am modifying the data too much up front and maybe need to change around the piping order perhaps? All suggestions welcomed.

[yuanliu]

Two critical question you haven't answered:

1. What is defined as "last login time" in relation to DeviceCompliance?  Is it the very last regardless of compliance?  Or is it the last time in each compliance state?
2. How do you want to display this value in your presentation?  Use a mockup table to illustrate.

[DLevine_]

Thanks for the reply. 

1. The logs identify a 'createdDateTime' for each login entry. There is no direct relationship to compliance, however, I am searching for successful logins in the search, then defining what percentage of those are compliant.  So, here I am trying to get the very last attempt, regardless of compliance. 

2.Ideally the table would look something like this:

userPrincipalName	Compliant	Total	Percent	lastLogin
Joe.Camel@domain.com	4	4	100	2024-12-06T18:14:50Z
Drake.Mallard@domain.com	1	4	25	2024-12-05T22:56:25Z
Jane.Doe@domain.com	0	8	0	2024-11-25T07:13:22Z

Where the 'lastLogin' column is the entry for the very last 'createdDateTime' login entry per user.

[yuanliu · Accepted solution]

Now this is a well-defined problem.  As you suspected, you will have to manipulate data one way or another  if you want this chart format.  So here is one option

index="audit" sourcetype="signin" userPrincipalName="*domain.com" status.errorCode=0
| rename "deviceDetail.isCompliant" as DeviceCompliance
| stats count latest(createdDateTime) as lastLogin by userPrincipalName DeviceCompliance
| eventstats max(lastLogin) as lastLogin by userPrincipalName
| tojson userPrincipalName lastLogin
| chart sum(count) as count over _raw by DeviceCompliance
| fillnull true false
| eval total=true + false
| rename true as compliant
| eval percent=((compliant/total)*100)
| spath
| table userPrincipalName compliant total percent lastLogin

Here is an emulation to test this:

index = _audit action IN (artifact_deleted, quota)
| eval action = if(action == "quota", "true", "false")
| rename user AS userPrincipalName, action AS DeviceCompliance, _time as createdDateTime
| eval createdDateTime = strftime(createdDateTime, "%FT%H:%M:%S")
``` the above emulates
index="audit" sourcetype="signin" userPrincipalName="*domain.com" status.errorCode=0
| rename "deviceDetail.isCompliant" as DeviceCompliance
```

Combine these, I get

userPrincipalName	compliant	total	percent	lastLogin
yliu	46	46	100	2024-12-05T22:04:13
splunk-system-user	134	392	34.183673469387756	2024-12-06T03:06:08

[DLevine_]

Thank you, this does work wonderfully. I am still learning and not wanting to be spoon-fed. 
Can you explain why you chose to json at the point you did and what is this portion of the piping doing? 

| chart sum(count) as count over _raw by DeviceCompliance

 The rest of it makes complete sense when I work through it out loud.

[yuanliu]

Glad it works out.  JSON allows for semantic expression.  The more traditional "Splunk" trick is to use string concatenation then split after stats.  tojson command is present in all Splunk versions; in this case, it is also very concise.

If you remove the rest of search after that chart, you'll see something like this:

_raw	false	true
{"lastLogin":"2024-12-12T23:42:47","userPrincipalName":"yliu"}		28
{"lastLogin":"2024-12-13T00:58:38","userPrincipalName":"splunk-system-user"}	290	150

The intent is to construct a chart that will render the desired table layout while retaining all the data needed to produce final presentation. (This is why I ask for a mockup table so I know how you want to present data.  Presentation does influence solution.)