Splunk Search

How to compare logs from two different sources and create a table out of it?

Mrig342
Contributor

Hi All,

I have two set of logs in two different sources in splunk, one containing the predefined list of VPNs and Queues and the other containing list of VPNs and Queues where activity is done. Now I have to compare the list of the 2nd set with the 1st set and create a table.

Set 1:

log: 

10.96.195.70/SEMP/v2/monitor/msgVpns/CPGC_S_SIT/queues/gcg.apac.eventcloud.publish.hk.twa/txFlows
10.96.195.70/SEMP/v2/monitor/msgVpns/EHEM_S_SIT/queues/gcg.emea.eventcloud.publish.ae.cops/txFlows
10.96.195.70/SEMP/v2/monitor/msgVpns/EHEM_S_SIT/queues/gcg.emea.eventcloud.publish.ae.sendprioritycommandretrycomm/txFlows
10.96.195.70/SEMP/v2/monitor/msgVpns/EHEM_S_SIT/queues/gcg.emea.eventcloud.publish.ae.triggerbatchtocommhub/txFlows
10.96.195.70/SEMP/v2/monitor/msgVpns/ALERTSGC_S_SIT/queues/gcg.ap.cop_163124.card.lightning.eas.queue

So I created the below query to get the VPN & Queue:

*** | source="*/sit_solace_updated.txt" | rex field=_raw max_match=0 "msgVpns\/(?P<VPN_Name>[^\/]+)\/queues\/(?P<Queue_Name>[^\/]+)\/" | table VPN_Name,Queue_Name | eval row=mvrange(0,mvcount(VPN_Name)) | mvexpand row
| foreach *_*
[| eval <<FIELD>>=mvindex(<<FIELD>>,row)]
| fields - row

Set 2:

log1:  "activationTime":1652444666, "clientName":"source.solace.emea.ae.custmgt.cops.cops.raw.int.rawevent-05d1d1b@gtgcb-csrla01s.nam.nsroot.net/59133/#00a00005/VIzOONZsrL", "msgVpnName":"EHEM_S_SIT", "queueName":"gcg.emea.eventcloud.publish.ae.cops",

log2: "activationTime":1650620233"clientName":"BW-ALERTS_JMS_CONN-queue-ALERTSServices-1-1-AlertServices_ALERTSServices_a37s_01", "msgVpnName":"ALERTSGC_S_SIT", "queueName":"gcg.ap.cop_163124.card.lightning.eas.queue",

And here I used the below query to create a table:

*** | source="*/final_sol_queue.txt" | rex field=_raw "Time\"\:(?P<Act_Time>[^\,]+)\," | rex field=_raw "clientName\"\:\"(?P<Client_Name>[^\"]+)\"\," | rex field=_raw "VpnName\"\:\"(?P<VPN_Name>[^\"]+)\"\," | rex field=_raw "queueName\"\:\"(?P<Queue_Name>[^\"]+)\"\," | eval Activation_Time=strftime(Act_Time,"%a, %d %b %Y %H:%M:%S") | table Activation_Time,Client_Name,VPN_Name,Queue_Name | dedup Activation_Time,Client_Name,VPN_Name,Queue_Name

it gave the below table:

Activation_Time Client_Name VPN_Name Queue_Name
Fri, 22 Apr 2022 15:28:39 BW-ALERTS_JMS_CONN-queue-ALERTSServices-1-1-AlertServices_ALERTSServices_a37s_01 ALERTSGC_S_SIT gcg.ap.cop_163124.card.lightning.eas.queue
Fri, 13 May 2022 17:54:26 source.solace.emea.ae.custmgt.cops.cops.raw.int.rawevent-05d1d1b@gtgcb-csrla01s.nam.nsroot.net/59133/#00a00005/VIzOONZsrL EHEM_S_SIT gcg.emea.eventcloud.publish.ae.cops

However, I want to create a table that contains the missing VPNs & Queues also from set1 as below:

Activation_Time Client_Name VPN_Name Queue_Name
Fri, 22 Apr 2022 15:28:39 BW-ALERTS_JMS_CONN-queue-ALERTSServices-1-1-AlertServices_ALERTSServices_a37s_01 ALERTSGC_S_SIT gcg.ap.cop_163124.card.lightning.eas.queue
Fri, 13 May 2022 17:54:26 source.solace.emea.ae.custmgt.cops.cops.raw.int.rawevent-05d1d1b@gtgcb-csrla01s.nam.nsroot.net/59133/#00a00005/VIzOONZsrL EHEM_S_SIT gcg.emea.eventcloud.publish.ae.cops
Not_Available Not_Available EHEM_S_SIT gcg.emea.eventcloud.publish.ae.triggerbatchtocommhub
Not_Available Not_Available EHEM_S_SIT gcg.emea.eventcloud.publish.ae.sendprioritycommandretrycomm
Not_Available Not_Available CPGC_S_SIT gcg.apac.eventcloud.publish.hk.twa

 

Please help to modify the query in a way that it compares set1 and set2 for the VPN & Queue values and produce the table in the above manner.

Thank you All..!!

Labels (4)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

Firstly a small but important technical thing - if you post some code or eveng samples, do it within a code box or using the "preformatted" style. It prevents the code from being wrapped around or mutilated in other ways by the browser and the forum mechanics.

But to the point - instead of using join (which would probably work), you can simply use stats to get values of various fields groupped by another field(s). If there are no fields to be matched you'd simply get empty cell there.

So extract fields and/or rename them so that you have all those fields you need in the table and do a

| stats values(Activation_Time) as Activation_Time values(Client_Name) as Client_Name by VPN_Name Queue_Name

You can prepare data for statsing by using append (but append has its limitations) or by conditional evaluation or different regexes matching/not matching your events.

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

Firstly a small but important technical thing - if you post some code or eveng samples, do it within a code box or using the "preformatted" style. It prevents the code from being wrapped around or mutilated in other ways by the browser and the forum mechanics.

But to the point - instead of using join (which would probably work), you can simply use stats to get values of various fields groupped by another field(s). If there are no fields to be matched you'd simply get empty cell there.

So extract fields and/or rename them so that you have all those fields you need in the table and do a

| stats values(Activation_Time) as Activation_Time values(Client_Name) as Client_Name by VPN_Name Queue_Name

You can prepare data for statsing by using append (but append has its limitations) or by conditional evaluation or different regexes matching/not matching your events.

Mrig342
Contributor

Thank you @PickleRick ..!!

Using stats values() command worked for me and I am able to get the desired output.

0 Karma

nadlurinadluri
Communicator

You can use the below format to achieve the same - 

 

$firsttable with all the VPN Queue data avaialble (bigger dataset)$ 
|table VPN Queue
|join type=left VPN Queue
[ $Secondtable with only the VPN Queue data where the activity is done$ 
| table VPN Queue Client_Name ActivationTime
]
| fillnull value="Not_Available"

 

Example query - 

 

| makeresults 
| eval VPNQueue="vpn1,queue1;vpn2,queue2;vpn3,queue3;vpn4,queue4" 
| makemv delim=";" VPNQueue 
| mvexpand VPNQueue 
| eval VPN=mvindex(split(VPNQueue,","),0) 
| eval Queue=mvindex(split(VPNQueue,","),1) 
| table VPN Queue 
| join type=left VPN Queue
    [| makeresults 
    | eval VPNQueue="vpn1,queue1;vpn2,queue2" 
    | makemv delim=";" VPNQueue 
    | mvexpand VPNQueue 
    | eval VPN=mvindex(split(VPNQueue,","),0) 
    | eval Queue=mvindex(split(VPNQueue,","),1) 
    | eval Client_Name=case(VPN="vpn1","client1",VPN="vpn2","client2") 
    | eval ActivationTime=case(VPN="vpn1","01-01-2021",VPN="vpn2","01-01-2022") 
    | table VPN Queue Client_Name ActivationTime
        ]
| fillnull value="Not_Available"

 

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 ...