My situation is quite simple. I lack the basic training. The ChatGPT showed me the way. Maybe this can be an answer for another Splunk report newbie super user. ”Ah, so you want to deduplicate data and use it in a Pivot table – great clarification! Pivot in Splunk is based on Data Models, and indeed, there are limitations on SPL commands (like | dedup _raw) in that context. 🔍 The Problem: Pivot uses a Data Model, and in the base search of a Data Model, you cannot use pipe (|) commands like dedup. 🎯 Your Goal: Remove duplicates based on _raw and still use the data in a Pivot table. ✅ Solution Options for Use in Pivot: 🔁 1. Create a Saved Search with dedup, then build a Data Model on top of it This is the recommended method: Step 1: Create a Saved Search Go to Splunk’s Search view. Enter the SPL: index=your_index sourcetype=your_sourcetype | dedup _raw Click Save As → Report. Name it, for example: Deduped Raw Events. Step 2: Create a new Data Model based on that Saved Search Go to Settings → Data Models → New Data Model. Give it a name and save it. Add a new Object, and choose: Object Type: Event Constraint: Use the saved search: savedsearch="Deduped Raw Events" NOTE: savedsearch="your_report_name" references the saved search. Step 3: Use Pivot on top of this Data Model Go to Pivot → Select your new Data Model → Deduped Raw Events. Build your table as desired. ⚠️ Notes: This only works if the saved search is public (shared) or you have permission to use it. The Saved Search must return fields that you can use in Pivot (like _time, host, source, custom fields, etc.). 🧪 Option 2: Simulate Dedup within the Data Model (if possible) Data Models do not allow | dedup, but you can: Add an auto-extracted field, which lets you group by that field in Pivot. Or, if you have a unique identifier (e.g., event_id), you can use first-value or latest-value aggregations in Pivot to simulate deduplication. 📌 Summary: Method Dedup Allowed? Usable in Pivot? Saved Search + dedup ✅ ✅ Native Data Model search ❌ ✅ SPL with pipes in Pivot UI ❌ (not allowed) ✅ but very limited If you’d like, I can also help you write the full search or configure it for a specific type of data or log source – just let me know what you’re using it for in Pivot!” The data is very simple event log type data. The amount of data is small. There is a unique field in log lines (event id). The question was about how to tweak existing data set. Splunk is not good for these type of business reports which should be moved to another report platform (ie MSBI).
... View more
