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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...