1

My end goal is to have a script that will count the instances of each username in all files.

A username is a string, in quotes, that follows the string 'login'. For example, in one file, I might have:

{"this":"is', {"a":"strange"}, "type":{"of":"object", "but":"please"},
 "go":"withit", "login":"username1"}

{"this":"is', {"login":"username2"}, "type":{"of":"object", "but":"please"},
 "go":"withit"}

And in another file, I might have:

{"this":"is', {"a":"strange"}, "type":{"of":"object", "but":"please"},
 "go":"withit", "login":"username3"}

{"login":"username1", "please":"gowithit"}

In which case, I'd like to have a txt file that contains a dict object with the count of the number of times each username appears in the files:

{"username1": 2, "username2":1, "username3":1}

I've read a few things to get me started, but I can't seem to put this together. I've sort of pseudocoded it, but I can't progress from this point.

I think I need to do this in two stages.

1) Get a list of all the usernames

2) Count the number of times each username appears in all files.

For task 1):

 grep 'login:' * | sed 's/^.*: //'
#Except I think this gets everything from the line after 'login', which isn't what I want.

For task 2):

for all_usernames_in_file:
     stringval = username_read_from_saved_file
     cat * | grep -c $stringval > output.txt

Can anyone take it from here?

EDIT:

Do you mean I should do this:

grep -o 'login":"[^"]*"' /path/to/dir/* | cut -d'"' -f3 | sort | uniq -c | sed '1i{ s/\s*\([0-9]*\)\s*\(.*\)/"\2": \1,/;$a}' > output.txt

EDIT 2: Still not working. I'm trying to diagnose by understanding what each command does.

Let's say I'm just looking at this part to start:

grep -o 'login":"[^"]*"' /path/to/dir/* | cut -d'"' -f3 | sort | uniq -c > myfile.txt

Right now, myfile.txt is blank.

Here's what I think this command is doing:

grep -o matches non-empty parts of a matching line.

'login":"[^"]*"' is the string we want grep to match. In the middle, the [^"] matches any character after login":" not equal to ", and the * says we want any length of match - that is, the length of the username doesn't matter, we want everything between the quotes.

| is a pipe. It means "and then"

cut -d '"' -f3 means slice up the returned line (all stuff after login":"), using the delimiter ", and take field 3 (that is, just the username).

| is a pipe. It means "and then"

sort the usernames

| is a pipe. It means "and then"

Get the unique usernames and count the number of times each appears.

If I take that much, and put a > myfile.txt at the end, then I should end up with a txt file that contains usernames and a count of the number of times each appears. It won't be well-formatted, but it will exist.

Why am I not getting such a file?

NOTE: does it matter that I'm searching through .json.gz formatted files? I've gotten the script to work when searching through txt, but not through the other format.

2
  • Are the documents in fact JSON? Is the single quote in {"this":"is', a typo?
    – Kusalananda
    Mar 27, 2019 at 19:31
  • If this is supposed to be JSON documents, could you please add properly formatted examples of these documents? At the moment, the data is not JSON and any attempt to parse them using a proper JSON parser (jq) fails due to stray objects without keys.
    – Kusalananda
    Mar 27, 2019 at 20:07

4 Answers 4

2

Assuming you always have login and value in double quotes following each other without spaces here's a construction to grep and count it:

grep -o 'login":"[^"]*"' * | cut -d'"' -f3 | sort | uniq -c

This will produce list of logins with a number of occurrences.

Now we need to form from it a json format you need. sed is able to do that for you:

| sed '1i{
       s/\s*\([0-9]*\)\s*\(.*\)/"\2": \1,/;$a}'

Here sed will put { in the beginning of block and } to its end, and change uniq output to json format you expect.

UPD: In the end final command should look this way:

grep -o 'login":"[^"]*"' * | cut -d'"' -f3 | sort | uniq -c | sed '1i{
       s/\s*\([0-9]*\)\s*\(.*\)/"\2": \1,/;$a}' > file.txt
7
  • Does this result in a txt file that contains all usernames and their frequencies? Mar 27, 2019 at 16:08
  • Yeah, if you concatenate all the commands and redirect the output to the file, it should.
    – rush
    Mar 27, 2019 at 16:13
  • Silly question: do you put these two commands together on the same line? Please see the edit? Mar 27, 2019 at 16:41
  • @StatsSorceress I've added it to the answer. Please note that linebreak in sed is a very important. Otherwise it will insert in the beginning of block entire sed command instead.
    – rush
    Mar 27, 2019 at 16:44
  • That didn't work. It gave me a file containing { "[^": 1, } Mar 27, 2019 at 16:49
1

To get all usernames, i.e. all string associated with a login key, from a well formed JSON document, without knowing the document structure:

jq -r '..|select(.login?).login' file.json

Applying this to a number of JSON files, and sorting and counting the result:

jq -r '..|select(.login?).login' *.json | sort | uniq -c

The jq expression used here is

  • ..: Recurse through all keys and values.
  • select(.login?): Select the encountered objects that contain a login key.
  • .login: Get the value for that key.

The dictionary thing you'd like to have, building on the above jq expression:

jq -sr '[..|select(.login?).login]|group_by(.)|map({key:.[0],value:length})|from_entries' *.json

Testing:

$ cat file.json
{"this":"is", "A":{"login":"username2"}, "type":{"of":"object", "but":"please"},
 "go":"withit", "login":"me"}
$ jq -sr '[..|select(.login?).login]|group_by(.)|map({key:.[0],value:length})|from_entries' file.json
{
  "me": 1,
  "username2": 1
}

Giving it the same file twice:

$ jq -sr '[..|select(.login?).login]|group_by(.)|map({key:.[0],value:length})|from_entries' file.json f
ile.json
{
  "me": 2,
  "username2": 2
}

Use jq with -c to get the single line compact output.

For our example file, jq -sr '[..|select(.login?).login]' file.json would produce

[
  "me",
  "username2"
]

Passing this through group_by(.) gives

[
  [
    "me"
  ],
  [
    "username2"
  ]
]

The map({key:.[0],value:length}) part gives

[
  {
    "key": "me",
    "value": 1
  },
  {
    "key": "username2",
    "value": 1
  }
]

and the from_entries at the end gives the final result.

1

How about using a perl hash keyed on a regex match, which you can convert using the JSON module:

$ perl -MJSON -lne '$h{$1}++ for /(?<="login":")(.*?)(?=")/g }{ print encode_json \%h' file1 file2
{"username3":1,"username2":1,"username1":2}
7
  • Took me a few minutes to get my head around this but its sweet. Why do you need the 'l' switch?
    – bu5hman
    Mar 29, 2019 at 14:45
  • @bu5hman TBH I'm hazy on the perl -l switch (autochomp?) - I tend to just throw it in if the newline handling screws up (forgive me, perl gods). There's a discussion of what it does here: The top 10 tricks of Perl one-liners Mar 29, 2019 at 14:52
  • Fair enough. Can you also educate a perl script kiddie like me as to why the print only executes once under the '-n' switch? Is this a perl specific construct?
    – bu5hman
    Mar 29, 2019 at 15:04
  • @bu5hman not sure where it's documented, but the }{ is a cheat way of making everything to the right into an END{ } block; everything to the left is looped over per the -n switch Mar 29, 2019 at 15:08
  • Makes perfect sense since perldoc states that an END block is executed outside the implicit loop. I count myself educated. Thanks.
    – bu5hman
    Mar 29, 2019 at 15:15
0

@rush use of sed didn't work in my shell so I went this way

grep -Poh '(?<=login":")[^"]*' json* | sort | uniq -c | awk  -v OFS=': ' 'BEGIN{print "{"}{print $2, $1}END{print"}"}' | sed -E 's/([0-9])$/\1,/g;s/:/\":/g;s/^([^{}])/\"\1/g'

The multiple sed can be amended if your shell lets you escape the " and print them in the awk statement.

grep -Poh '(?<=login":")[^"]*' json* | sort | uniq -c | awk  -v OFS=': ' 'BEGIN{print "{"}{print \"$2\", $1}END{print"}"}' | sed -E 's/([0-9])$/\1,/g'

In my shell awk choked on the \" in the second script. Not sure why but am sure that someone out there will tell me.

I also tried jq but it choked on the json files. There appears to be a syntax error

"this":"is' #is written so I edited these to
"this":"is"

Also jq didn't like the construct

{"a":"strange"} # so I also edited these to
b: {"a":"strange"}

If original files are supposed to be as per the edits made then jq works

jq '.login' json* | sort | uniq -c | awk -v OFS=': ' 'BEGIN{print "{"}{print $2, $1}END{print"}"}' | sed -E 's/([0-9])$/\1,/g'

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .