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