Splunk Search

How to join rows from a lookup into single event?

kiran331
Builder

Hi
I have a situation: How do I join rows from a lookup file into a single event starting with ComputerName?

ComputerName            ABC
AppName               Service Pack 1 for SQL Server 2012 
AppVersion             11.0
AppVendor               Microsoft Corporation
InstalledDate           20160507
AppGUID               123456
SoftwareArchitecture     x64
ComputerName            XYZ
AppName               Service Pack 1 for SQL Server 2012 
AppVersion             11.0
AppVendor               Microsoft Corporation
InstalledDate           201600102
AppGUID               123456
SoftwareArchitecture    x64
Tags (4)
0 Karma

DalJeanis
Legend

Your question is a bit vague, and/or your data presented doesn't make sense. If you are really trying to create a single event that has multiple values for Computername and all of the other fields, you could do something like this :

| inputlookup yourlookuptable.csv 
| eval junk = 1
| stats list(ComputerName) as ComputerName, list(AppName) as AppName  ... by junk

but I don't see what the use for such an event might be.

Please give an explanation and a simplified example of what you are trying to achieve.


General use of a Lookup

Let's suppose there were events on index=myindex with only the fields ComputerName and _time, and these values

foo   12/27/2016:23:57:00
foo   12/21/2016:18:21:00
bar   12/29/2016:13:17:30

Let's suppose you have a lookup table yourlookuptable.csv -- with the fields ComputerName2, InstalledDate, SoftwareArchitecture, and junk3 (a field we don't need) -- and these values

bar 20160507 x32  blahjunk
foo 20160102 x64  moreblahjunk

The following code looks up the values

index=myindex 
| lookup yourlookuptable.csv ComputerName as ComputerName2 OUTPUT InstalledDate SoftwareArchitecture 
| sort 0 ComputerName InstalledDate _time

And results in

bar   12/29/2016:13:17:30  20160507  x32
foo   12/21/2016:18:21:00  20160102  x64
foo   12/27/2016:23:57:00  20160102  x64

edited to use sort 0 instead of sort, in case there were more than 100 values to sort.

0 Karma

somesoni2
Revered Legend

Try like this (the lookup table has two fields attribute and value)

| inputlookup yourlookuptable.csv | table attribute value
| eval joinfield=if(attribute="Computer Name",1,0)
| accum joinfield | stats list(attribute) as attributes list(value) as values by joinfield
0 Karma

skalliger
Motivator

I guess the transaction command is what you are looking for: https://docs.splunk.com/Documentation/Splunk/6.5.1/SearchReference/Transaction
Depending on which events you want to group together, stats might also be a better use because it's faster.
http://blogs.splunk.com/2012/11/29/book-excerpt-when-to-use-transaction-and-when-to-use-stats/

Edit: TSTATS would be even faster than stats, but no raw data then, if you need it.

0 Karma

kiran331
Builder

the above results are from lookup file. is there a way to do this in lookup?

0 Karma
Get Updates on the Splunk Community!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...