topic Re: Comparing variables in a table in Splunk Search

Comparing variables in a table

JoshuaJohn — Tue, 04 Apr 2017 19:53:56 GMT

I want to create a search that runs through a variable that contains many mac addresses that correspond to a specific store number, then compare it to another variable that has mac addresses that correspond to a specific store number from a different source but many of them should be identical. I want them to show up in rows that would like:

Store #----SCCM Store #------Mac Address-----SCCM Mac Address
1500----------1500-------------10:20:15:02:01-----10:20:15:02:01

Likewise when it doesn't match

Store #----SCCM Store #------Mac Address-----SCCM Mac Address
1500----------1200-------------10:20:15:02:01-----10:20:15:02:01

|inputlookup rnddata.csv |rename "Store #" as Store_Number|rename mac as Mac_Address 
| stats values(Mac_Address) as Mac_Address   values("SCCM Store") as "SCCM Store" by Store_Number SCCM_MAC_ADDRESS
| sort "SCCM Store" desc 
|table "SCCM Store" Store_Number Store_Desc Mac_Address SCCM_MAC_ADDRESS

Right now when I do this without sccm data it works perfectly and will show me macs in a specific store_number but when trying the compare the numbers are all off

Re: Comparing variables in a table

somesoni2 — Tue, 04 Apr 2017 20:06:02 GMT

Can you provide some sample data from your rnddata.csv lookup?

Re: Comparing variables in a table

DalJeanis — Tue, 04 Apr 2017 20:59:24 GMT

What is the format of each of your two data sources? In other words, what fields are available from each of them?

Re: Comparing variables in a table

DalJeanis — Tue, 04 Apr 2017 21:20:11 GMT

I'm assuming you have two different data sources, which I've called rnddata.csv and sccmdata.csv. I've assumed that the field names you are renaming in your code come from rnddata.csv, and that there are other field names to be renamed from the other file. Fill in the field names and correct the file names as appropriate.

| inputlookup rnddata.csv  
| rename "Store #" as StoreRND 
| rename mac as MacAddressRND  
| table StoreRND MacAddressRND 
| eval Store=StoreRND 
| eval MacAddress=MacAddressRND 
| append 
    [ | inputlookup sccmdata.csv 
    | rename xxxxx as StoreSCCM 
    | rename xxxxx as MacAddressSCCM 
    | table StoreSCCM MacAddressSCCM 
    | eval Store=StoreSCCM 
    | eval MacAddress=MacAddressSCCM ]
| eval type="detail"
| appendpipe 
    [| where type="detail" | stats values(*) as * by Store]
| appendpipe 
    [| where type="detail" | stats values(*) as * by MacAddress]
| where mvcount(Store)>1 OR mvcount(MacAddress)>1

This will give you records for each mac that is assigned to multiple stores, and for each store that is assigned to multiple macs, and you'll be able to see which file contains which values.

Re: Comparing variables in a table

JoshuaJohn — Tue, 04 Apr 2017 21:21:43 GMT

Re: Comparing variables in a table

JoshuaJohn — Tue, 04 Apr 2017 21:22:18 GMT

Its all one file, not two data sources that was my bad...need more caffeine

Re: Comparing variables in a table

DalJeanis — Thu, 06 Apr 2017 14:55:41 GMT

This makes a single file of test data with a rectype (RND or SCCM) and values for store and mac...

| makeresults 
| eval mydata="RND,1500,10:20:15:02:01 SCCM,1200,10:20:15:02:01 RND,1501,10:21:15:02:01 SCCM,1501,10:21:15:02:01 RND,1502,10:22:15:02:01 SCCM,1502,10:22:15:02:02 RND,1503,10:23:15:02:01 SCCM,1503,10:23:15:02:01" 
| makemv mydata | mvexpand mydata 
| rex field=mydata max_match=0 "(?<rectype>[^,\s]+),(?<store>[^,\s]+),(?<mac>[^,\s]+)"
| table rectype mac store

This breaks it out into specific fields for what type of data it is

| eval macfield="Mac".rectype 
| eval storefield="Store".rectype 
| eval {macfield}=mac 
| eval {storefield} = store 
| eval type="detail" 
| table mac store MacRND StoreRND MacSCCM StoreSCCM

This produces consolidated records for each mac and for each store to determine if it has been connected with more than one of the other thing...

| stats values(*) as * by mac store 
| eval type ="detail" 
| appendpipe 
    [| where type="detail" | stats values(*) as * by store | eval type ="DupMacByStore"]  
| appendpipe 
    [| where type="detail" | stats values(*) as * by mac | eval type ="DupStoreByMac"] 
| where mvcount(store)>1 OR mvcount(mac) > 1

...resulting in this...

mac              store            MacRND           MacSCCM          StoreRND         StoreSCCM        type             

10:22:15:02:01   1502             10:22:15:02:01   10:22:15:02:02   1502             1502             DupMacByStore    
10:22:15:02:02                                                                                                         

10:20:15:02:01   1200             10:20:15:02:01   10:20:15:02:01   1500             1200             DupStoreByMac    
                 1500