How to create a search check on a single day which a user had done transactions for more than 3 different vendors?

[splkjk]

Hello Splunkers, @SPL , Was working on some of the development activity, got stuck at some level.

We have a scenario where I need to check , on a single day which a user had done  transactions for more than 3 different vendors

Date	User ID	Vendor	Transactions
10/5/2021	user 1	SAAS (User1)	$$$$$
10/5/2021	user 2	PAAS (User1)	$$$$$
10/7/2021	user 3	IAAS	$$$$$
10/8/2021	user 4	AAA	$$$$$
10/9/2021	user 5	CCCC	$$$$$
10/10/2021	user 6	FFFF	$$$$$
10/5/2021	user 7	XXXX (User1)	$$$$$
10/6/2021	user 8	ZZZZ	$$$$$
10/8/2021	user 9	EEE	$$$$$
10/9/2021	user 10	QQQQ	$$$$$
10/10/2021	user 11	SSSS	$$$$$
10/11/2021	user 12	PPPP	$$$$$
10/12/2021	user 13	WWW	$$$$$

[gcusello · Accepted solution]

Hi @splkjk,

you could use the stats command, something like this:

(supponing that you already extracted the fields I'm using, otherwise you have to extract them)

index=your_index
| stats dc(Vendor) AS dc_vendor values(Vendor) AS Vendor BY User_ID
| rename User_ID As "User ID"

if possible don't use field names with spaces, you can rename fields at the end of the search.

Ciao.

Giuseppe

[splkjk]

Thanks @gcusello , i was able to get the distinct count for the User ID who had done transactions with different vendor on same day, but after extracting i need to get the table in below format.
When i add the below query 

"table Date User_ID Vendor Transaction" the table gets populated for only User ID and Vendor, date and Transactions are not getting populated

Regards

Date

User ID

Vendor

Transactions

10/5/2021	user 1	SAAS(User 1)	$$$$$
10/5/2021	PAAS(User1)
10/5/2021	XXXX (User1)	$$$$$

[gcusello]

Hi @splkjk,

you have to add some option to the stats command, something like this:

index=your_index
| stats 
     dc(Vendor) AS dc_vendor 
     values(Vendor) AS Vendor 
     earliest(_time) AS Date
     values(Transaction) AS Transaction
     BY User_ID
| rename User_ID As "User ID"
| eval Date=strftime(Date,"%Y-%m-%d %H:%M:%S")
| table Date User_ID Vendor Transaction

Ciao.

Giuseppe

P.S.: Karma Points are appreciated 😉