Problem in parsing Powershell commands

[valleyman]

Hello Community!
I am trying to set up a search to monitor Powershell commands from Windows hosts; specifically, I am starting from:

• an index with the full messages related to PS commands, contained in a field named "Message"
  • (related, for example, to event codes 4101, 800, etc...)
• a .csv file, with the list of commands I would like to monitored, contained in a column named "PS_command".

From these premises, I have already constructed a search that leverages on inputlookup to search the strings from the PS-monitored.csv file to the index field Message, outputting the result in a table, as the following (adding also details from the index: _time, host and EventCode).

index="wineventlog"

| search ( [|inputlookup PS-monitored.csv | eval Message= "*"  + PS_command + "*" | fields Message] )

| table _time host EventCode Message

 This, despite not being the most elegant solution (with the addition of wildcard characters *), is currently working, however I would also like to include the original search field (PS_command column from PS-monitored.csv) to the final table.

I tried to experiment a bit with lookup command, and with join options, without success; does anyone have some suggestions?

Finally, I would like avoid using heavy commands, such as join, if at all possible.

Thanks in advance!

[PickleRick]

index="wineventlog"

| search ( [|inputlookup PS-monitored.csv | eval Message= "*"  + PS_command + "*" | fields Message] )

This makes my teeth itch 😉

But seriously - doing initial search and then piping to another search is... well, simply not elegant. Splunk will optimize it out anyway and treat as it would a single search command so you could just write it as

index="wineventlog" [|inputlookup PS-monitored.csv | eval Message= "*"  + PS_command + "*" | fields Message] 

But that's less important.

More important thing is that you're creating a search with a Message=*something conditions. They will be very, very inefficient since Splunk has to parse every single event to find your matching ones.

Assuming your commands are "whole commands" meaning that if your command is "cmd", you're looking for strings like "whatever cmd whatever" and not "whatevercmd whatever" (notice the space difference), you can limit your search with

index="wineventlog" [ | inputlookup PS-monitored.csv | eval search=PS_command | fields search | format ] [ | inputlookup PS-monitored.csv | eval Message= "*"  + PS_command + "*" | fields Message] 

You could also try to combine those two subsearches into one.

[valleyman]

Hello!
Thanks for the extensive and very useful feedback!

I have had chance to look at my search again, and with the correction suggested, I am now able to highlight correctly the strings I am interested in the Message field of the index, as the following example

I am perhaps missing one final step, that is to add the search field from the following sub-search in the final table, as I understood the format command should add to my query.

[ | inputlookup PS-monitored.csv | eval search=PS_command | fields search | format ]

I tried to look for a newly created field "search", or any new created ones, but couldn't find anything...am I missing something obvious?

Thanks again!

[ITWhisperer]

search, and query are special field names which are removed from the subsearch results i.e. if the subsearch returned

( ( search="value1" ) OR ( search="value2" ) )

it would be added to the main search as

( ( "value1" ) OR ( "value2" ) )

[valleyman]

Hello,

I think I'm not that far, unfortunately I still cannot figure out how to extract field PS_command from the inputlookup, and passing it into the main search, and eventually how to map it to the Message from the index.

Could you please try to built a little more on the answers?

Thanks again!

[ITWhisperer]

What is your full current search?

[ITWhisperer]

If you include the wildcards in your lookup matching field and define the lookup to use WILDCARD matching, you may be able to lookup a field in the lookup when there is a wildcard match. Please share some anonymised events and contents of your lookup so we can see the sorts of things you are trying to match.