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!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

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