Knowledge Management

Displaying Blank Using Data Models

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

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

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.

0 Karma

Splunk Employee
Splunk Employee

glad to hear that you found a good solution.

0 Karma