How to edit my tstats search with inputlookup to return additional columns from a CSV file?

[sumitkathpal]

Hi All,

Need your help to refine this search.

Currently in the search, we are using the tstats command along with inputlookup to compare the blacklisted IP's with firewall IP's. Below is the search

| tstats `summariesonly`  dc(All_Traffic.dest) as dest_count from datamodel=Network_Traffic where All_Traffic.src_zone=outside    by All_Traffic.src  | search [| inputlookup commonBlacklistIp.csv | search "Confidence Level"=high| rename Ip as All_Traffic.src | fields All_Traffic.src ]| rename dest_count as "Unique Destination IP" All_Traffic.src as "Blacklisted IP" |  sort -"Unique Destination IP"

This search is working fine and we are getting the output Matched IP along with Count.

Now in our commonBlacklistIp.csv file, there are other fields like Severity and Confidence columns also. Once it matches the file, we need Matched IP , Severity IP , Confidence IP , Count in columns .

Hope you guys understand my question.

Do let me know if you guys have any questions for me.

Thank in advance 🙂

[sundareshr · Accepted solution]

You need the lookup command. Try it like this

 | tstats `summariesonly`  dc(All_Traffic.dest) as dest_count from datamodel=Network_Traffic where All_Traffic.src_zone=outside    by All_Traffic.src  | lookup commonBlacklistIp.csv ip AS "All_Traffic.src" OUTPUT "Matched IP" "Severity IP"  "Confidence Level" | search "Confidence Level"=high | rename dest_count as "Unique Destination IP" All_Traffic.src as "Blacklisted IP" |  sort -"Unique Destination IP"

[sumitkathpal]

Thanks ...................