Splunk Search

Check values exist within two columns

davidcraven02
Communicator

I tried to apply this logic as I want to check if the values from con_splunkUL exists within con_UL, but for me it seems its checking for a direct match between both fields rather than checking for a match within the whole data set.

| eval MonitoringStatus = if(like(con_splunkUL,"%".con_UL."%"), "Monitored", "Not Monitored") 

alt text

0 Karma

micahkemp
Champion

I think you could accomplish this more easily without concatenating the drive and machine name, and without the join.

Consider something like:

(index=windows host=*nas* source=WMI:Shares) OR (source="otl_varonis_monitoring.csv" host="opspksh01d.options-it.com" sourcetype="csv" type=Production)
| eval machine=if(source="WMI:Shares", lower(host), machine)
| eval drive=if(source="WMI:Shares", Path, drive)
| stats values(source) AS sources BY machine drive
| eval MonitoringStatus=if(match(sources, "otl_varonis_monitoring.csv"), "Monitored", "Not Monitored")

As a run anywhere example:

| makeresults | eval host="host1", Path="a", source="WMI:Shares"
| append [|makeresults | eval host="host1", Path="b", source="WMI:Shares"]
| append [|makeresults | eval host="host1", Path="c", source="WMI:Shares"]
| append [|makeresults | eval host="host2", Path="a", source="WMI:Shares"]
| append [|makeresults | eval machine="host1", drive="a", source="otl_varonis_monitoring.csv"]
| append [|makeresults | eval machine="host1", drive="b", source="otl_varonis_monitoring.csv"]
| append [|makeresults | eval machine="host2", drive="a", source="otl_varonis_monitoring.csv"]
| eval machine=if(source="WMI:Shares", lower(host), machine)
| eval drive=if(source="WMI:Shares", Path, drive)
| stats values(source) AS sources BY machine drive
| eval MonitoringStatus=if(match(sources, "otl_varonis_monitoring.csv"), "Monitored", "Not Monitored")

davidcraven02
Communicator

Yeah that makes sense thanks

0 Karma

micahkemp
Champion

If you have tested this method and it works, please accept the answer so that others may more quickly find the solution.

If not, please disregard this comment. 🙂

0 Karma

davidcraven02
Communicator

So for further context both fields contain concatenations of the FileServer + Drive so that I can easily compare if there are any matches. For each File Server there may be more than 1 drive being monitored.

I added a stats value to store all possible values contained within con_UL to see if any con_splunkUL values exists within con_UL but it still does not work as I think its comparing the full value of con_UL

Below is the full query:

index=windows host=*nas* source=WMI:Shares 
| eval machine=lower(host) 
| eval drive = Path 
| rex field=drive "(?P<Drive>\w+)\:" 
| eval con_splunk=machine. "," .Drive 
| eval con_splunkUL = upper(con_splunk) 
| join type=left machine 
    [ search source="otl_varonis_monitoring.csv" host="opspksh01d.options-it.com" sourcetype="csv" type=Production 
    | eval con=machine. "," .drive 
    | eval con_UL = upper(con) 
    | stats values(con_UL) as con_UL by machine] 
| eval MonitoringStatus = if(like(con_splunkUL,"%".con_UL."%"), "Monitored", "Not Monitored") 
| dedup machine, Path, MonitoringStatus 
| table machine, Path, MonitoringStatus, type , con_splunkUL, con_UL 
| sort +str(type), machine

Sample output: OPNAS02E,E OPNAS02E,F OPNAS02E,G

0 Karma

lguinn2
Legend

Yes, the eval command is applied to each event independently. So you are checking the fields within events, not between events.

If you explain your problem a bit more, the community might be able to help with a solution. What was the complete search that got you to this point?

davidcraven02
Communicator

The requested information is below.

0 Karma

micahkemp
Champion

If you want to compare the components of the strings prior to the comma, that should be fairly easy.

| rex field=con_splunkUL "(?<con_splunkUL_pre>[^,]+)," 
| rex field=con_UL "(?<con_UL_pre>[^,]+)," 
| eval MonitoringStatus=if(con_splunkUL_pre=con_UL_pre, "Monitored", "Not Monitored")

davidcraven02
Communicator

Thank you but I want to basically use con_UL as a lookup containing all values that I want to check against. I added more information below.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...