Thanks for your time. Im using splunk to parse the log.
I have two search. the columns i got from A is as below
tktnum, prcnum, type Columns for search B is tktnum, _time.
How may I find tktnum existed in A but not in B then print table like
tktnum(In A not B), prcnum, type
search A with tktnum, prcnum, type | stats values(tktnum) as TKT1, values(prcnum) as PRCNUM, values(ftrectyp) as TYPE | appendcols [search B with tktnum] | stats values(tktnum) as TKT2 ] | makemv TKT1 | makemv TYPE | mvexpand TKT1 | mvexpand TYPE | where not match(TKT2, TKT1) | table TKT1, TYPE
I do get the tktnum which exist in A, not in B. But the problem is TYPE not match. Can anyone help on it.
The type should be corresponding to that tktnum from the original row, but now I got is different.
How about this?
search A with tktnum, prcnum, type | where NOT [search B with tktnum _time | table tktnum] | renamte tktnum as "tktnum(in A not B)"
Based on type of queries for search A, you could actually move the NOT filter to base search of A (search portion before first pipe symbol).
This is pretty straightforward since the results from Search B contain a field that's not present in Search A. Combine the two searches at the outset so that you first gather all results from both searches. Then use the
eventstats command to copy the
_time field from results from Search B to results from Search A with the same
tktnum, and finally filter down to only events that don't contain a
_time field, as these are tickets that had results in Search A but not in Search B.
[ combined Search A and Search B ] | eventstats first(_time) AS _time BY tktnum | where isnull(_time)
I also have a similar requirement of searching results based on a column.
The below is the search results of SPL: -
In the above table, I have idx1 which has 2 host values: hostA, hostB; but, common pair value: Pair1.
rectype is a multi-value field, for hostA, rectype is "index lookup" and for hostB, rectype is "lookup".
I need your help to filter such records where if pair value is same and rectype values are different, along with rectype cell having values "index lookup".
Thus, from the above search output, I need to filter out rows having value: - hostA, hostB, hostC, hostF; and display rows with values with hostD, hostE.
Reason for filtering rows with hostA and hostB: -
-> Pair value is same.
-> rectype values are different.
Reason for filtering out hostC: -
-> rectype value is "index lookup".
Reason for filtering out hostF: -
-> rectype value is "index lookup".
Thus, the expected output after applying filter on above search result is: -
Please help by sharing your inputs.
Give this a try
Your current search with field index host pair rectype | eventstats dc(rectype) as rectypes by index pair | where NOT (rectypes=2 OR (mvcount(rectype)=2 AND isnotnull(mvfind(rectype,"(index|lookup)"))))
Thank you @somesoni2 for sharing your inputs. Your logic worked perfectly with the usecase.
I tried the below as well and it gave me results same as yours: -
|eventstats dc(rectype) as rectypes by index, pair |where NOT (rectypes=2) AND rectype="lookup" AND rectype!="index"
Do you find if I have missed any condition by removing mvcount and isnotnull from the code you shared?
Will following row be included in the result? Will there be any record with just rectype=index?
If no, then your logic is correct. In fact, 2nd condition is (below) is not required. (if rectype=lookup, it'll never be index, so it's a deadcode)
Thank you for your prompt response.
In my case, I do have records with rectype=index.
But, for my current usecase I need to filter out those records, so it seems the code logic is fine.
Thank you for your help.