Creating a look up table

Follow

Nitesh Joshi

• June 06, 2018 21:37

I want to create a lookup table and save data. I am feeding Sumo with a csv of 2 columnns in which one column is IP and other is server name, I want to create a lookup which will have one to one mapping of ip address and the server name.

 

I have written the following regex which gives me this output:

_sourceCategory="RC/Staging/Linux/LookUpFile"
| parse regex "(?<server>.*;)" multi
| parse regex "(?<ip_address>\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})" multi

OUTPUT:

IN-Dev:Reporting  1.1.1.1
IN-Dev:Reporting  2.2.2.2
IN-Dev:Reporting  3.3.3.3
IN-Dev:Reporting  4.4.4.4
IN-Dev:Reporting  5.5.5.5

EXPECTED:

IN-Dev:Reporting  1.1.1.1
EX-Dev:Reporting  2.2.2.2
IN-Dev:SVN  3.3.3.3
EX-Dev:SVN  4.4.4.4
IN-DomainController  5.5.5.5

Please help me with the regex.   Thanks

0

• Official comment

  

  Raghu Murthy

  • June 06, 2018 23:08

  Hi Nitesh,

  You can do this easily using the save and lookup operators

  1. Once you run a query to find the data for example

  | count Src_ip, Src_port, Dest_ip, Dest_port

  https://help.sumologic.com/Search/Search-Query-Language/Search-Operators/save#Saving_files_to_a_shared_location

  then you can add
  To save a file to a shared location:

  Include the following at the end of your query:
  ...save /shared/myFolder/mySubFolder/fileName

   

  
  You can verify that the data is saved by running
  cat /shared/myFolder/mySubFolder/fileName

  Example query

  _sourceCategory = AWS/VPC
  | json "message"
  | parse field=message "* * * * * * * * * * * * * *" as version,accountID,interfaceID,src_ip,dest_ip,src_port,dest_port,Protocol,Packets,bytes,StartSample,EndSample,Action,status
  | count by src_ip,src_port, dest_ip, dest_port
  You can append the line to bottom of above query to create a saved file
  | save /shared/nitesh/ip_list

  Then you can say
  cat /shared/nitesh/ip_list
  to display the results

  You can configure this query as a scheduled search to overwrite the results to shared file

  2. To access the saved file via a lookup operator refer this doc
  https://help.sumologic.com/Search/Search-Query-Language/Search-Operators/lookup

  ------------------------from Doc----------

  Examples
  Type the lookup operator in the Search tab, just as you would any other operator.

  To match the userID string with a users' ID in your CSV, your query could be:

  * | parse "name=*, phone number=*," as (name, phone)
  | lookup email from https://company.com/userTable.csv on name=userName, phone=cell

  ------------------------------

  Hope that helps

  Regards
  Raghu

  Permalink

Please sign in to leave a comment.