NRQL Query On Custom Event Data

Hello All,

I have built a custom shell script in Bash to collect various bits of patching information from our Linux servers in the environment. I am able to query the data and see everything that I expect.

Now I am trying to build a Dashboard around this data, but one piece of data I collect is a string output of the uptime command, from which I just pull the first field of output, carved out with use of awk (all commands are below). When I run the below NRQL query I get what I expect, which is I want to see the latest Patch Date, as well as the amount of time the Server has been running. This will tell me if the Server rebooted after patches were applied. Problem is, when I run the NRQL and include the uptime FACET, I get duplicated entries for each server, so the latest(timestamp) is not working as I would expect. When the NRQL is run WITHOUT the uptime FACET, then I get the output I expect.

Command used to collect uptime data:
uptime | awk -F',' '{print $1}'

Sample output of above uptime command:

$ uptime | awk -F’,’ ‘{print $1}’
11:49:21 up 75 days

NRQL Used (I get duplicate hostname entries spanning previous hour of clock time):
SELECT latest(timestamp) FROM Patch_Info FACET host_name,patches_applied,cached_patches,patch_date,uptime LIMIT MAX

If I EXCLUDE the uptime FACET then I get exactly what I expect, I see the latest timestamp of when the Event data was collected, how many patches were applied, how many patches we expected to be applied and when the last patches were applied.

Any help would be greatly appreciated!

Thanks in advance!

What attributes are included and populated on a patch_info event?

Hello @6MM, I collect the below, using the listed Linux commands:

host_name = $(hostname -s)
This is likely NOT needed, but I am not good enough yet with NRLQ to pull in the hostname from SystemSample in addition to my Patch_Info…

up_time = $(uptime | awk -F',' '{print $1}')
Collects how long since last reboot, should just be a string.

cached_patches = patches=$(/usr/bin/find /var/cache/zypp/packages -name '*.rpm' | wc -l)
Locates updated packages in Zypper cache, just returns a number

last_patch_date = $(rpm -qa --last | head -1 | awk '{print $2,$3,$4,$5}')
Grabs the date the most recent patch was applied

last_patches_applied = $(rpm -qa --last | grep "${last_patch_date}" | wc -l)
Uses last_patch_date to find how many patches were applied on that date.

So you have all the attributes in an event that you facet on in your nrql statement? Each event contains every attribute?

How many hosts do you have and do they change frequently?

Yes, that is correct. Should I separate the events out to individual events/scripts under the same eventType?

I am collecting this from around 80-100 hosts and the data does not change frequently, but when it does change I would like to know fairly quickly. Obviously the uptime will change each time the data is collected but all other data is mostly static, likely changing up to one week before patches are actually applied. We cache the Linux patches on all machines, Dev, QA, Prod, all at the same time so we can apply the same updates to all environments.

I think you can then try only doing facet on host while including latest() for each attribute.

1 Like

Thanks! That does seem to have fixed the issue!

Now to see how to get other metrics in this report.

Thanks again for the guidance!