Hello Team @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 user had done transactions for more than 3 different vendors and transfer the output into tabular format.
When i perform the distinct count i get the count of the user who had done transaction with 3 vendors on same day
| stats dc(Vendor) AS dc_vendor values(Vendor) AS Vendor BY UserID
Need to have output detailed in table 2
Table 1:-
Date | UserID | Vendor | Transactions |
10/5/2021 | user 1 | SAAS(User 1) | $$$$$ |
10/5/2021 | user 2 | PAAS(User 1) | $$$$$ |
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 (User 1) | $$$$$ |
10/6/2021 | user 8 | ZZZZ | $$$$$ |
10/8/2021 | user 9 | EEE | $$$$$ |
10/9/2021 | user 10 | QQQQ | $$$$$ |
Output Table 2
Date | UserID | Vendor | Transactions |
10/5/2021 | user 1 | SAAS(User 1) | $$$$$ |
AAS(User 1) | $$$$$ | ||
XXXX (User 1) | $$$$$ |
Hi @splkjk,
you have to join the values of Vendor and Transaction before stats and separe them afte, something like this:
| eval Vendor_Transaction=vendor."|".Transaction
| stats dc(Vendor) AS dc_vendor values(Vendor_Transaction) AS Vendor_Transaction BY UserID
| where dc_Vendor>3
| mvexpand Vendor_Transaction
| rex field=Vendor_Transaction "^(?<Vendor>[^\|]+)*|(?<Transaction>.*)"
| table UserID Vendor Transaction
Ciao.
Giuseppe
Thanks @gcusello for the inputs, but i should have been more specific. I'm new to Splunk and trying to explore the options.
My output table shouldn't contain Transaction amount, just the date, UserID and Vendor
Date | UserID | Vendor |
10/5/2021 | user 1 | SAAS(User 1) |
AAS(User 1) | ||
XXXX (User 1) |
Hi @splkjk,
in this case you can use the first halp of my search:
your_search
| stats dc(Vendor) AS dc_vendor values(Vendor) AS Vendor earliest(_time) AS Date BY UserID
| where dc_Vendor>3
| eval Date=strftime(earliest,"%d-%m-%Y")
| table Date UserID Vendor
Ciao.
Giuseppe