Check values exist within two columns

[davidcraven02]

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") 

[micahkemp]

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]

Yeah that makes sense thanks

[micahkemp]

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. 🙂

[davidcraven02]

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

[lguinn2]

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]

The requested information is below.

[micahkemp]

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]

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.