I am using a visualization type of Data Table for some database logs. When add a field as a row, and this field contains null value in some records, the null for field is not counted.
real-case (attached image)
I have 120 records in total.
when I use field AUDIT_TYPE only, I have FineGrainedAudit=96 and Standard=24. Which is the correct result versus total -120.
when I add OBJECT_SCHEMA, I have FineGrainedAudit=96 and Standard=4. the missing 20 are those records on which field OBJECT_SCHEMA is null, as there are actions that does no involve objects. However the field AUDIT_TYPE does always have a value.
Is it possible to have count[s] for null OBJECT_SCHEMA in the Data Table?
In these case, as the logon events belong to the audit type Standard, I would like to see a third record:
AUDIT_TYPE =Standard, OBJECT_SCHEMA null, and Count0 =20
Your first picture shows me that your counting the field AUDIT_TYPE data.
Second picture your counting the two fields AUDIT_TYPE & OBJECT_SCHEMA. which has two different outputs for data called Standard.
This shows me that there are only 4 out of 24 messages that have both these fields together and you trying to find the rest ( 20) messages?
Exactly @gsmith - “I am looking for the other 20 records on which OBJECT_SCHEMA is null.”
Let me clarify further. These are Oracle database audit logs. Most records are actions of kind INSERT, DELETE, SELECT … and these are actions that do involve an object. Thus the OBJECT_SCHEMA column does always have a value.
But there are actions - like LOGON - that involve no object, and thus OBJECT_SCHEMA values are null. I want these records to be counted. On the dataset of these example there are precisely
Hello,
Sorry for the delay, I remove myself from the internet on the weekends
I was wondering what kind of configuration have you tried on the widget?
For an example, instead of having this flow.
AUDIT_TYPE ---> OBJECT_SCHEMA ---> COUNT
Maybe try something like this
OBJECT_SCHEMA ---> AUDIT_TYPE ---> COUNT
Another example of grabbing details for a certain field. In my lab I create a widget for getting the User name that logs into each of my Linux servers and the count on how many times. this is shown below. Perhaps is this comparable to what you want?
The null OBJECT_SCHEMA will skip counting whenever it be put - start, middle or end position related to other fields.
As per your linux user widget, yes, I am building an aggregate with 4-5 fields.
Let me illustrate with a SQL example - graylog_null_value_02.png.
On the left there is a table with five records, name being NULL in one of them.
On the right its is the summary performed by fields of the table. The null name is grouped (with id=2) and counted. This is the right behavior.
In Graylog Data Table aggregation, eventual null values do not appear and are not counted.
I understand, So from the beginning You have a field called Standard with the count 24 but soon as you add OBJECT_SCHEMA field you lose a 20 count. This makes be believe that the fields called Standard & OBJECT_SCHEMA are not all in the same message. But I also see in my lab fields that don’t have data are not present (null value). Not sure how to tackle this issue. I would have to do some more testing into this, since I haven’t really had this type of issue. I was looking into how to put some type of data in a null field/s so it could at least be presented even if it was just a “-” sign.
EDIT: my last suggestion. Think I found a solution here.
The fields AUDIT_TYPE and OBJECT_SCHEMA are two (of many) columns of the same table. The AUDIT_TYPE does always have a value. The OBJECT_SCHEMA has null values sometimes.
Now, I have a singe-value aggregation that tells me there are 338 records. When I build a visualization, type Data Table (graylog_null_value_03.png)
and I add OBJECT_SCHEMA as a dimension, I see that the total sum of count-s amounts to 41 + 2 + 66 + 96 +56 = 261.
So the summary will show counts only from those records on which OBJECT_SCHEMA is not null. I want to show also the ones when OBJECT_SCHEMA is null - there are 77 of them as I can confirm by searching (always same time interval) :
NOT _exists_:OBJECT_SCHEMA
As per the Graylog behavior - not show/count records with any NULL (grouped) field - I am not saying that there is an issue with that. I have seen the same behaviour in another SIEM I have experience with - Splunk. When making aggregate with “| stats” I noticed exactly the same behavior: records with null value in any single or multiple grouped fields were not shown/counted.
Splunk original search - discounting nulls: index=my_index | stats count by F1, F2, OBJECT_SCHEMA
Splunk fixed search: index=my_index | eval OBJECT_SCHEMA= if(isnull(OBJECT_SCHEMA),"",OBJECT_SCHEMA) | stats count by F1, F2, OBJECT_SCHEMA
by making an EVAL and setting an empty “” string to the field’s null value earlier before stats
I am looking for some similar approach on Graylog.
best regards
Altin
Hello,
I understand,
When there is no data in the field called “OBJECT_SCHEMA” it will not populate therefore you do not see this counted in your visualization/widget , So what needs to happen is to put a place holder in there for that NULL value like “0” or a text value like " NULL". Perhaps a pipeline would be able to solve this issue for you.
Example:
rule "Null Value"
when
contains (to_string($message.OBJECT_SCHEMA ), " ")
then
set_field("OBJECT_SCHEMA", NULL);
end
I’m not very good at pipeline but @tmacgbay maybe able to help on that subject.
.
No, I’m trying to put a place holder in a blank space so elasticsearch will populate your empty field with a null value in it. Like I said I’m not good at pipeline so you may need to research this.
Hello,
My apologies for not showing the correct example. Unfortunately I’m at work right now.
Maybe this is a better example. Not 100% sure if it will work correctly you may need to adjust it but this
should give you a better idea of what needs to be done.
rule "Set fields with Null Value"
when
has_field("OBJECT_SCHEMA") AND contains (to_string($message.OBJECT_SCHEMA ), "null")
then
set_field("OBJECT_SCHEMA", "!");
end
I believe the issue here is less about NULL and more about when Graylog/Elasticsearch encounters a message where OBJECT_SCHEMA does not have a value, the field is not stored in Elasticsearch for that message. So it doesn’t exist with a value of null…it simply doesn’t exist within that message and its fields.
If you need to count it’s “existential field crisis” () ~and~ you want to show it listed in a widget you will need to create a pipeline rule that will find all messages where OBJECT_SCHEMA ~should~ exist and insert it with some value like set_field("OBJECT_SCHEMA ", "!?!");
If you were so inclined the link that @gsmith provided earlier “Allow empty fields in Graylog” has some links in it for how to change Elasticsearch to allow a null_value field with a custom mapping
) ~and~ you want to show it listed in a widget you will need to create a pipeline rule that will find all messages where OBJECT_SCHEMA ~should~ exist and insert it with some value like