Given the relevance sample below, is there any way to get relevance to just return “peach”?
Q: (multiplicity of it, it) of unique values of ("cherry";"apple";"orange";"apple";"banana";"apple";"peach";"peach";"peach";"peach")
A: 3, apple
A: 1, banana
A: 1, cherry
A: 1, orange
A: 4, peach
T: 0.152 ms
I: plural ( integer, string with multiplicity )
(I thought it was actually sorting by multiplicity, so that I could grab the first result and be done, but then I realized that the unique values were sorted alphabetically and I had used “apple” as my max, so… peachpeachpeachpeach!)
How about sort the results so the one with the largest multiplicity count it the last item, concatenate the plural results to a singular string the parse the text after the last comma?
Q: following text of last "," of concatenation "," of unique values of ((multiplicity of it as string & ", " & it) of unique values of ("cherry";"apple";"orange";"apple";"banana";"apple";"peach";"peach";"peach";"peach")) as trimmed string
A: peach
T: 0.448 ms
I: singular string
Seem to work if then say banana has the highest number of occurrences
Q: following text of last "," of concatenation "," of unique values of ((multiplicity of it as string & ", " & it) of unique values of ("cherry";"banana";"apple";"orange";"apple";"banana";"apple";"peach";"peach";"peach";"peach";"banana";"banana";"banana")) as trimmed string
A: banana
T: 0.214 ms
I: singular string
I’m stumped though if say peach and banana both have the same number of occurrences as it will only return peach due to the alphabetical sort.
Q: following text of last "," of concatenation "," of unique values of ((multiplicity of it as string & ", " & it) of unique values of ("cherry";"banana";"apple";"orange";"apple";"banana";"apple";"peach";"peach";"peach";"peach";"banana";"banana")) as trimmed string
A: peach
T: 0.207 ms
I: singular string
Q: following text of last "|" of concatenation "|" of unique values of (it as string) of (multiplicity of it, it) of unique values of ("cherry";"apple";"orange";"apple";"banana";"apple";"peach";"peach";"peach";"peach")
A: 4, peach
Ohhhh good point re: ties… might need to get a bit more sophisticated and use tuple strings
Q: items 1 of items 1 of (maximum of (it as integer) of tuple string items 0 of substrings separated by "|" of it, (tuple string item 0 of it, tuple string item 1 of it) of substrings separated by "|" of it) whose (item 0 of it = item 0 of item 1 of it as integer) of concatenation "|" of (it as string) of (multiplicity of it, it) of unique values of ("cherry";"apple";"orange";"apple";"banana";"apple";"apple";"peach";"peach";"peach";"peach")
A: apple
A: peach
Thanks @SLB & @brolly33! The actual use case for this logic should have one mulitplicity that is far larger than the others, so ties aren’t a concern, but it’s good to have a way to get all of the “winners” if there is a tie.
Okay… new wrinkle. The counts of my use-case’s mulitplicities are 6, 6, 12, and 274. Alphanumerically, “6” sorts after “274”. Some zero-padding is required.
Also, I’ll stop being so coy and just share my actual relevance, which counts local logins and wake-ups in the Windows Security Event Log over the last 30 days to identify the most likely “owner” of a computer based on authentication counts:
Q: following text of last "|" of concatenation "|" of unique values of (last 4 of it of ("0000" & multiplicity of it as string) & "|" & it) of unique values of ((node values of xpath ("xmlns:a='http://schemas.microsoft.com/win/2004/08/events/event'","/a:Event/a:EventData/a:Data[@Name='TargetUserName']/text()") of xml of it) of (records of security event log) whose (event id of it = 4624 and time generated of it > (now - 30*day) and (description of it contains "Logon Type:%09%092" or description of it contains "Logon Type:%09%097") and not (description of it contains "Font Driver Host" or description of it contains "Window Manager")))
A: jcstraff
T: 16323.945 ms
I: singular substring
It’s highly unlikely that one account will have over 9,999 authentications in 30 days, so 4 characters should be enough. It also takes 16 seconds to run (and isn’t likely to change much either), so once per day on this one should do.
I’m a little late to the party, but I personally prefer a variation on the method @brolly33 used.
Rather than concatenating with a pipe, though, I prefer to build a set containing the string and its multiplicity. That allows me to better handle cases where the pipe delimiter might already be part of the string.
Treating these as tuple strings, any value that already had a comma embedded gets escaped automatically. In earlier versions, there was a problem where unmatched parentheses in the strings were not properly handled, but I’m pleased to see that at least version 10.0.3 handles that case as well.
Since I’m treating the multiplicities as integers here, I also don’t have to worry about string comparisons or zero-padding the strings.
Q: tuple string items 1 of items 0 of (elements of item 0 of it, item 1 of it) whose (tuple string item 0 of item 0 of it as integer = item 1 of it) of (it, maximum of (it as integer) of tuple string items 0 of elements of it) of set of (it as string) of (multiplicity of it, it) of unique values of ("cherry";"apple";"orange";"apple";"banana";"apple";"apple";"peach";"peach";"peach";"peach")
A: apple
A: peach
T: 0.477 ms
Tuple strings are the solution to an entire class of problems where you need to summarize and loop back to the original list to match on the sums. It’s tricky the first few times, but then it’s like morning coffee - cannot function without them!
Love your use of XMLNS and that class of inspectors.
I would tweak just a bit to use Jason’s set method for a slightly more robust solution.
q: tuple string items 1 of items 1 of (maximum of (it as integer) of tuple string items 0 of elements of it, elements of it) whose (item 0 of it = tuple string item 0 of item 1 of it as integer) of set of (it as string) of (multiplicity of it, it) of unique values of ((node values of xpath ("xmlns:a='http://schemas.microsoft.com/win/2004/08/events/event'","/a:Event/a:EventData/a:Data[@Name='TargetUserName']/text()") of xml of it) of (records of security event log) whose (event id of it = 4624 and time generated of it > (now - 30*day) and (description of it contains "Logon Type:%09%092" or description of it contains "Logon Type:%09%097") and not (description of it contains "Font Driver Host" or description of it contains "Window Manager")))
Looks a little like a “who logged on the most in the last 30 days”? type of use case. Very elegant.
I wonder if it would be permissible to speed it up alot with @strawgate method of limiting event log queries to an arbitrary number of them (say the last 5000 log entries only?)
Q: tuple string items 1 of items 1 of (maximum of (it as integer) of tuple string items 0 of elements of it|0, elements of it) whose (item 0 of it = tuple string item 0 of item 1 of it as integer) of set of (it as string) of (multiplicity of it, it) of unique values of((node values of xpaths ("xmlns:a='http://schemas.microsoft.com/win/2004/08/events/event'","/a:Event/a:EventData/a:Data[@Name='TargetUserName']/text()") of xmls of it) of records ((integers in(item 0 of it + item 1 of it - 1,maximum of (item 0 of it + item 1 of it - 5000;item 1 of it))) of (record count of it, oldest record number of it)) whose (event id of it = 4624 and time generated of it > (now - 30*day) and (description of it contains "Logon Type:%09%092" or description of it contains "Logon Type:%09%097") and not (description of it contains "Font Driver Host" or description of it contains "Window Manager")) of security event log)
