Reporting

No matching events on my pivot builder

shrout1
Explorer

My problem is nearly identical to the issue listed in this past post (https://answers.splunk.com/answers/508577/pivot-not-showing-results-even-though-sampling-the.html) (not enough Karma to post links yet). While my search returns millions of results and I have integrated this search into my dataset, I cannot seem to get the pivot-editor to show any matching events. "Sampling" my search confirms that it is valid.

The default column values setting is "Count of events" basically, but it says that there are 0 matches. I can see the server processing my search as it says "0 of 950,000 events matched" etc. etc. until it hits my 2.6 million odd records and simply states "0 events before CURRENT DATETIME"

I have tried changing which column it is using in column values to no avail.

I have set the dataset permissions to "Global" and the lookup table it uses to "Global" as well. I am wondering if the presence of a lookup table in my search is contributing to this problem.

Any help is greatly appreciated and I will provide additional details / samples if required. Only started using Splunk last week so forgive any ignorance on my behalf.

Edit: Sample Data
Update: When I edit the fields in the data set inside of my data model it returns "Values" but no "Events". Given the sample data, why is that?

Here is a table that is the result of joining Rapid7's forward DNS data (the first 10 .com domains in their file) with MaxMind's GeoLite 2 ASN data file:

Domain Name                                IP Address      ASN Range          ASN        ASN Organization
0.220.165.83.static.reverse-mundo-r.com 83.165.220.0    83.165.0.0/16     12334 R Cable y Telecable Telecomunicaciones, S.A.U.
0.220.178.107.bc.googleusercontent.com   107.178.220.0   107.178.192.0/18   15169   GOOGLE
0.220.178.170-dedicated.multacom.com       204.13.152.7 204.13.152.0/22    35916    MULTA-ASN1
0.220.184.35.bc.googleusercontent.com     35.184.220.0  35.184.0.0/13     15169 GOOGLE
0.220.154.104.bc.googleusercontent.com   104.154.220.0   104.154.0.0/15     15169   GOOGLE
0.220.155.104.bc.googleusercontent.com   104.155.220.0   104.154.0.0/15     15169   GOOGLE
0.220.170.108.bc.googleusercontent.com   108.170.220.0   108.170.192.0/18   15169   GOOGLE
0.220.125.34.bc.googleusercontent.com     34.125.220.0    34.125.0.0/16      15169  GOOGLE
0.220.144.82.colo.static.dcvolia.com       82.144.220.0    82.144.192.0/19  25229   Volia
0.220.124.190-isp.enetworksgy.com         190.124.220.0   190.124.220.0/22   52253  E-Networks Inc.

I want to pivot this table on the ASN value first, then other values in other reports. Obviously this sample data is only 10 lines long, my production data has several million lines based on the domain names I have specified.

Here is the search that yields this information:

index="top_10_com_dns" | lookup ASNs network as value OUTPUT network as network, autonomous_system_number as asn, autonomous_system_organization as asn_org | table name, value, network, asn, asn_org

I am using the MaxMind GeoLite 2 ASN data (https://dev.maxmind.com/geoip/geoip2/geolite2/) as a lookup table and checking the IP address from the Rapid7 DNS against the ASN ranges to establish which ASN it is a part of. I can provide samples with formatting of that data if required.

When I put the above data into a pivot it comes back with "0 of 0 events before CURRENT DATETIME"

I hope this sample data makes my problem more clear. Thanks again!

Labels (1)
0 Karma
1 Solution

shrout1
Explorer

Turns out removing the " | table name, value, network, asn, asn_org " portion of my syntax solved the problem with the data not pivoting.

That's nice.

View solution in original post

0 Karma

shrout1
Explorer

Turns out removing the " | table name, value, network, asn, asn_org " portion of my syntax solved the problem with the data not pivoting.

That's nice.

0 Karma

to4kawa
Ultra Champion

what's your data model?

Check Setting >> Data Models

0 Karma

shrout1
Explorer

Hey! I have a data model that includes the same search I put into my data set. I have tried to pivot from the data model as well. Is there something specific I should be doing with my data model in order to get this data pivot-able?

I'm so used to MS Excel, highlight columns, insert pivot table, choose which columns go where. Clearly this is more complex than that and I don't fully grasp the difference. Thanks!

0 Karma

to4kawa
Ultra Champion

I have a data model that includes the same search I put into my data set.
your SPL should be different. really?

0 Karma

shrout1
Explorer

I may be explaining this incorrectly, forgive me. I have created a data model that has my base search in it - I believe that creates the "data set" within the data model itself. I have also created an entirely different data set which includes the same base search. They are two separate entities. I have tried pivoting from within the data model using its data set and also pivoting on the separate data set as well. Both efforts show that the search is returning results but none match.

I will try to create a small set of example data that matches the format of what I am trying to do. Might take me just a little bit. Thank you for taking time out of your day to help me 🙂 it's very much appreciated.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...