Splunk Search

How to join two searches based on 1 common field

JoshuaJohn
Contributor

Not sure why this isn't working!

index=NitroLogs " location="Store 0060 fastlane" WiFiMAC=00-16-E4-12-9B-4B Model=* OSVer=* FWVer=* |rename WiFiMac as hpp.macaddress
 | join type=left hpp.macaddress
    [search index=virtue_slate hpp.locId=0060 hpp.macaddress=00-16-E4-12-9B-4B "hpp.deviceModel"=* "hpp.firmwareVersion"=*  
    | eval indextime=strftime(_indextime,"%Y-%m-%d %H:%M:%S") 
    | eval Indextime =strptime(indextime,"%Y-%m-%d %H:%M:%S.%N") 
    | eval Time =strptime(_time,"%s") 
    | eval Minutes_Diff = round((Indextime - Time)/60,2) 
    | stats avg(Minutes_Diff) as Avg_Minutes_Diff stdev(Minutes_Diff) as StDev_Minutes_Diff min(Minutes_Diff) as Min_Minutes_Diff max(Minutes_Diff) as Max_Minutes_Diff count as count by hpp.macaddress hpp.locId hpp.deviceModel hpp.firmwareVersion 
    | eval Avg_Minutes_Diff = round(Avg_Minutes_Diff,2) 
    | rename count as "Sample Size" 
    | table hpp.macaddress Avg_Minutes_Diff] 


| table "location","hpp.macaddress", "Avg_Minutes_Diff","ResourceID","DeviceName","IPAddress","Model","OSVer","FWVer" 

Avg_Minutes_Diff keeps appearing blank, along with hpp.macaddress

Tags (2)
0 Karma

somesoni2
Revered Legend

Give this a try

index=NitroLogs " location="Store 0060 fastlane" WiFiMAC=00-16-E4-12-9B-4B Model=* OSVer=* FWVer=* |rename WiFiMac as hpp.macaddress
  | join type=left hpp.macaddress
     [search index=virtue_slate hpp.locId=0060 hpp.macaddress=00-16-E4-12-9B-4B "hpp.deviceModel"=* "hpp.firmwareVersion"=*  
     | eval Minutes_Diff = round((_indextime-_time)/60,2) 
     | stats avg(Minutes_Diff) as Avg_Minutes_Diff stdev(Minutes_Diff) as StDev_Minutes_Diff min(Minutes_Diff) as Min_Minutes_Diff max(Minutes_Diff) as Max_Minutes_Diff count as count by hpp.macaddress hpp.locId hpp.deviceModel hpp.firmwareVersion 
     | eval Avg_Minutes_Diff = round(Avg_Minutes_Diff,2) 
     | rename count as "Sample Size" 
     | table hpp.macaddress Avg_Minutes_Diff] 
 | table "location","hpp.macaddress", "Avg_Minutes_Diff","ResourceID","DeviceName","IPAddress","Model","OSVer","FWVer" 

If it doesn't work, can you confirm if your subsearch returns results on it's own (run it separately)?

0 Karma

livehybrid
Builder

Just out of interested, have you tried putting the hpp.macaddress=00-16-E4-12-9B-4B as hpp.macaddress="00-16-E4-12-9B-4B"
(e.g. with quotes)

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...