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

[mzn1979]

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.

[bowesmana · Accepted solution]

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

| makeresults
| eval x=split("2020-12-27,101:2020-12-27,102:2020-12-27,201:2020-12-27,202:2020-12-27,203:2020-12-28,101:2020-12-29,101:2020-12-29,102:2020-12-29,103:2020-12-29,201:2020-12-29,202:2020-12-29,203",":")
| mvexpand x
| rex field=x "(?<date>[^,]*),(?<BrokerID>\d+)"
| stats values(BrokerID) as BrokerID by date
| appendcols [
  | inputlookup Brokers.csv | stats values(ID) as IDS
]
| filldown IDS
| mvexpand IDS
| eval Missing=if(isnull(mvfind(BrokerID,IDS)),true(), null())
| where !isnull(Missing)
| stats values(IDS) as IDS by date
| lookup Brokers.csv ID as IDS
| fields - IDS

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.

[mzn1979]

Thank you! It's worked perfectly