IBM QRadar

IBM QRadar

Join this online topic group to communicate across Security product users and IBM experts by sharing advice and best practices with peers and staying up to date regarding product enhancements.


#Security
#QRadar
#SecuringhybridcloudandAI
 View Only
Back to discussions
Expand all | Collapse all

Wrong table results when using IF statement with UNIQUECOUNT

  • 1.  Wrong table results when using IF statement with UNIQUECOUNT

    Posted 07/04/23 03:56 AM

    Dears, 

    I am trying to get a table showing the number of obsolete OS compared to supported OS

    I have a reference set where all the Obsolete IPs are listed below.

    Obsolete Server IP
    1.1.1.1
    2.2.2.2
    3.3.3.3

    And below is the inventory of our servers 

    Operating System IP Details
    Windows 1.1.1.1 Obsolete OS
    Red Hat 2.2.2.2 Obsolete OS
    Red Hat 3.3.3.3 Obsolete OS
    Windows 4.4.4.4 Supported OS
    Windows 5.5.5.5 Supported OS
    Windows 6.6.6.6 Supported OS
    Red Hat 7.7.7.7 Supported OS
    Red Hat 8.8.8.8 Supported OS
    Red Hat 9.9.9.9 Supported OS

    I need to get a table similar to the one below :

    Operating System Obsolete Supported
    Windows 1 3
    Red Hat 2 3

    So I am trying to use the below AQL query to get what I need: 

    select "Operating System",
    (IF REFERENCESETCONTAINS('Obsolete Server IP',sourceip) = 'true' then long(UNIQUECOUNT(sourceip)) else 0) as Obsolete,
    (IF REFERENCESETCONTAINS('Obsolete Server IP',sourceip) = 'false' then long(UNIQUECOUNT(sourceip)) else 0) as Normal
    from events where LOGSOURCENAME(logsourceid) = 'Logsource1'
    group by "Operating System"
    last 1 DAYS

    The issue is that I'm receiving the following inaccurate result:

    Operating System Obsolete Supported
    Windows 4 0
    Red Hat 0 5

    Your recommendations are highly appreciated to get the correct table results.

    Best

    Nabil



    ------------------------------
    Nabil Nehme
    ------------------------------


Related Content