-1

I am trying to replace a string in a text file that is a CSV file. The row delimiter string is {"id": and want to insert a new line before each occurrence of it as the CSV file appears only as one row and all columns.

Ideally I need the file to be separated by commas translating to columns, and everywhere a {"id":" occurs to translate to a new line i.e. new row

each column should be delimited by "*":, where * indicates any text e.g.: "TLP": or "id":

A sample of the file is below, and the sample text should result in 3 rows and a column for each label

Sorry for a painful question, however I'ave tried every combination of sed and awk I can think of and nothing works

{"id":"5863ddde2577f521dccd9a3a","name":"Switcher: Android joins the attack-the-router club","description":"Recently, in our never-ending quest to protect the world from malware, we found a misbehaving Android trojan. Although malware targeting the Android OS stopped being a novelty quite some time ago, this trojan is quite unique. Instead of attacking a user, it attacks the Wi-Fi network the user is connected to, or, to be precise, the wireless router that serves the network. The trojan, dubbed Trojan.AndroidOS.Switcher, performs a brute-force password guessing attack on the router’s admin web interface. If the attack succeeds, the malware changes the addresses of the DNS servers in the router’s settings, thereby rerouting all DNS queries from devices in the attacked Wi-Fi network to the servers of the cybercriminals (such an attack is also known as DNS-hijacking). So, let us explain in detail how Switcher performs its brute-force attacks, gets into the routers and undertakes its DNS-hijack.","author_name":"AlienVault","modified":"2016-12-28T15:44:30.187000","created":"2016-12-28T15:44:30.187000","tags":["android","baidu","android","mobile","dns hijack","Trojan.AndroidOS.Switcher","Kaspersky"],"references":["hxxps://securelist.com/blog/mobile/76969/switcher-android-joins-the-attack-the-router-club/"],"revision":1.0,"indicators":[{"content":"","indicator":"acdb7bfebf04affd227c93c97df536cf","description":"","created":"2016-12-28T15:44:31","is_active":1,"title":"","access_reason":"","access_type":"public","access_groups":[],"role":null,"expiration":null,"type":"FileHash-MD5","id":1744766,"observations":1},{"content":"","indicator":"64490fbecefa3fcdacd41995887fe510","description":"","created":"2016-12-28T15:44:31","is_active":1,"title":"","access_reason":"","access_type":"public","access_groups":[],"role":null,"expiration":null,"type":"FileHash-MD5","id":1744767,"observations":1},{"content":"","indicator":"101.200.147.153","description":"","created":"2016-12-28T15:44:31","is_active":1,"title":"","access_reason":"","access_type":"public","access_groups":[],"role":null,"expiration":null,"type":"IPv4","id":1744768,"observations":1},{"content":"","indicator":"112.33.13.11","description":"","created":"2016-12-28T15:44:31","is_active":1,"title":"","access_reason":"","access_type":"public","access_groups":[],"role":null,"expiration":null,"type":"IPv4","id":1744769,"observations":1},{"content":"","indicator":"120.76.249.59","description":"","created":"2016-12-28T15:44:31","is_active":1,"title":"","access_reason":"","access_type":"public","access_groups":[],"role":null,"expiration":null,"type":"IPv4","id":1744770,"observations":1}],"TLP":"green","public":true,"adversary":"","targeted_countries":["China"],"industries":[]},{"id":"585bdcd497316a2db901eaa5","name":"Fancy Bear Tracking of Ukrainian Field Artillery Units","description":"Late in the summer of 2016, CrowdStrike Intelligence analysts began investigating a curious Android Package (APK) named ‘Попр-Д30.apk’ which contained a number of Russian language artifacts that were military in nature. Initial research identified that the filename suggested a relationship to the D-30 122mm towed howitzer, an artillery weapon first manufactured in the Soviet Union in the 1960s but still in use today. In-depth reverse engineering revealed the APK contained an Android variant of X-Agent, the command and control protocol was closely linked to observed Windows variants of X-Agent, and utilized a cryptographic algorithm called RC4 with a very similar 50 byte base key.","author_name":"AlienVault","modified":"2016-12-22T14:03:53.674000","created":"2016-12-22T14:01:56.495000","tags":["apt28","fancy bear","ukraine","military","X-Agent","D-30","crowdstrike"],"references":["hxxps://www.crowdstrike.com/blog/danger-close-fancy-bear-tracking-ukrainian-field-artillery-units/","hxxps://www.crowdstrike.com/wp-content/brochures/FancyBearTracksUkrainianArtillery.pdf"],"revision":2.0,"indicators":[{"content":"","indicator":"69.90.132.215","description":"","created":"2016-12-22T14:01:57","is_active":1,"title":"","access_reason":"","access_type":"public","access_groups":[],"role":"command_and_control","expiration":null,"type":"IPv4","id":1683228,"observations":1},{"content":"","indicator":"6f7523d3019fa190499f327211e01fcb","description":"","created":"2016-12-22T14:01:57","is_active":1,"title":"","access_reason":"","access_type":"public","access_groups":[],"role":null,"expiration":null,"type":"FileHash-MD5","id":1683229,"observations":2}],"TLP":"green","public":true,"adversary":"Fancy Bear","targeted_countries":["Ukraine"],"industries":["defence","military"]},{"id":"585ae32297316a22f301eaa5","name":"Fake Apps Take Advantage of Super Mario Run Release","description":"Earlier this year, we talked about how cybercriminals took advantage of the popularity of Pokemon Go to launch their own malicious apps. As 2016 comes to a close, we observe the same thing happening to another of Nintendo’s game properties: Super Mario.\n\nIn advance of any official release, cybercriminals have already released their own Mario-related apps. Since 2012, we have found more than 9,000 apps using the Mario name on various sources online. About two-thirds of these apps show some kind of malicious behavior, including displaying ads and downloading apps without the user’s consent.","author_name":"AlienVault","modified":"2016-12-21T20:16:34.201000","created":"2016-12-21T20:16:34.201000","tags":["super mario","android","mario","nintendo","google play","malware","trendmicro"],"references":["hxxp://blog.trendmicro.com/trendlabs-security-intelligence/fake-apps-take-advantage-mario-run-release/"],"revision":1.0,"indicators":[{"content":"","indicator":"8373aedc9819ff5dacb0fc1864eeb96adc5210b2","description":"","created":"2016-12-21T20:16:35","is_active":1,"title":"","access_reason":"","access_type":"public","access_groups":[],"role":null,"expiration":null,"type":"FileHash-SHA1","id":1674453,"observations":1},{"content":"","indicator":"4ba312a6eaf79da9036d4228a43f19c611345a5a","description":"","created":"2016-12-21T20:16:35","is_active":1,"title":"","access_reason":"","access_type":"public","access_groups":[],"role":null,"expiration":null,"type":"FileHash-SHA1","id":1674454,"observations":1}],"TLP":"green","public":true,"adversary":"","targeted_countries":[],"industries":[]}]

  • Are you sure this is CSV, it looks more like JSON starting with {'id':. – Anthon Dec 29 '16 at 10:35
  • Use a proper JSON parser like jq – Inian Dec 29 '16 at 10:58
  • 1
    Does the file you retrieved really have a ] at the end, but no matching [ at the start? – JigglyNaga Dec 29 '16 at 14:41
1

As others have commented, that file is JSON, not CSV, and you should really use a dedicated JSON parser for it.

If this needs to run somewhere that a JSON library is unavailable, then splitting the array into three CSV-ish lines is a simple enough problem for a sed script:

s/},{"id":/\
"id":/g        # remove delimiter between records, insert newline
s/^\[\?{//     # remove delimiter before first record
s/}\]\?$//     # remove delimiter after last record
0

Not really an answer: Just a quick jq example:

(After adding a "[" that is missing in the beginning of the input)

we can extract id, author_name, and TLP values with:

$ jq '.[] | .id + ":" + .author_name + ":" + .TLP'   input
"5863ddde2577f521dccd9a3a:AlienVault:green"
"585bdcd497316a2db901eaa5:AlienVault:green"
"585ae32297316a22f301eaa5:AlienVault:green"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.