topic Re: Displaying Blank Using Data Models in Knowledge Management

Displaying Blank Using Data Models

robertlynch2020 — Thu, 30 Mar 2017 13:02:49 GMT

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

Re: Displaying Blank Using Data Models

Masa — Mon, 03 Apr 2017 17:52:13 GMT

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

Re: Displaying Blank Using Data Models

robertlynch2020 — Tue, 04 Apr 2017 13:41:16 GMT

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.

Re: Displaying Blank Using Data Models

Masa — Wed, 05 Apr 2017 21:37:55 GMT

glad to hear that you found a good solution.

Re: Displaying Blank Using Data Models

DavidHourani — Wed, 25 Sep 2024 08:57:42 GMT

I think you're lookin for fillnull_value
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/tstats#Optional_arguments