Sorting values from substrings

Content Authoring
1

(imported topic written by khanand91)

hi folks,

Can anyone help me complete the query, i’m a bit stuck trying to return the last part ( equivilent of max time )

here’s what i have so far:-

QnA

(it, multiplicity of it) of unique values whose (multiplicity of it > 1) of (preceding texts of firsts “,” of (following texts of firsts “,” of (lines of file (location of regapp “besclient.exe” & “\data.txt”))))

A

user2, 194

user3, 6

user1, 215

what I’m looking for is the above but with the max time ( user logon times from the text file for each unique user )

i.e.

2010-05-07 16:28:35,user2, 194

2010-05-20 10:55:51,user3, 6

2010-05-20 10:55:51,user1, 215

data.txt example:

dom,user1,10,LONWS29252,2010-05-07 16:28:35,528

dom,user1,10,2010-05-07 16:28:36,538

dom,user1,10,LONWS29252,2010-05-07 17:20:10,528

dom,user1,10,2010-05-07 17:20:11,538

dom,user1,2,LONWS29252,2010-05-10 08:08:27,528

dom,user1,2,2010-05-10 08:08:30,538

dom,user2,2,LONWS29252,2010-05-20 09:30:43,528

dom,user2,2,LONWS29252,2010-05-20 09:48:41,528

dom,user2,2,LONWS29252,2010-05-20 09:53:59,528

dom,user2-a,2,LONWS29252,2010-05-20 10:30:01,528

dom,user2-a,2,2010-05-20 10:55:28,538

dom,user2,2,LONWS29252,2010-05-20 10:55:51,528

thanks!

2

(imported comment written by NoahSalzman)

Here is some work that might help, but only tackles subsets of the larger problem… I’m stuck on the part where we get a unique list with the maximum of one of the fields (time).

Q: (items 0 of items 0 of it, items 1 of items 0 of it, items 1 of items 1 of it) of ((it, multiplicity of it) of unique values whose (multiplicity of it > 1) of ((preceding texts of firsts “,” of following texts of firsts “,” of it) of lines of file “c:\data.txt”), ((preceding texts of firsts “,” of following texts of firsts “,” of it, following texts of lasts “,” of preceding texts of lasts “,” of it) of lines of file “c:\data.txt”)) whose (item 0 of item 0 of it is item 0 of item 1 of it)

A: user1, 6, 2010-05-07 16:28:35

A: user1, 6, 2010-05-07 16:28:36

A: user1, 6, 2010-05-07 17:20:10

A: user1, 6, 2010-05-07 17:20:11

A: user1, 6, 2010-05-10 08:08:27

A: user1, 6, 2010-05-10 08:08:30

A: user2, 4, 2010-05-20 09:30:43

A: user2, 4, 2010-05-20 09:48:41

A: user2, 4, 2010-05-20 09:53:59

A: user2, 4, 2010-05-20 10:55:51

A: user2-a, 2, 2010-05-20 10:30:01

A: user2-a, 2, 2010-05-20 10:55:28

Q: maximum of ((substring (8,2) of it & " " & month (substring (5,2) of it as integer) as string & " " & substring (0,4) of it & " " & substring (11,8) of it & " -0000") of it as time) of items 1 of ((preceding texts of firsts “,” of following texts of firsts “,” of it, following texts of lasts “,” of preceding texts of lasts “,” of it) of lines of file “c:\data.txt”)

A: Thu, 20 May 2010 03:55:51 -0700

(substring only works in version 8.x, you will need to use “first/last” operators in version 7.x)

3

(imported comment written by khanand91)

thanks Noah, that does help a little … but means that i will potentially be returning a huge amount of results still.

Is there anyone else that fancies having a crack at this problem, or are we saying that its actually not possible using native bigfix code.

cheers

4

(imported comment written by JackCoates91)

You might be able to do it in two passes… take the maximum, write it to a file, then read the file and take the unique.

5

(imported comment written by khanand91)

I guess that approach would work, but then means I would need to create / have a task deployed. with in excess of 110k desktops it would takes months to get full coverage.

It’s an analysis or bust i’m afraid :expressionless:

Thanks anyway …

6

(imported comment written by NoahSalzman)

Woo hoo! One of our long time product experts, John Talbert, provided me with this psychedelic bit of Relevance:

q: (it, maximum of items 1 of items 1 of (it,(
"cat", 1; 
"cat", 5; 
"dog", 3; 
"dog", 4; 
"bat", 1; 
"bat", 7; 
"bat", 11)) whose (item 0 of it = item 0 of item 1 of it)) of unique values of items 0 of  (
"cat", 1; 
"cat", 5; 
"dog", 3; 
"dog", 4; 
"bat", 1; 
"bat", 7; 
"bat", 11) A: bat, 11 A: cat, 5 A: dog, 4

I then added the multiplicity part to get:

Q: (items 0 of it, items 1 of it, maximum of items 1 of items 1 of (items 0 of it, (
"cat", 1; 
"cat", 5; 
"dog", 3; 
"dog", 4; 
"bat", 1; 
"bat", 7; 
"bat", 11)) whose (item 0 of it = item 0 of item 1 of it)) of (it, multiplicity of it) of unique values of items 0 of  (
"cat", 1; 
"cat", 5; 
"dog", 3; 
"dog", 4; 
"bat", 1; 
"bat", 7; 
"bat", 11) A: bat, 3, 11 A: cat, 2, 5 A: dog, 2, 4

Which led me to this solution (I hope) for your problem:

Q: (items 0 of it, items 1 of it, maximum of items 1 of items 1 of (items 0 of it, ((preceding texts of firsts 
"," of following texts of firsts 
"," of it, ((substring (8,2) of it & 
" " & first 3 of (month (substring (5,2) of it as integer) as string) & 
" " & substring (0,4) of it & 
" " & substring (11,8) of it & 
" -0000") of it as time) of following texts of lasts 
"," of preceding texts of lasts 
"," of it) of lines of file 
"c:\data.txt")) whose (item 0 of it = item 0 of item 1 of it))  of  (it, multiplicity of it) of unique values whose (multiplicity of it > 1) of  (preceding texts of firsts 
"," of following texts of firsts 
"," of lines of file 
"c:\data.txt") A: user1, 6, ( Mon, 10 May 2010 01:08:30 -0700 ) A: user2, 4, ( Thu, 20 May 2010 03:55:51 -0700 ) A: user2-a, 2, ( Thu, 20 May 2010 03:55:28 -0700 )

And here it is again with v7 compatible syntax, since “substring” requires v8:

Q: (items 0 of it, items 1 of it, maximum of items 1 of items 1 of (items 0 of it, ((preceding texts of firsts 
"," of following texts of firsts 
"," of it, ((last 2 of first 10  of it & 
" " & first 3 of (month (last 2 of first 7 of it as integer) as string) & 
" " & first 4 of it & 
" " & last 8 of it & 
" -0000") of it as time) of following texts of lasts 
"," of preceding texts of lasts 
"," of it) of lines of file 
"c:\data.txt")) whose (item 0 of it = item 0 of item 1 of it)) of (it, multiplicity of it) of unique values whose (multiplicity of it > 1) of  (preceding texts of firsts 
"," of following texts of firsts 
"," of lines of file 
"c:\data.txt") A: user1, 6, ( Mon, 10 May 2010 01:08:30 -0700 ) A: user2, 4, ( Thu, 20 May 2010 03:55:51 -0700 ) A: user2-a, 2, ( Thu, 20 May 2010 03:55:28 -0700 )

Edit: fixed month-to-time string to work with more than just “May” :slight_smile:

7

(imported comment written by khanand91)

problem solved!!! the relevance works perfectly …

I’ve always thought that one of the many great things that sets Bigfix apart from its competitors is the excellent support and enthusasium shown by its employees and those that contribute to this forum :slight_smile:

thanks again.