topic Re: how to set default count=0 when the search result is null in Splunk Search

how to set default count=0 when the search result is null

Freya_X — Wed, 04 Jan 2017 03:44:40 GMT

When the search result is null with the special filter, how to show it with count =0 instead of no record?

index=app  sourcetype=ELP99 host="CHeapp*" source="C:\\TPFiles\\LogFile*.log" sourcetype="etp99" 
msgId=LS-* OR msgId=LAS-* OR msgId=LSP-* OR msgId=LRS-*
| rex field=msgId "(?<msgId_short>.*)-"
| stats dc(serverName) as ServerInRotation  by msgId_short

The result is:

msgId_short     ServerInRotation  
LS                              4
LSP                            5
LRS                            1

But no records about LAS, I want to display row LAS|0. How to do it?

Re: how to set default count=0 when the search result is null

jplumsdaine22 — Wed, 04 Jan 2017 14:26:41 GMT

You'll need to a combination of join type=left and an inputlookup

First, create a lookup table with just the field msgId and all the values you want to capture.

Now your initial search can look like this (this isn't actually neccessary, but since you went to the trouble of creating the lookup table you can use this!):

index=app sourcetype=ELP99 host="CHeapp*" source="C:\\TPFiles\\LogFile*.log" sourcetype="etp99"  [| inputlookup msgID.csv] 
| rex field=msgId "(?<msgId_short>.*)-"
| stats dc(serverName) as ServerInRotation by msgId_short

Next we'll join it to another invocation of the lookup, but the main search will actually become a subsearch

| inputlookup msgID.csv  | rex field=msgId "(?<msgId_short>.*)-"  fillnull value=0 ServerInRotation  
| join type=left msgId_short [ 
    index=app sourcetype=ELP99 host="CHeapp*" source="C:\\TPFiles\\LogFile*.log" sourcetype="etp99"  [| inputlookup msgID.csv] 
| rex field=msgId "(?<msgId_short>.*)-"
| stats dc(serverName) as ServerInRotation by msgId_short
]

I'm having a guess at your regex - if you want to include special characters in a post you need a newline and indent 4 spaces.

Anyhow, give it a go. See here for more on join: https://docs.splunk.com/Documentation/Splunk/6.4.2/SearchReference/Join

Re: how to set default count=0 when the search result is null

jplumsdaine22 — Wed, 04 Jan 2017 14:27:16 GMT

Strictly speaking you don't need a lookup table - you could do all this with eval, but I find a lookup table petter practice for his sort of thing.

Re: how to set default count=0 when the search result is null

hunters_splunk — Wed, 04 Jan 2017 14:47:22 GMT

Hi Freya_X,

Seems the rex syntax in your search was incorrect. You must supply a field name to which to assign the regex-captured group value. For example, if the new field name is msg_prefix, then the search should be:

index=app sourcetype=ELP99 host="CHeapp*" source="C:\\TPFiles\\LogFile*.log" sourcetype="etp99"
msgId=LS- OR msgId=LAS- OR msgId=LSP- OR msgId=LRS-
| rex field=msgId "(?<msg_prefix>.*)-"
| stats dc(serverName) as ServerInRotation by msgId_short

Please update your search and try again. Thanks!
Hunter

Re: how to set default count=0 when the search result is null

somesoni2 — Wed, 04 Jan 2017 16:21:57 GMT

Give this a try (adding dummy rows with 0 count for all msgId_short that you may get, then taking the max count. Anything missing in main search result will show 0 count)

index=app  sourcetype=ELP99 host="CHeapp*" source="C:\\TPFiles\\LogFile*.log" sourcetype="etp99" 
 msgId=LS-* OR msgId=LAS-* OR msgId=LSP-* OR msgId=LRS-*
 | rex field=msgId "(?<msgId_short>.*)-"
 | stats dc(serverName) as ServerInRotation  by msgId_short
| append [| gentimes start=-1 | eva msgId_short="LAS LSP LRS" | table msgId_short | makemv msgId_short | mvexpand msgId_short | eval ServerInRotation=0 ]
| stats max(ServerInRotation) as ServerInRotation by msgId_short