0

i have huge data list

My data looks like this

"[01/Dec/2011:20:53:04 +0900] ","COMZ","90.663.65.61","21.123.31.100","250","CONNECT","t.ierz.er:443","13127","836"
"[01/Dec/2011:22:20:01 +0900] ","COMZ","90.663.65.61","21.123.31.100","250","CONNECT","t.ierz.er:443","13127","836"
"[02/Dec/2011:24:33:04 +0900] ","COMZ","20.663.65.61","2.123.91.100","220","CONNECT","t.ierz.er:443","13127","836"

How can I get a data format like unique value data or IP address

01/DEC/2011 90.663.65.61 21.123.31.100

Because I have get the same value and cant get the unique value

[01 / Dec / 2011: 20: 53: 04 0900] 90.663.65.61 21.123.31.100
[01 / Dec / 2011: 20: 53: 04 0900] 90.663.65.61 21.123.31.100

code:

file.csv | awk -F\" '{print $2,$6,$8}' | sort | uniq -c | sort -n
warezers
  • 1
  • can you share few more lines in examples data? – Siva Jan 23 '19 at 07:28
  • 1
    You probably want uniq -u? Also, it's not clear from your question what you're trying to accomplish. So you want to see unique IPs and unique dates? – Panki Jan 23 '19 at 07:28
  • yes Unique IP address with date Because the seconds in that date format are different so cant get uniq values – warezers Jan 23 '19 at 07:36

4 Answers4

1

Try this,

 awk -F '[:"[]' '{print $3" "$10" "$12}' file.csv | sort | uniq
Siva
  • 9,077
  • That's better. but you should include the commands that were first there. uniq -c | sort -n. – jayooin Jan 23 '19 at 07:45
  • @jayooin does it required? I hope...OP's requirement is to get the unique value rather than its count... – Siva Jan 23 '19 at 08:12
1

You should use sed to complete your request.

Here is a command that should work for your case : 

 cat file.csv | awk -F\" '{print $2,$6,$8}' | sed 's#\(:[[:digit:]]\{2\}\)\{3\} +0900##' | sort | uniq -c | sort -n

It will remove the date to keep only this format : [01/DEC/2011] 90.663.65.61 21.123.31.100.

jayooin
  • 409
  • 2
  • 8
1

As your data seems to be in CSV format you might be able to use csvsql from csvkit, see https://csvkit.readthedocs.io/en/1.0.3/scripts/csvsql.html#

Assuming your file is named data.csv

csvsql -H --query 'SELECT a,c,d FROM data GROUP BY c,d' data.csv

prints

a,c,d
[02/Dec/2011:24:33:04 +0900] ,20.663.65.61,2.123.91.100
[01/Dec/2011:22:20:01 +0900] ,90.663.65.61,21.123.31.100

See also https://unix.stackexchange.com/a/495010/330217

Bodo
  • 6,068
  • 17
  • 27
1

I always recommend using a CSV parser for CSV data. Here's ruby:

ruby -rcsv -ne 'CSV.parse($_) do |row|
  puts [row[0][1..11].upcase, row[2], row[3]].join " "
end' | sort -u

01/DEC/2011 90.663.65.61 21.123.31.100
02/DEC/2011 20.663.65.61 2.123.91.100
glenn jackman
  • 85,964