Where iteration with date condition on json data

Hi,
Having some trouble with conditions.
{{ $users := (where $.Site.Data.internal.users “Administrator” “eq” “True”) }}
Works as expected and users are greater then 0.

Then i try filtering one more time on a property inside that user.
{{$filtered_users := where $users “Last_Logon” “le” (now.AddDate 0 0 -90) }}
But this gives me a result of zero.

{{len $users}} = Non zero
{{ len $filtered_users }} = should be greater then zero, but are 0.

I can however do below to give me my results but then i cant “sum them up” like i intended with
{{ len $filtered_users}}
{{ range where $.Site.Data.internal.users “Administrator” “eq” “True” }}
{{ if le .Last_Logon (now.AddDate 0 0 -90) }}
{{ .username }}

{{ end }}
{{ end }}

if someone has a more clean solution to both present the content and be able to count them then im all ears.

Please share a sample of the data file.

minimal example json for two users called internal.json:

{
    "users": [
        {
            "Domain": "lab.net",
            "Administrator": "True",
            "DomainAdmin": "True",
            "Enterprise Admin": "True",
            "Disabled": "False",
            "Expired": "False",
            "PasswordNeverExpires": "True",
            "PasswordNotRequired": "False",
            "PasswordLastChanged": "2020-03-11 20:05:31",
            "LastLogon": "2022-02-23 23:07:02",
            "DN": "CN=Administrator,CN=Users,DC=lab,DC=net",
            "UsernameFull": "lab.net\\Administrator",
            "Username": "Administrator"
        },
        {
            "Domain": "lab.net",
            "Administrator": "False",
            "DomainAdmin": "False",
            "Enterprise Admin": "False",
            "Disabled": "False",
            "Expired": "False",
            "PasswordNeverExpires": "False",
            "PasswordNotRequired": "False",
            "PasswordLastChanged": "2020-03-11 20:05:32",
            "LastLogon": "2022-02-23 23:07:03",
            "DN": "CN=TRUDY_MCCRAY,OU=Devices,OU=AZR,OU=Stage,DC=lab,DC=net",
            "UsernameFull": "lab.net\\TRUDY_MCCRAY",
            "Username": "TRUDY_MCCRAY"
        }
    ]
}

In your original attempt:

  1. You are looking for the Last_Logon key but the example data has a LastLogon key (no underscore)
  2. You are trying to compare a string (the JSON value) to a time.Time value (now.AddDate 0 0 -90)

Since we cannot modify the left side of the where comparison, we can modify the right side, converting it to a string with the same format:

{{ $users := where site.Data.internal.users "Administrator" "eq" "True" }}
{{ $users = where $users "LastLogon" "le" (now.AddDate 0 0 -90 | time.Format "2006-01-02 15:04:05") }}

Now we’re performing a string comparison. Although not ideal (date-to-date comparison is preferable), you should be OK provided that the format of the LastLogon in your JSON data is consistent.

1 Like

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.