topic Re: Identifying compliant logins - including last login time. in Splunk Search

Identifying compliant logins - including last login time.

DLevine_ — Fri, 06 Dec 2024 16:46:41 GMT

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.

Re: Identifying compliant logins - including last login time.

yuanliu — Fri, 06 Dec 2024 18:00:46 GMT

Two critical question you haven't answered:

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?
How do you want to display this value in your presentation? Use a mockup table to illustrate.

Re: Identifying compliant logins - including last login time.

DLevine_ — Fri, 06 Dec 2024 18:43:41 GMT

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.

Re: Identifying compliant logins - including last login time.

yuanliu — Sat, 07 Dec 2024 04:23:55 GMT

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

Re: Identifying compliant logins - including last login time.

DLevine_ — Fri, 13 Dec 2024 14:06:12 GMT

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.

Re: Identifying compliant logins - including last login time.

yuanliu — Fri, 13 Dec 2024 23:26:51 GMT

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