topic Re: Get a list of ID by date and compare with lookup file and get result not in search in Splunk Search

Get a list of ID by date and compare with lookup file and get result not in search

mzn1979 — Tue, 26 Jan 2021 09:07:26 GMT

Hi everyone

I have a lookupfile that contains a name and an ID

Brokers.csv Name ID Broker1 101 Broker2 102 Broker3 103 Broker4 201 Broker5 202 Broker6 203

I run this search query on my data.

index=SQL | fields BrokerID host | convert timeformat="%Y-%m-%d" ctime(_time) AS date | stats values(BrokerID) by date

and this is my results

date	BrokerID
2020-12-27	101
	102
2020-12-27	201
	202
	203
2020-12-28	101
2020-12-29	101
	102
	103
2020-12-29	201
	202
	203

So What query I should run to get following result?

2020-12-27	Broker3
2020-12-28	Broker2
	Broker3
	Broker4
	Broker5
	Broker6

Thanks in advance.

Re: Get a list of ID by date and compare with lookup file and get result not in search

bowesmana — Tue, 26 Jan 2021 21:10:28 GMT

@mzn1979

It's a bit fiddly, which is often the case when proving negatives, but what this is doing is appending the entire Brokers.csv file to a new column for _every_ row in the results set. See the stats values(BrokerID) command down for the solution. Up to there, it's just setting up your example data.

appendcols is doing the adding of all the brokers to a new column called IDS in the first result set row and then filldown will copy that column to all subsequent rows.

Then it's just a matter of expanding out that data and effectively doing a lookup of itself for each row to see which ones are missing.

Hope this helps.

Re: Get a list of ID by date and compare with lookup file and get result not in search

mzn1979 — Wed, 27 Jan 2021 12:55:25 GMT

Thank you! It's worked perfectly