☰

如何使用awk命令从Excel单元格中提取特定单词？

tag-icon • tag-icon awk
如何使用awk命令从Excel单元格中提取特定单词？

我需要有关如何使用 awk 命令从 csv 单元格中提取特定单词的帮助？

文件名：审核日志.csv

栏目：D

{“CreationTime”：“2022-05-24T19：40：38”，“Id”：“034ad6cv-b57c-4d14-8f42-0a312a8c6423”，“操作”：“UserLoggedIn”，“OrganizationId”：“b54f43fe-5553- 34cr-9d67-3ed3b8b1c487","RecordType":15,"ResultStatus":"成功","UserKey":"13f42edc-acc4-2d43-b042-8734d938c23d","UserType":0,"版本":1,"工作负载":"AzureActiveDirectory","ClientIP":"192.223.211.111","ObjectId":"00000021-0322-0ff1-ce00-000000000000","UserId":"[电子邮件受保护]","AzureActiveDirectoryEventType":1,"ExtendedProperties":[{"Name":"ResultStatusDetail","Value":"成功"},{"Name":"UserAgent","Value":"JAS3POPE"},{ "Name":"UserAuthenticationMethod","Value":"1"},{"Name":"RequestType","Value":"OAuth2:Token"}],"ModifiedProperties":[],"Actor":[ {"ID":"034ad6cv-b57c-4d14-8f42-0a312a8c6423","类型":0},{"ID":"[电子邮件受保护]","类型":5}],"ActorContextId":"b45f44de-9876-22cf-9d34-3ed3b8b1c987","ActorIpAddress":"192.223.211.111","InterSystemsId":"b10f76da-cbf3-23a7-86d8-98760854baba ","IntraSystemId":"022ad6ab-b57c-4d09-8f67-0a983a8c8787","SupportTicketId":"","目标":[{"ID":"00000023-3243-0ff1-ce00-000000009887","类型" :0}],"TargetContextId":"b23f34de-9890-23cf-9d34-3ed3b8b1c988","ApplicationId":"00000023-0230-0ff1-ce00-000000000000","DeviceProperties":[{"Name":"BrowserType" ,"值":"其他"},{"名称":"IsCompliantAndManaged","值":"False"},{"名称":"SessionId","值":"b8787err-4afb-9898-bebb- 498f8e9898abb"}],"错误编号":"0"}

我想从这一列中提取的词是 ActorIpAddress:192.223.211.111

有人可以帮助我使用 awk 命令或任何其他工具进行提取吗？所有这些都在一个 D 列中。

请注意：出于隐私考虑，所有详细信息均已更改为随机字符。

提前致谢。 J

答案1

您的审核文件实际上是一个格式良好的json文件，在这种情况下，更合适和更直接的工具将是jq，您可以通过包管理器（即sudo apt install jq）获得它。我将向您提供一个简短的细分，jq以防万一，但如果您只想要该值，那么您可以滚动到底部查看单行。

1. 如何格式化 AuditLog.csvjson文件以获得更好的可读性：

cat AuditLog.csv | jq

上述命令在终端内运行的输出应类似于以下内容（您也应该获得颜色，但如果没有，请在上述命令-C后面附加标志）：jq

{
  "Actor": [
    {
      "ID": "034ad6cv-b57c-4d14-8f42-0a312a8c6423",
      "Type": 0
    },
    {
      "ID": "[email protected]",
      "Type": 5
    }
  ],
  "ActorContextId": "b45f44de-9876-22cf-9d34-3ed3b8b1c987",
  "ActorIpAddress": "192.223.211.111",
  "ApplicationId": "00000023-0230-0ff1-ce00-000000000000",
  "AzureActiveDirectoryEventType": 1,
  "ClientIP": "192.223.211.111",
  "CreationTime": "2022-05-24T19:40:38",
  "DeviceProperties": [
    {
      "Name": "BrowserType",
      "Value": "Other"
    },
    {
      "Name": "IsCompliantAndManaged",
      "Value": "False"
    },
    {
      "Name": "SessionId",
      "Value": "b8787err-4afb-9898-bebb-498f8e9898abb"
    }
  ],
  "ErrorNumber": "0",
  "ExtendedProperties": [
    {
      "Name": "ResultStatusDetail",
      "Value": "Success"
    },
    {
      "Name": "UserAgent",
      "Value": "JAS3POPE"
    },
    {
      "Name": "UserAuthenticationMethod",
      "Value": "1"
    },
    {
      "Name": "RequestType",
      "Value": "OAuth2:Token"
    }
  ],
  "Id": "034ad6cv-b57c-4d14-8f42-0a312a8c6423",
  "InterSystemsId": "b10f76da-cbf3-23a7-86d8-98760854baba",
  "IntraSystemId": "022ad6ab-b57c-4d09-8f67-0a983a8c8787",
  "ModifiedProperties": [],
  "ObjectId": "00000021-0322-0ff1-ce00-000000000000",
  "Operation": "UserLoggedIn",
  "OrganizationId": "b54f43fe-5553-34cr-9d67-3ed3b8b1c487",
  "RecordType": 15,
  "ResultStatus": "Success",
  "SupportTicketId": "",
  "Target": [
    {
      "ID": "00000023-3243-0ff1-ce00-000000009887",
      "Type": 0
    }
  ],
  "TargetContextId": "b23f34de-9890-23cf-9d34-3ed3b8b1c988",
  "UserId": "[email protected]",
  "UserKey": "13f42edc-acc4-2d43-b042-8734d938c23d",
  "UserType": 0,
  "Version": 1,
  "Workload": "AzureActiveDirectory"
}

2. 如何获取所有顶级键的列表：

上一个命令将为您提供结构化且彩色的json输出，但您仍然需要在日志中查找一个特定值时对其进行解析。jq可以轻松地通过键=值对为您提供该值以及任何其他值，即您提供键并将jq为您提供其值。其语法格式如下：

` 猫 [file.json] | jq '.[键].[子键][.subsubkey]'

  • 现在，您通常需要首先查看可用的顶级键列表：
cat AuditLog.csv | jq 'keys'

[
  "Actor",
  "ActorContextId",
  "ActorIpAddress",
  "ApplicationId",
  "AzureActiveDirectoryEventType",
  "ClientIP",
  "CreationTime",
  "DeviceProperties",
  "ErrorNumber",
  "ExtendedProperties",
  "Id",
  "InterSystemsId",
  "IntraSystemId",
  "ModifiedProperties",
  "ObjectId",
  "Operation",
  "OrganizationId",
  "RecordType",
  "ResultStatus",
  "SupportTicketId",
  "Target",
  "TargetContextId",
  "UserId",
  "UserKey",
  "UserType",
  "Version",
  "Workload"
]

3. 最后一步 - 获取给定键对应的值。

一旦您知道密钥的名称，您就可以使用它来获取其存储的值。就您而言，您专门寻找ActorIpAddress(笔记：注意.每当您搜索值时，键名称前面的必需项）：

cat AuditLog.csv | jq '.ActorIpAddress'

"192.223.211.111"

额外：当您需要获得多个值或链下游的值时。

例 1：将“ActorIpAddress、UserId 和 UserKey”放在一起。

 cat stack.csv | jq ".ActorIpAddress, .UserId,.UserKey"
"192.223.211.111"
"[email protected]"
"13f42edc-acc4-2d43-b042-8734d938c23d"

前任。 2：获取用户代理密钥“UserAgent”的值和OAuth匹配值的密钥，并以key=value的格式输出。

 $cat stack.json | jq -C '.ExtendedProperties[] | select(.Name=="UserAgent" or .Value=="OAuth2:Token") | "\(.Name) = \(.Value)"'
"UserAgent = JAS3POPE"
"RequestType = OAuth2:Token"

相关内容