Splunk Search
Highlighted

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

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
Highlighted

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

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
Highlighted

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

Path Finder

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

0 Karma