Splunk Search
Highlighted

Check values exist within two columns

Communicator

I tried to apply this logic as I want to check if the values from consplunkUL exists within conUL, 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
Highlighted

Re: Check values exist within two columns

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

Re: Check values exist within two columns

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
Highlighted

Re: Check values exist within two columns

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?

Highlighted

Re: Check values exist within two columns

Communicator

The requested information is below.

0 Karma
Highlighted

Re: Check values exist within two columns

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 conUL to see if any consplunkUL values exists within conUL but it still does not work as I think its comparing the full value of conUL

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
Highlighted

Re: Check values exist within two columns

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

Re: Check values exist within two columns

Communicator

Yeah that makes sense thanks

0 Karma
Highlighted

Re: Check values exist within two columns

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
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.