Knowledge Management

Displaying Blank Using Data Models

robertlynch2020
Motivator

Hi

I have data that looks like below, as you can see some parts have blanks.

  Date  |    Time    | UserName |iD     |           Context            |           Command            
20161209|17:28:55.238|MUREXFO   |     1 |LOGIN                         |SPBActUserLogin              
20161209|17:29:02.456|MUREXFO   |     1 |                              |Login                       
20161209|17:29:28.555|MUREXFO   |     2 |Report Selection              |                            
20161209|17:29:32.344|MUREXFO   |     3 |Report Selection NAME         |&Open                     
20161209|17:29:33.404|MUREXFO   |     4 |Creation INFO                 |&Open                    
20161209|17:29:35.966|MUREXFO   |     5 |ADT_OBJDSP                    |                         
20161209|17:29:38.907|MUREXFO   |     6 |Scenario details              |Open

I am able to work whit these with a normal Query, however when i use Data Models i cant.

Below Query works as i can replace blank with NULL and that is fine - I get 10 entries and it displays NULL

index=mlc_log_drop host="mxtiming_qc3"  source="/net/dell425srv/dell425srv/apps/SPLUNK_FILES/MXTIMING_QC3/Resources/logs/MXTIMING/mxtiming_small.log" | fillnull value=NULL |dedup Context |table Context

However i cant seem to do this with DataModels, I only get 9 entries as NULL is not displayed. I am not sure how to add this at the DataModel level.

| tstats count(MXTIMING.CPU) AS count FROM datamodel=MXTIMING where  source="/net/dell425srv/dell425srv/apps/SPLUNK_FILES/MXTIMING_QC3/Resources/logs/MXTIMING/mxtiming_small.log" groupby MXTIMING.Context

We can see in the image i get 9 results in the tstats and 10 in the normal with NULL
alt text

alt text

Masa
Splunk Employee
Splunk Employee

tstats groupby is similar to "stats split-by". So, if by field is null, you cannot populate result for null field.
So, you need to find a field or combination of fields for groupby.

I'm not sure if the following search works in your case...but, here is a tstats search example.

| tstats values(MXTIMING.Context) as Context 
         FROM datamodel=MXTIMING 
         where source="*/mxtiming_small.log" 
         groupby MXTIMING.Date MXTIMING.Time MXTIMING.UserName 
         prestats=t
| fillnull value=NULL 
| stats count by Context
0 Karma

robertlynch2020
Motivator

Thanks for this.

I put this in but the performance was a bit slow over Millions of lines as i was doing a lot of calculations after the datamodel.

In fact you gave me a great idea.

Soooooo. I changed the datamodel to have Context=if(isnull(Context),"NULL",Context). This worked great as now it has NULL at the datamodel level and i can now get all the data i need with the original query.

Masa
Splunk Employee
Splunk Employee

glad to hear that you found a good solution.

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