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

[Mrig342]

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

[PickleRick · Accepted solution]

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]

Thank you @PickleRick ..!!

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

[nadlurinadluri]

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"