Splunk Search

How to edit my search to join results with a lookup table?

chadman
Path Finder

I have a lookup table and have one search working good. I have another search I want to join to the lookup table, but can't figure out the syntax.

This works just how I need it to:

| inputlookup Meeting_Rooms 
| join type=left host [metadata type=hosts] 
| eval age = now()-lastTime | where age > 900 
| convert ctime(lastTime) | eval field_in_ddhhmmss=tostring((age) , "duration") 
| rename field_in_ddhhmmss as "Time Offline" lastTime as "Last Update Time" host as "Serial Number"
| sort + "lastTime" 
| table "Serial Number" "Time Offline" "Last Update Time" Campus Building Room

This works, but I would like to add Campus Building Room to the table at the end of it, but not sure how. If I just add those to the table they come up blank.

sourcetype="sortlog"  earliest=-30m [| inputlookup Meeting_Rooms | fields host]  
| stats avg(cpu) as "CPU Percent" by host | where 'CPU Percent' > 80 | rename host as "Serial Number" 
| eval "CPU Percent" = round('CPU Percent',0) 
| table "Serial Number" "CPU Percent"
0 Karma
1 Solution

sundareshr
Legend

Try this (assuming Campus Building Room is in the lookup file)

sourcetype="sortlog" earliest=-30m [| inputlookup Meeting_Rooms | fields host] 
| stats avg(cpu) as "CPU Percent" by host | where 'CPU Percent' > 80 
| lookup Meeting_Rooms host OUTPUT "Campus Building Room" 
| rename host as "Serial Number" 
| eval "CPU Percent" = round('CPU Percent',0) 
| table "Serial Number" "CPU Percent" "Campus Building Room"

View solution in original post

0 Karma

sundareshr
Legend

Try this (assuming Campus Building Room is in the lookup file)

sourcetype="sortlog" earliest=-30m [| inputlookup Meeting_Rooms | fields host] 
| stats avg(cpu) as "CPU Percent" by host | where 'CPU Percent' > 80 
| lookup Meeting_Rooms host OUTPUT "Campus Building Room" 
| rename host as "Serial Number" 
| eval "CPU Percent" = round('CPU Percent',0) 
| table "Serial Number" "CPU Percent" "Campus Building Room"
0 Karma

chadman
Path Finder

perfect! Campus Building and Room are all separate fields, but removed the quotes and works great. Thanks so much!

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...